Sorting of objects by their properties in VBA


Sorting in VBA is a quite tricky task because VBA itself has no support for sorting values. You have to use an algorithm like QuickSort. But what if you want to sort objects? It is even worse and harder, algorithms become more complex and less clear. Luckily, you can again make use of mscorlib.dll library and with the help of ArrayList and IComparer interface, you can implement your own object sorting method.

Prerequisites

An Employee class

Firstly, we need a collection of objects we would like to sort. Let's create a simple class with just 3 properties, let's store it in a class module and call it an Employee class.


Public ID As Long
Public FullName As String
Public Birthdate As Date
    

MyComparer class

For sorting of our Employee objects we need to create a custom class that implements the IComparer interface. Interface exposes a method IComparer.Compare(Object, Object) that compares two objects and returns a value indicating whether one is less than, equal to, or greater than the other. It takes two parameters (x object, y object) and returns a signed integer that indicates the relative values of x and y.

  • If less than 0, x is less than y.
  • If 0, x equals y.
  • If greater than 0, x is greater than y.

We have to implement this method in our class. Then, based on our object property value we perform either string comparison with VBA StrComp function in case of FullName property or numeric comparison with standard VBA comparison operators in case of ID or Birthdate property (as VBA dates are internally stored also as numbers).


Implements mscorlib.IComparer

Private sortByField As SortBy

Enum SortBy
    ID = 1
    FullName = 2
    Birthdate = 3
End Enum

' set property to sort by
Public Sub SetSortingField(sortField As SortBy)
    sortByField = sortField
End Sub

' IComparer.Compare method implementation
Public Function IComparer_Compare(ByVal x As Variant, ByVal y As Variant) As Long
    Select Case sortByField
        Case SortBy.FullName
            ' string comparison
            IComparer_Compare = StrComp(x.FullName, y.FullName, vbTextCompare)
            
        Case SortBy.ID, SortBy.Birthdate
            ' numeric comparison
            Dim a As Variant, b As Variant
            
            If sortByField = SortBy.ID Then
                a = x.ID
                b = y.ID
            Else
                a = x.Birthdate
                b = y.Birthdate
            End If
            
            If a > b Then
                IComparer_Compare = 1
            ElseIf a < b Then
                IComparer_Compare = -1
            ElseIf a = b Then
                IComparer_Compare = 0
            End If
    End Select
End Function
    

Code in a standard module

To save some time and writing, we make a factory function which creates and return our Employee object.


' create and return Employee object
Public Function CreateEmployee(idNo As Long, fName As String, dateOfBirth As Date) As Employee
    Dim person As New Employee
    
    With person
        .ID = idNo
        .FullName = fName
        .Birthdate = dateOfBirth
    End With
    
    Set CreateEmployee = person
End Function
      
    

Our main routine (below) deserves some description. The first part is clear, we create a new instance of MyComparer class as well as an ArrayList collection with some Employees objects.

In a second part, we set comparer to a desired field and we perform sorting of objects with an ArrayList Sort_2 method. But wait, what? Sort_2 method? Yes :).

There are some facts you have to be aware of when using mscorlib objects in VBA:

  • Function overloading is not inherently supported by VBA
  • Overloaded methods are renamed. Generally, the first method in the MSDN documentation would have a normal (default) name and the subsequent methods (overloads) would be enumerated with an underscore
  • Overloads of the Sort method look as below:
MSDN Documentation VBA alternative
Sort() Sort()
Sort(IComparer) Sort_2(IComparer)
Sort(Int32, Int32, IComparer) Sort_3(Int32, Int32, IComparer)

' sort objects by their properties
Sub SortObjects()
    Dim it As Employee
    Dim arrList As Object
    Dim comp As New MyComparer
    
    Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList
    
    ' populate ArrayList
    With arrList
        .Add CreateEmployee(6, "Vito Corleone", #1/1/1980#)
        .Add CreateEmployee(3, "Darth Vader", #1/1/1975#)
        .Add CreateEmployee(1, "Forrest Gump", #1/1/1965#)
        .Add CreateEmployee(5, "Indiana Jones", #1/1/1985#)
        .Add CreateEmployee(2, "The Joker", #1/1/1970#)
        .Add CreateEmployee(4, "Han Solo", #1/1/1960#)
    End With
    
    ' set MyComparer object to sort by ID
    comp.SetSortingField SortBy.ID
    ' perform sorting
    arrList.Sort_2 comp
    
    ' print output to Immediate window
    Debug.Print "Sorted by ID"
    For Each it In arrList
        Debug.Print it.ID, it.FullName, it.Birthdate
    Next
    
    ' set MyComparer object to sort by FullName
    comp.SetSortingField SortBy.FullName
    ' perform sorting
    arrList.Sort_2 comp
    
    ' print output to Immediate window
    Debug.Print "Sorted by FullName"
    For Each it In arrList
        Debug.Print it.ID, it.FullName, it.Birthdate
    Next
    
    ' set MyComparer object to sort by Birthdate
    comp.SetSortingField SortBy.Birthdate
    ' perform sorting
    arrList.Sort_2 comp
    
    ' print output to Immediate window
    Debug.Print "Sorted by Birthdate"
    For Each it In arrList
        Debug.Print it.ID, it.FullName, it.Birthdate
    Next
End Sub    
    

And loops are there just to print output to a screen. If everything goes well, you should see the following output in the Immediate window. Employee objects nicely sorted by ID, FullName and then by Birthdate.


Sorted by ID
 1            Forrest Gump  01.01.1965 
 2            The Joker     01.01.1970 
 3            Darth Vader   01.01.1975 
 4            Han Solo      01.01.1960 
 5            Indiana Jones 01.01.1985 
 6            Vito Corleone 01.01.1980 
Sorted by FullName
 3            Darth Vader   01.01.1975 
 1            Forrest Gump  01.01.1965 
 4            Han Solo      01.01.1960 
 5            Indiana Jones 01.01.1985 
 2            The Joker     01.01.1970 
 6            Vito Corleone 01.01.1980 
Sorted by Birthdate
 4            Han Solo      01.01.1960 
 1            Forrest Gump  01.01.1965 
 2            The Joker     01.01.1970 
 3            Darth Vader   01.01.1975 
 6            Vito Corleone 01.01.1980 
 5            Indiana Jones 01.01.1985  
    

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.