Sorting Collections

How to sort a collection of Objects 

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


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.

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.





Comments