Sorting objects in a collection is not necessarily as simple as you would hope - or at least having a routine that can sort any collection using any object is not that straightforward. Most programmers know how to sort and this piece is not about sorting algorythms, of which there are really quite a few if you look into it. Also, the code presented here is not really intended to sort large collections, as not much of an attempt has been made to optimize it for speed. But this code does show you how to handle the issues you will come across if you need generic code to sort objects that are contained in a VB Collection.

There are four main problems:

• You can't just move items around in a collection (at least not easily)
• Which property of the contained objects do you want to sort on?
• How do you generically call that property?
• How do you generically handle sorting on different data types?

Fortunately these are not too dificult to solve. Lets take the first issue - because its not really practical to move items around in a collection - the best approach is to simply make a new collection as you spin through the original collection. In fact this approach, coupled with the VB Collection's Add method optional parameters allow you to sort the data in essentially one pass.

The second and third issues can be handled with VB's CallByName function - probably the most under used function in VB. If you are into writing generic code - you should really look into this function as it provides some great flexibility in a lot of different situations. There are some caveats to be aware of if what you are calling can raise an error though - so look into the Knowledge Base about CallByName if you are interested. But here we won't deal with that since we are assuming that we are calling fairly safe code (in fact whatever property you are sorting the objects by had better return its value quickly - or your sort performance will really stink).

And the final issue presented above can easily be handled with a flag parameter to the function to indicate what kind of sort to do.

Lets look at the code.

Code below provides the following functionality:

• Sort a collection of objects
• Sort can be by any named property on the contained objects
• Support for numeric or string sorts (date sorts could be added easily too)

1. Create a new standard project
2. Add 1 listbox, and a button called 'btnSort'
3. Set the Caption of the command button to Sort
4. Create a class called 'clsTest.' This will be our object in the collection to test with.
5. Create a module (bas file) called 'modItemSort.' This will hold the generic sorting routine.
6. Cut and Paste the code below into the form, the class, and the module, as appropriate.

Option Explicit

Public Function SortItemCollection(col As Collection, strPropertyName, Optional blnCompareNumeric As Boolean = False) As Collection
    Dim colNew As Collection
    Dim objCurrent As Object
    Dim objCompare As Object
    Dim lngCompareIndex As Long
    Dim strCurrent As String
    Dim strCompare As String
    Dim blnGreaterValueFound As Boolean

    'make a copy of the collection, ripping through it one item
    'at a time, adding to new collection in right order...
    
    Set colNew = New Collection
    
    For Each objCurrent In col
    
        'get value of current item...
        strCurrent = CallByName(objCurrent, strPropertyName, VbGet)
        
        'setup for compare loop
        blnGreaterValueFound = False
        lngCompareIndex = 0
        
        For Each objCompare In colNew
            lngCompareIndex = lngCompareIndex + 1
            
            strCompare = CallByName(objCompare, strPropertyName, VbGet)
            
            'optimization - instead of doing this for every iteration, have 2 different loops...
            If blnCompareNumeric = True Then
                'this means we are looking for a numeric sort order...
                
                If Val(strCurrent) < Val(strCompare) Then
                    'found an item in compare collection that is greater...
                    'add it to the new collection...
                    blnGreaterValueFound = True
                    colNew.Add objCurrent, , lngCompareIndex
                    Exit For
                End If
                
            Else
                'this means we are looking for a string sort...
                
                If strCurrent < strCompare Then
                    'found an item in compare collection that is greater...
                    'add it to the new collection...
                    blnGreaterValueFound = True
                    colNew.Add objCurrent, , lngCompareIndex
                    Exit For
                End If
            
            End If
        Next
        
        'if we didn't find something bigger, just add it to the end of the new collection...
        If blnGreaterValueFound = False Then
            colNew.Add objCurrent
        End If
              
    Next

    'return the new collection...
    Set SortItemCollection = colNew
    Set colNew = Nothing

End Function

Option Explicit

Public ID As Long
Public NameValue As String

Option Explicit

Private col As Collection

Private Sub btnSort_Click()
    Dim obj As clsTest

    Set col = SortItemCollection(col, "ID", True)
    
    List1.Clear
    
    For Each obj In col
        List1.AddItem obj.NameValue
    Next

End Sub

Private Sub Form_Load()
    Dim obj As clsTest

    'build a collection of test objects when the form loads...
    
    Set col = New Collection
    
    Set obj = New clsTest
    obj.ID = 6
    obj.NameValue = "Item Six"
    col.Add obj
    
    Set obj = New clsTest
    obj.ID = 2
    obj.NameValue = "Item Two"
    col.Add obj
    
    Set obj = New clsTest
    obj.ID = 3
    obj.NameValue = "Item Three"
    col.Add obj
    
    Set obj = New clsTest
    obj.ID = 1
    obj.NameValue = "Item One"
    col.Add obj
    
    Set obj = New clsTest
    obj.ID = 4
    obj.NameValue = "Item Four"
    col.Add obj
    
    Set obj = New clsTest
    obj.ID = 5
    obj.NameValue = "Item Five"
    col.Add obj
    
    Set obj = New clsTest
    obj.ID = 7
    obj.NameValue = "Item Seven"
    col.Add obj
    
    
    For Each obj In col
        List1.AddItem obj.NameValue
    Next
    
End Sub