« Add New Column to External Data
Making the Best Choice II »

Sort a Collection

June 02, 2004, 12:48 pm by Dick Kusleika

This post demonstrates how to sort a collection.

Sub SortCollection()

    Dim cFruit As Collection
    Dim vItm As Variant
    Dim i As Long, j As Long
    Dim vTemp As Variant
   
    Set cFruit = New Collection
   
    'fill the collection
    cFruit.Add "Mango", "Mango"
    cFruit.Add "Apple", "Apple"
    cFruit.Add "Peach", "Peach"
    cFruit.Add "Kiwi", "Kiwi"
    cFruit.Add "Lime", "Lime"
   
    'Two loops to bubble sort
    For i = 1 To cFruit.Count - 1
        For j = i + 1 To cFruit.Count
            If cFruit(i) > cFruit(j) Then
                'store the lesser item
                vTemp = cFruit(j)
                'remove the lesser item
                cFruit.Remove j
                're-add the lesser item before the
                'greater Item
                cFruit.Add vTemp, vTemp, i
            End If
        Next j
    Next i
   
    'Test it
    For Each vItm In cFruit
        Debug.Print vItm
    Next vItm

End Sub

The above code sorts a collection that uses keys that are the same as the items. This is a common use for collections particularly when you use the collection to get a unique list. It also works for collections where the key is not specified, with the following line changed

cFruit.Add vTemp, , i

This leaves me with two problems. First, I want a general purpose collection sorting routine. But how can I determine if the collection uses user-defined keys or default keys? Second, what if the keys are different than the items AND not default keys? Basically, I want to pass any of the three following collections to a procedure and sort it, maintaining the keys if they exist. Can it be done?

'No keys
cFruit.Add "Mango"
cFruit.Add "Apple"
cFruit.Add "Peach"
cFruit.Add "Kiwi"
cFruit.Add "Lime"

'Keys same as items
cFruit.Add "Mango", "Mango"
cFruit.Add "Apple", "Apple"
cFruit.Add "Peach", "Peach"
cFruit.Add "Kiwi", "Kiwi"
cFruit.Add "Lime", "Lime"

'Keys different than items
cFruit.Add "Mango", "M321"
cFruit.Add "Apple", "A679"
cFruit.Add "Peach", "P581"
cFruit.Add "Kiwi", "K633"
cFruit.Add "Lime", "L745"
Category: VBA Advanced  |  Comment (RSS)  |  Trackback

One Comment

  1. Chris says:
    December 09, 2005 at 5:39 am

    Hi,

    it isn´t possible to get the keys of a collections. You can only check if a key exsists (http://www.vb-tec.de/collctns.htm text is german, but sourcecode is in english). So, if you know all keys of the collection you are lucky.
    What about this sub:

    Sub sortCollection(Data As Collection, Optional Keys = False)

    Dim vItm As Variant
    Dim i As Long, j As Long
    Dim vTemp As Variant

    If Keys = True Then
    For i = 1 To Data.Count - 1
    For j = i + 1 To Data.Count
    If Data(i) > Data(j) Then
    ’store the lesser item
    vTemp = Data(j)
    ‘remove the lesser item
    Data.Remove j
    ‘re-add the lesser item before the
    ‘greater Item
    Data.Add vTemp, vTemp, i
    End If
    Next j
    Next i
    Else
    For i = 1 To Data.Count - 1
    For j = i + 1 To Data.Count
    If Data(i) > Data(j) Then
    ’store the lesser item
    vTemp = Data(j)
    ‘remove the lesser item
    Data.Remove j
    ‘re-add the lesser item before the
    ‘greater Item
    Data.Add vTemp, , i
    End If
    Next j
    Next i
    End If
    End Sub

    It can´t maintain your userdef keys, but can sort both collections: collections with keys same as values and collections without keys. If you pass a collection with userdef. keys you will get a collection whitch has keys and values the same…
    Thank you for this small code, i find it very useful.

    greetings,
    Chris

To post VBA code in your comment, use <code> tags, like this: <code lang="vb">Code goes here</code>.

Leave a Reply