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