# VBA to sort an array of numbers

#### kmh6278

##### Board Regular
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
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
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
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

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

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
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
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
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"]'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:

Replies
0
Views
235
Replies
2
Views
248
Replies
5
Views
97
Replies
3
Views
54
Replies
1
Views
44

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.

### Which adblocker are you using?

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

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