WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]
One of the things I find most frustrating about VBA for Excel is the lack of a native sorting method for arrays. Perhaps it’s because there’s the option of sorting it on the worksheet, but that’s not always viable.
This post is mainly just a store for VBA implementations of sorting algorithms and will probably be updated as and when I come across other algorithms that seem like they’d be useful in VBA code.
Each of the functions has been implemented in a way which allows you to specify the sort order, with a default of descending. It also allows you to specify what column of the array to sort on. The syntax is myArray = AlgorithmName(inputArray, 0, Descending = False) which will return an array sorted in ascending order by the values in the first column.
Worth noting is that although comb sort is generally faster than bubble sort, it is not “stable”, meaning that identical items will not necessarily preserve their order from before sorting. This means that if you want to sort by one column and then by another, while keeping the results of the first sort in their sorted order you will need to use bubble sort. Where this isn’t important, comb sort is probably a better choice.
Bubble sort
In developing the Pareto frontier code I had to search around on the net for a good sort method and came across Andy Pope’s implementation of bubble sort. This code is based on that.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Function BubbleSort(TempArray() As Variant, _ SortIndex As Long, _ Optional Descending As Boolean = True) Dim NoSwaps As Boolean Dim Item As Long Dim Temp() As Variant Dim Col As Long ReDim Temp(0 To UBound(TempArray, 2)) Do NoSwaps = True For Item = LBound(TempArray) To UBound(TempArray) - 1 If Descending And _ (TempArray(Item, SortIndex) < TempArray(Item + 1, SortIndex)) Then ' Sort Z-A NoSwaps = False For Col = 0 To UBound(TempArray, 2) Temp(Col) = TempArray(Item, Col) TempArray(Item, Col) = TempArray(Item + 1, Col) TempArray(Item + 1, Col) = Temp(Col) Next ElseIf Not Descending And _ TempArray(Item, SortIndex) > TempArray(Item + 1, SortIndex) Then ' Sort A-Z NoSwaps = False For Col = 0 To UBound(TempArray, 2) Temp(Col) = TempArray(Item, Col) TempArray(Item, Col) = TempArray(Item + 1, Col) TempArray(Item + 1, Col) = Temp(Col) Next End If Next Loop While Not NoSwaps BubbleSort = TempArray End Function |
Comb sort
Bubble sort is well known for being a slow algorithm as the length of the list to be sorted increases so I decided to take a look around for other algorithms to implement. The Wikipedia page for sorting algorithms came up. The summary of comb sort seemed promising, particularly given the comment that it took only a small amount of code.
The following code is based on the pseudocode on the Wikipedia page for comb sort.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
Function CombSort(TempArray As Variant, _ SortIndex As Long, _ Optional Descending As Boolean = False) Dim Gap As Double Dim Temp() As Variant Dim Col As Long Dim NoSwaps As Boolean Dim i As Long ReDim Temp(0 To UBound(TempArray, 2)) Gap = UBound(TempArray) Do Until Gap = 1 And NoSwaps Gap = (Gap / 1.24733095010398) If Gap < 1 Then Gap = 1 End If i = 0 NoSwaps = True Do Until (i + Gap) >= UBound(TempArray) If Descending And _ (TempArray(i, SortIndex) < TempArray(i + Gap, SortIndex)) Then ' Sort Z-A For Col = 0 To UBound(TempArray, 2) Temp(Col) = TempArray(i, Col) TempArray(i, Col) = TempArray(i + Gap, Col) TempArray(i + Gap, Col) = Temp(Col) Next NoSwaps = False ElseIf Not Descending And _ (TempArray(i, SortIndex) > TempArray(i + Gap, SortIndex)) Then ' Sort A-Z For Col = 0 To UBound(TempArray, 2) Temp(Col) = TempArray(i, Col) TempArray(i, Col) = TempArray(i + Gap, Col) TempArray(i + Gap, Col) = Temp(Col) Next NoSwaps = False End If i = i + 1 Loop Loop CombSort = TempArray End Function |
Happy sorting!
7 Comments on “Sorting functions for VBA”
Can you show a sample implementation of the algo? I.e., what are the arguments?
A sample would be
mySortedArray = CombSort(inputArray, 0, Descending = False)which will return an array sorted in ascending order by the values in column 0.thank you for your code!
I’ve found, that your current CombSort would not correctly sort Array(“test2″,”test3″,”test1”) unless you removing the the following line
Do Until (i + Gap) >= UBound(TempArray)
to
Do Until (i + Gap) > UBound(TempArray)
additionally in both functions you could simply use
Dim Temp As Variant
'...
Temp = TempArray(i, Col)
'...
TempArray(i + Gap, Col) = Temp
instead of
Dim Temp() As Variant
'...
ReDim Temp(0 To UBound(TempArray, 2))
'...
Temp(Col) = TempArray(i, Col)
'...
TempArray(i + Gap, Col) = Temp(Col)
as you store only one field at the time anyway, which would lower the memory footprint.
furthermore you can shorten both functions by using
If ((TempArray(i, SortIndex) TempArray(i + Gap, SortIndex)) And Descending = False) Then
instead of your if … elseif … end if-statement and thus have to write the actual copy-block only once.
brgds,
martin.
actually, the code
If ((TempArray(i, SortIndex) TempArray(i + Gap, SortIndex)) And Descending = False) Then
can be further compacted by simply using
If TempArray(i, SortIndex) TempArray(i + Gap, SortIndex) Then
brgds,
martin.
PS: my code was – and will probably again – not displayed correctly. some parts in the if-clause has simply been discarded. trying to use -pre- instead of -code- in the following sample
aha, > has to be written as >. horrible.
so my short version would read:
the longer version was:
… of course, the short version is wrong …! 😀