VBA – ArrayList class – a helper to sort arrays in VBA

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:

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:

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:

-5 
-1 
 0 
 2 
 3 
 4 
 4 
 33 

Enjoy it (either the code above or Kylie).