Sort a Collection

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"

2 thoughts on “Sort a Collection

  1. 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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.