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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
I use UDF to sort arrays.

Code:
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

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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.

Code:
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

Board Regular
Joined
Mar 7, 2006
Messages
52

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,980
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 25, 2010
Messages
588

ADVERTISEMENT

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

Code:
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

Rich (BB code):
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

Well-known Member
Joined
May 25, 2010
Messages
588
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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:

Code:
[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]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,082
Messages
5,639,967
Members
417,120
Latest member
Pavithra devi

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
Top