|How to sort objects in a collection||
|NT, 9x, 2000
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:
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:
|Add the following to the module|
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
|Add the following to the Class|
Option Explicit Public ID As Long Public NameValue As String
|Add the following to the Form|
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