VBA - .NET - Implement IComparable on your VBA classes to use .NET sorter

It is a common question to ask what is the best way to sort in VBA. Best can mean different things to different people. I do not wish to get into religious wars about sort algorithms, I simply give here a way of using a .NET sorter found in the System.Collections.ArrayList class. It requires implementing a standard .NET interface IComparable which is accessed by adding a Tools Reference to mscorlib.dll.

The use case given is a trivial one, we have Employees who have a unique identifier of an EmployeeId by which we will sort them.

Employee Class

First, the Employee class. After adding the Tools Reference to mscorlib.dll add the line Implements IComparable, this will require the implementation of only one method IComparable_CompareTo which must return -1,0 or 1 indicating smaller than, equal to or larger than.

We will sort employees by EmployeeID which will be a 32-bit integer so the implementation of IComparable_CompareTo below is easy. The logic can be very easily expanded to compare multiple variables, so long as the method returns -1,0 or 1.

The objects are held by System.Collections.ArrayList on a late bound basis (i.e. IDispatch) they are also passed in on a late bound basis so the obj parameter needs to be cast to Employee so it can be called for comparison.

Option Explicit

'*Tools->References->mscorlib.dll

Implements IComparable

Public EmployeeID As Long

Private Function IComparable_CompareTo(ByVal obj As Variant) As Long

    '* obj is late bound so we need to cast to get to a callable interface
    Dim oEmployee As Employee
    Set oEmployee = obj
    
    IComparable_CompareTo = (Me.EmployeeID - oEmployee.EmployeeID)

End Function

Test standard module

In a standard module (called anything) paste the following code. We create an instance of mscorlib.ArrayList but despite being early bound there was no Intellisense so we are a little in the dark.

Option Explicit

Function CreateNewEmployee(ByVal lEmployeeId As Long) As Employee
    Set CreateNewEmployee = New Employee
    CreateNewEmployee.EmployeeID = lEmployeeId
End Function

Sub Test()

    Dim oArrayList As mscorlib.ArrayList
    Set oArrayList = New mscorlib.ArrayList

    '* No Intellisense but for list of potential methods try
    '* https://docs.microsoft.com/en-us/dotnet/api/system.collections.arraylist.add?view=netframework-4.7.2
    oArrayList.Add CreateNewEmployee(1114)
    oArrayList.Add CreateNewEmployee(157)
    oArrayList.Add CreateNewEmployee(623)
        
    oArrayList.Sort

    Debug.Print oArrayList.Item(0).EmployeeID
    Debug.Print oArrayList.Item(1).EmployeeID
    Debug.Print oArrayList.Item(2).EmployeeID

    '* the above prints the following, demonstrating they have been sorted
    '   157
    '   623
    '   1114
End Sub

The code requires little further explanation and should be easy to follow. Simply run Test to see the results. For fun, you can set a break-point in the Employee class to watch the execution dive in and out between the .NET platform and your VBA and witness true VBA / .NET inter-operability.