If you are coding professionally in VBA, most probably you are using arrays every now and then and you have your own function for sorting an array.
Something like this bubble sort or anything similar is what you have written at least once in your code:
|
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 |
Public Function fnVarBubbleSort(ByRef varTempArray As Variant) As Variant Dim varTemp As Variant Dim lngCounter As Long Dim blnNoExchanges As Boolean Do blnNoExchanges = True For lngCounter = LBound(varTempArray) To UBound(varTempArray) - 1 If CDbl(varTempArray(lngCounter)) > CDbl(varTempArray(lngCounter + 1)) Then blnNoExchanges = False varTemp = varTempArray(lngCounter) varTempArray(lngCounter) = varTempArray(lngCounter + 1) varTempArray(lngCounter + 1) = varTemp End If Next lngCounter Loop While Not (blnNoExchanges) fnVarBubbleSort = varTempArray On Error GoTo 0 Exit Function End Function |
And while writing it you were thinking something like – why am I not coding in C#, Java, C++, Python etc? 🙂
Well, to be honest, the bubble sort above was used by me as well. Until lately, when I have actually decided to use ArrayList for sorting of arrays, instead of using the good old bubble sort.
Thus, something like this is always handy:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Option Explicit Public Sub TestMe() Dim arrList As Object Set arrList = CreateObject("System.Collections.ArrayList") Dim someArray As Variant someArray = Array(3, 4, 0, -1, 2, 33, -5, 4) Dim cnt As Long For cnt = LBound(someArray) To UBound(someArray) arrList.Add someArray(cnt) Next cnt arrList.Sort ReDim someArray(arrList.Count) For cnt = 0 To arrList.Count - 1 arrList(cnt) = arrList.Item(cnt) Debug.Print arrList(cnt) Next cnt End Sub |
And here is the result in the immediate window:
|
1 2 3 4 5 6 7 8 |
-5 -1 0 2 3 4 4 33 |
Enjoy it (either the code above or Kylie).