Sorting Collections

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.

Page Content hide
1 Creating a needSwap function.
2 The sort Collection procedure
3 Sorting the hierarchy
4 Summary

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 SelectEnd FunctionPublic Function toString() As String    toString = pNameEnd 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 hierarchy

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

Summary

There we have it. 2 normally rather complex concepts, recursion and sorting, made simple through the use of classes and collections. Comments and questions are welcome on our forum.