Sort values in VBA without an algorithm


Sorting of values is a quite common programming task. VBA has no native sorting method. You have to use algorithm like QuickSort to sort the array in place. However, there is another, maybe easier way how to sort your data without using algorithms.

ArrayList class

ArrayList is a dynamic array type whose size is dynamically increased as required. It is similar to VBA Collection type, but offers more methods. One of them is Sort method. Indexes in this collection are zero-based.

The ArrayList is not part of the standard VBA-library, but a part of System.Collections namespace of .NET Framework.

ArrayList object in VBA

There are basically two ways how to create an ArrayList instance in VBA.

  • Early Binding which requires mscorlib.tlb reference. In your VBA editor go to Tools -> References and choose mscorlib.dll library.
  • Late Binding where you do not need to reference any library.

I prefer to use Late Binding, because it seems to be more robust, portable and you avoid possible compatibility issues when moving between computers with different version of the same library. Keep in mind that using Late Binding you loose IntelliSense features like code completion.

Early binding


Dim firstList As ArrayList
Set firstList = New ArrayList
    

Late binding


Dim secondList As Object
Set secondList = CreateObject("System.Collections.ArrayList")
    

ArrayList example


Sub ArrayListExample()
    Const TOP_BOUND = 100
    
    Dim i As Long
    
    ' early binding
    Dim firstList As ArrayList
    Set firstList = New ArrayList
    
    For i = 1 To TOP_BOUND
        ' populate with random numbers between 0 - 100
        firstList.Add Int((100 - 0 + 1) * Rnd + 0)
    Next
    
    ' late binding
    Dim secondList As Object
    Set secondList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList
    
    For i = 1 To TOP_BOUND
        ' populate with random [A -Z] characters
        secondList.Add Chr(Int((90 - 65 + 1) * Rnd + 65))
    Next
    
    ' sort lists
    firstList.Sort
    secondList.Sort
    
    ' you can call also other methods of ArrayList class
    firstList.AddRange secondList
    firstList.InsertRange 0, secondList
    
    For i = 0 To firstList.Count - 1
        Debug.Print firstList(i)
    Next
    
    Dim arr()
    ' populate array variable with ArrayList content
    arr = firstList.ToArray()
End Sub
    

There are other useful ArrayList methods you can use to build your application, like:

  • Contains method: Determines whether an element is in the ArrayList.
  • RemoveAt method: Removes the element at the specified index of the ArrayList.
  • RemoveRange method: Removes a range of elements from the ArrayList.
  • Reverse method: Reverses the order of the elements in the ArrayList or a portion of it.
  • ...

Please check official Microsoft reference here.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>