VBA to sort an array of numbers

kmh6278

Board Regular
Joined
Mar 7, 2006
Messages
52
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.
 
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.

Sorry about that; I certainly blew it! Okay, Split() produces substrings, which I should have thought of. Thus - the sort is actually correct (although not what we want), as 17 does follow 1, and 2 follows 17, when sorting left-to-right (as strings).

Anyways, my bad. Not particularly efficient, but we can use an extra Variant array, throw the values into it, where they will be sub-typed to doubles... sort, then join (which is allowed as the array is still a variant).

Then we get the desired "0,1,2,4,5,8,17,21,35,42,99" from your example values.

Rich (BB code):
Option Explicit
    
Sub CallIt()
Dim StringToSort As String
    
    StringToSort = "1,5,8,99,4,2,0,35,21,17,42"
    MsgBox StringToSort
    BubbleSrt StringToSort, True
    MsgBox StringToSort
    Debug.Print StringToSort
    StringToSort = "1,5,8,99,4,2,0,35,21,17,42"
    MsgBox StringToSort
    BubbleSrt StringToSort, False
    MsgBox StringToSort
    Debug.Print StringToSort
        
End Sub
    
Public Function BubbleSrt(StringIO As String, Ascending As Boolean)
Dim ArrayTmp As Variant
Dim ArrayIn() As Variant
Dim SrtTemp As Variant
Dim i As Long
Dim j As Long
    
    ArrayTmp = Split(StringIO, ",")
    ReDim ArrayIn(LBound(ArrayTmp) To UBound(ArrayTmp))
    
    For i = LBound(ArrayTmp) To UBound(ArrayTmp)
        ArrayIn(i) = CDbl(ArrayTmp(i))
    Next
    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

I ran it both ways this time, just to make sure.:oops:

Mark
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
No problem. Actually I only tried the other solutions first to see why they didn't work as they looked fine to me too - came to the same conclusion as above after a head scratch :confused:
 
Upvote 0
Thank you for all your help! Using most of the above code, I have been able to get my macro working about 98%. I am using the following function for splitting, but there is an issue when the string contains only one number (i.e. "12,"). The debugging gives me a subscript out of range error. I am thinking that I want to only split it if there is more than 1 item in the string, but I am not sure how I can go about finding that out. As long as there is more than 1 number in the string, everything works as needed. Any ideas?

Function Split(txt As Variant, delim As String) As Variant
Dim i As Long, ii As Integer, a(), result()
For i = 1 To Len(txt)

If i < Len(txt) And Mid(txt, i, 1) = delim Then
ii = ii + 1: ReDim Preserve a(ii): a(ii) = i
End If

Next
a(0) = 1
ReDim Preserve a(ii + 1): a(ii + 1) = Len(txt) + 1
ReDim result(UBound(a) - 1): ii = -1
For i = LBound(a) To UBound(a) - 1
ii = ii + 1
result(ii) = Replace(Mid(txt, a(i), a(i + 1) - a(i)), delim, "")
Next
Split = result

End Function
 
Upvote 0
I have no idea what you are doing now. You do not need to write a Split function. VBA has a split function already. Plus you should never name a custom function the same as an inbuilt function. Just erase all that code and use VBA's split function.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top