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
- Open the workbook in which to store the code.
- Open the Visual Basic Editor (Alt+F11).
- Insert a standard module (Insert > Module).
- Copy/paste the above code into the module.
- Return to Microsoft Excel (Alt+Q).
- Save the workbook.
How to Use the Macro
- Display the Macro dialog box (Alt+F8).
- Click/select the macro called "SortCollection".
- Click/select "Run".
Sample Workbook: Download