Sorting collections is something you might need to do. In this example, we'll use the cMyClass object we created in Get Started Snippets to demonstrate sorting a collection of objects and how to use recursion to sort the object's children, and entire family tree. As usual you can download this in the getting started workbook.
Creating a needSwap function.
If you want to sort a custom object, you should create two public functions. toString() which will return the value to use for sorting on, and needSwap() which will tell the sort function whether or not the current object is in the wrong position relative to the object it is being compared against. Here are these functions for our cMyClass object. These can be copied directly in to your class with only minor amendments.
Public Function needSwap(cc As cMyClass, e As eSort) As Boolean
' this can be used from a sorting alogirthm
Select Case e
Case eSortAscending
needSwap = LCase(toString) > LCase(cc.toString)
Case eSortDescending
needSwap = LCase(toString) < LCase(cc.toString)
Case Else
needSwap = False
End Select
End Function
Public Function toString() As String
toString = pName
End Function
The sort Collection procedure
This is a very simple kind of bubble sort. Note that it calls the needSwap() function and the toString() function that you will have created in your class.
Option Explicit
' sort a collection
Public Enum eSort
eSortNone
eSortAscending
eSortDescending
End Enum
Function SortColl(ByRef coll As Collection, Optional eorder As Long = eSortAscending) As Long
Dim ita As Long, itb As Long
Dim va As Object, vb As Object, bSwap As Boolean
Dim x As Object, y As Object
For ita = 1 To coll.Count - 1
For itb = ita + 1 To coll.Count
Set x = coll(ita)
Set y = coll(itb)
bSwap = x.needSwap(y, eorder)
If bSwap Then
With coll
Set va = coll(ita)
Set vb = coll(itb)
.add va, x.toString , itb
.add vb, y.toString , ita
.Remove ita + 1
.Remove itb + 1
End With
End If
Next
Next
Sorting the heirarchy
As discussed in Getting started with recursion , cycling through parents and children is straightforward. With this technique so is sorting the children within each parent. In this case we will sort in descending order. Here is the updated test to print out the tree structure, but also to sort it first.
Sub sortMe(mr As cMyClass)
Dim mc As cMyClass
SortColl mr.Children, eSortDescending
For Each mc In mr.Children
If mc.Children.Count > 0 Then sortMe mc
Next mc
End Sub
Sub testChildrenRecurse()
Dim mroot As cMyClass, mc As cMyClass
Set mroot = New cMyClass
With mroot
.init 100, "bill"
Set mc = New cMyClass
.Children.add mc.init(202, "mary")
Set mc = New cMyClass
.Children.add mc.init(200, "janet")
Set mc = New cMyClass
.Children.add mc.init(201, "john")
Set mc = New cMyClass
.Children(2).Children.add mc.init(300, "tom")
Set mc = New cMyClass
.Children(2).Children.add mc.init(301, "fred")
End With
sortMe mroot
printChildren mroot
End Sub
This gives the correctly sorted output as follows.
bill has 3 children
mary has 0 children
john has 0 children
janet has 2 children
tom has 0 children
fred has 0 children