Sort a Collection

The following procedure sorts a Collection by Item, and displays the items from the Collection in a message box.  Note, however, it does not preserve the keys associated with the items. To preserve the keys, use the procedure called Sort a Dictionary by Item. To sort by and preserve keys, use the procedure called Sort a Dictionary by Key.

'Force the explicit declaration of variables
Option Explicit

Sub SortCollection()

    'Declare the variables
    Dim Coll As New Collection
    Dim Itm As Variant
    Dim Arr() As Variant
    Dim Temp As Variant
    Dim Txt As String
    Dim i As Long
    Dim j As Long
    'Add items to the Collection
    Coll.Add "Mango", "Mango"
    Coll.Add "Kiwi", "Kiwi"
    Coll.Add "Apple", "Apple"
    Coll.Add "Peach", "Peach"
    Coll.Add "Lime", "Lime"
    'Allocate storage space for the dynamic array
    ReDim Arr(1 To Coll.Count)
    'Fill the array with items from the Collection
    For i = 1 To Coll.Count
        Arr(i) = Coll.Item(i)
    Next i
    'Sort the array using the bubble sort method
    For i = LBound(Arr) To UBound(Arr) - 1
        For j = i + 1 To UBound(Arr)
            If Arr(i) > Arr(j) Then
                Temp = Arr(j)
                Arr(j) = Arr(i)
                Arr(i) = Temp
            End If
        Next j
    Next i
    'Remove all items from the Collection
    Set Coll = Nothing
    'Add the sorted items from the array back to the Collection
    For i = LBound(Arr) To UBound(Arr)
        Coll.Add Item:=Arr(i)
    Next i
    'Build a list of items from the Collection
    For Each Itm In Coll
        Txt = Txt & Itm & vbCrLf
    Next Itm
    'Display the list in a message box
    MsgBox Txt, vbInformation
End Sub

Where to Put the Code

  1. Open the workbook in which to store the code.
  2. Open the Visual Basic Editor (Alt+F11).
  3. Insert a standard module (Insert > Module).
  4. Copy/paste the above code into the module.
  5. Return to Microsoft Excel (Alt+Q).
  6. Save the workbook.

How to Use the Macro

  1. Display the Macro dialog box (Alt+F8).
  2. Click/select the macro called "SortCollection".
  3. Click/select "Run".

Sample Workbook: Download