VBA to sort an array of numbers

[kmh6278]

I am looking to sort an array of numbers but don't know where to even begin with VBA code.

My array is (1,4,2,32,5,21) and needs to result as (1,2,4,5,21,32).

Any help would be greatly appreciated.

 

[rjwebgraphix]

I use UDF to sort arrays.

Public Function BubbleSrt(ArrayIn, Ascending As Boolean)

Dim SrtTemp As Variant
Dim i As Long
Dim j As Long


If Ascending = True Then
    For i = LBound(ArrayIn) To UBound(ArrayIn)
         For j = i + 1 To UBound(ArrayIn)
             If ArrayIn(i) > ArrayIn(j) Then
                 SrtTemp = ArrayIn(j)
                 ArrayIn(j) = ArrayIn(i)
                 ArrayIn(i) = SrtTemp
             End If
         Next j
     Next i
Else
    For i = LBound(ArrayIn) To UBound(ArrayIn)
         For j = i + 1 To UBound(ArrayIn)
             If ArrayIn(i) < ArrayIn(j) Then
                 SrtTemp = ArrayIn(j)
                 ArrayIn(j) = ArrayIn(i)
                 ArrayIn(i) = SrtTemp
             End If
         Next j
     Next i
End If

BubbleSrt = ArrayIn

End Function

then just call it in the fashion of..

Array2Sort = BubbleSrt(Array2Sort, True)

Array2Sort being your Array variable. True being sort as Ascending. False will sort Decending.

 

[kmh6278]

Thanks. As it turns out, I don't have an array, I have a string of numbers. So, I have a type mismatch error. I will keep this code handy as I suspect it will come in useful in other projects.

Thanks again!

 

[hiker95]

rjwebgraphix,

Thanks for the Bubble Sort code.

kmh6278,

You displayed your array as numbers, not strings:
My array is (1,4,2,32,5,21) and needs to result as (1,2,4,5,21,32).

So, the following macro and function should work correctly.

Option Explicit
Sub SortArray()
' hiker95, 03/11/2013
' http://www.mrexcel.com/forum/excel-questions/690718-visual-basic-applications-sort-array-numbers.html
Dim MyArray As Variant, i As Long
MyArray = Array(1, 4, 2, 32, 5, 21)
' True being sort as Ascending. False will sort Decending.
MyArray = BubbleSrt(MyArray, True)
For i = LBound(MyArray) To UBound(MyArray)
  MsgBox MyArray(i)
Next i
End Sub

Public Function BubbleSrt(ArrayIn, Ascending As Boolean)
' rjwebgraphix, 03/11/2013
' http://www.mrexcel.com/forum/excel-questions/690718-visual-basic-applications-sort-array-numbers.html
' True being sort as Ascending. False will sort Decending.
Dim SrtTemp As Variant
Dim i As Long
Dim j As Long
If Ascending = True Then
  For i = LBound(ArrayIn) To UBound(ArrayIn)
    For j = i + 1 To UBound(ArrayIn)
      If ArrayIn(i) > ArrayIn(j) Then
        SrtTemp = ArrayIn(j)
        ArrayIn(j) = ArrayIn(i)
        ArrayIn(i) = SrtTemp
      End If
    Next j
  Next i
Else
  For i = LBound(ArrayIn) To UBound(ArrayIn)
    For j = i + 1 To UBound(ArrayIn)
      If ArrayIn(i) < ArrayIn(j) Then
        SrtTemp = ArrayIn(j)
        ArrayIn(j) = ArrayIn(i)
        ArrayIn(i) = SrtTemp
      End If
    Next j
   Next i
End If
BubbleSrt = ArrayIn
End Function

 

[kmh6278]

Thank you. Unfortunately I was mistaken in my original post. In stepping through and debugging my code I realized that the format of my variable is actually:
var="1,4,2,32,5,21" and needs to result as newvar="1,2,4,5,21,32".

 

[Rick Rothstein]

Thank you. Unfortunately I was mistaken in my original post. In stepping through and debugging my code I realized that the format of my variable is actually:
var="1,4,2,32,5,21" and needs to result as newvar="1,2,4,5,21,32".

Then change this line of code from hiker95's posted macro...

MyArray = Array(1, 4, 2, 32, 5, 21)

to this...

MyArray = Split("1,4,2,32,5,21", ",")

 

[rjwebgraphix]

Combining the bubble sort with this code gets you half way there.

Sub StringSort()

Dim StrVar As String
Dim SortStr As Variant
Dim NewStr As String

Dim i As Long

StrVar = "1,4,2,32,5,21"

SortStr = Split(StrVar, ",")

SortStr = BubbleSrt(SortStr, True)

For i = LBound(SortStr) To UBound(SortStr)
    If i = LBound(SortStr) Then
        NewStr = SortStr(i)
    Else
        NewStr = NewStr & "," & SortStr(i)
    End If

Next i

Debug.Print NewStr

End Sub

This is not resulting in the intended string output. I'll just have to figure out why the sort is sorting 21 and 32 lower than 4, but above 2.

I've seen this before, but can't recall the fix off the top of my head and I'm also in the middle of a problem of my own, so can't spend a lot of time on this, but maybe someone else can look at the sort code and say why its sorting wrong.

 

[GTO]

Combining the bubble sort with this code gets you half way there....This is not resulting in the intended string output. I'll just have to figure out why the sort is sorting 21 and 32 lower than 4, but above 2.

I've seen this before, but can't recall the fix off the top of my head and I'm also in the middle of a problem of my own, so can't spend a lot of time on this, but maybe someone else can look at the sort code and say why its sorting wrong.

@rjwebgraphix:

I haven't tried your latest code, but fwiw, this seems to work. As Rick suggested, use Spli(), and I tossed in the Join(), guessing that we want a string at the end.

Hope it helps?

Mark

Option Explicit
    
Sub CallIt()
Dim StringToSort As String
    
    StringToSort = "1,5,8,99,4,2,0"
    MsgBox StringToSort
    BubbleSrt StringToSort, True
    MsgBox StringToSort
    
End Sub
    
Public Function BubbleSrt(StringIO As String, Ascending As Boolean)
Dim ArrayIn As Variant
Dim SrtTemp As Variant
Dim i As Long
Dim j As Long
    
    ArrayIn = Split(StringIO, ",")
    
    If Ascending = True Then
        For i = LBound(ArrayIn) To UBound(ArrayIn)
             For j = i + 1 To UBound(ArrayIn)
                 If ArrayIn(i) > ArrayIn(j) Then
                     SrtTemp = ArrayIn(j)
                     ArrayIn(j) = ArrayIn(i)
                     ArrayIn(i) = SrtTemp
                 End If
             Next j
         Next i
    Else
        For i = LBound(ArrayIn) To UBound(ArrayIn)
             For j = i + 1 To UBound(ArrayIn)
                 If ArrayIn(i) < ArrayIn(j) Then
                     SrtTemp = ArrayIn(j)
                     ArrayIn(j) = ArrayIn(i)
                     ArrayIn(i) = SrtTemp
                 End If
             Next j
         Next i
    End If
    StringIO = Join(ArrayIn, ",")
End Function

 

[rjwebgraphix]

Ha! Join is better than how rebuilt the string list, however, it still produces the same result.

"1,5,8,99,4,2,0,35,21,17,42"

becomes

"0,1,17,2,21,35,4,42,5,8,9"

when it should be

"0,1,2,4,5,8,9,17,21,35,42"

Looking for a resolution now. If I find one sooner than someone else, I'll post for you.

 

[xenou]

It appears the confusion is that you are sorting *strings* but you want them to be sorted as *numbers*. I'm not sure i have the most direct answer but one way through this is to convert the strings to numbers, sort them, and then spit out the result as a string again:

[COLOR="Navy"]Sub[/COLOR] TestIt()
[COLOR="Navy"]Dim[/COLOR] a, b, i, s

    s = "1,5,8,99,4,2,0,35,21,17,42"
    a = Split(s, ",")
    [COLOR="Navy"]ReDim[/COLOR] b(0 [COLOR="Navy"]To[/COLOR] UBound(a))
    [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] UBound(a)
        b(i) = CLng(a(i))
    [COLOR="Navy"]Next[/COLOR] i
    b = BubbleSort(b)
    s = Join(b, ",")
    MsgBox s

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


[COLOR="SeaGreen"]'Adapted from http://support.microsoft.com/kb/133135[/COLOR]
[COLOR="SeaGreen"]'Note: a zero-based array is assumed (jazz this up by testing for zero or one base arrays and proceeding accordingly)[/COLOR]
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] BubbleSort(ByVal tempArray [COLOR="Navy"]As[/COLOR] Variant) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] NoExchanges [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]

    [COLOR="SeaGreen"]' Loop until no more "exchanges" are made.[/COLOR]
    [COLOR="Navy"]Do[/COLOR]
        NoExchanges = True
        
        [COLOR="SeaGreen"]' Loop through each element in the array.[/COLOR]
        [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] UBound(tempArray) - 1
        
            [COLOR="SeaGreen"]' Substitution when element is greater than the element following int[/COLOR]
            [COLOR="Navy"]If[/COLOR] tempArray(i) > tempArray(i + 1) [COLOR="Navy"]Then[/COLOR]
                NoExchanges = False
                Temp = tempArray(i)
                tempArray(i) = tempArray(i + 1)
                tempArray(i + 1) = Temp
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        
        [COLOR="Navy"]Next[/COLOR] i
    
    [COLOR="Navy"]Loop[/COLOR] [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] (NoExchanges)
    
    BubbleSort = tempArray

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]