Sort a Dictionary by Item

The following procedure sorts the Dictionary by Item, and displays the keys and items from the Dictionary in a message box. Note that the Dictionary object is a component of the Microsoft Scripting library, which requires Excel 2000 or later. Also, you'll need to set a reference to Microsoft Scripting Runtime by using Tools > References in the Visual Basic Editor (Alt+F11).

'Force the explicit declaration of variables
Option Explicit

Sub SortDictionaryByItem()

    'Set a reference to Microsoft Scripting Runtime by using
    'Tools > References in the Visual Basic Editor (Alt+F11)

    'Declare the variables
    Dim Dict As Scripting.Dictionary
    Dim Arr() As Variant
    Dim Temp1 As Variant
    Dim Temp2 As Variant
    Dim Txt As String
    Dim i As Long
    Dim j As Long
    
    'Create an instance of the Dictionary
    Set Dict = New Dictionary
    
    'Set the comparison mode to perform a textual comparison
    Dict.CompareMode = TextCompare
    
    'Add keys and items to the Dictionary
    Dict.Add "Mango", "M250"
    Dict.Add "Kiwi", "K150"
    Dict.Add "Apple", "A325"
    Dict.Add "Peach", "P350"
    Dict.Add "Lime", "L275"
    
    'Allocate storage space for the dynamic array
    ReDim Arr(0 To Dict.Count - 1, 0 To 1)
    
    'Fill the array with the keys and items from the Dictionary
    For i = 0 To Dict.Count - 1
        Arr(i, 0) = Dict.Keys(i)
        Arr(i, 1) = Dict.Items(i)
    Next i
    
    'Sort the array using the bubble sort method
    For i = LBound(Arr, 1) To UBound(Arr, 1) - 1
        For j = i + 1 To UBound(Arr, 1)
            If Arr(i, 1) > Arr(j, 1) Then
                Temp1 = Arr(j, 0)
                Temp2 = Arr(j, 1)
                Arr(j, 0) = Arr(i, 0)
                Arr(j, 1) = Arr(i, 1)
                Arr(i, 0) = Temp1
                Arr(i, 1) = Temp2
            End If
        Next j
    Next i
    
    'Clear the Dictionary
    Dict.RemoveAll
    
    'Add the sorted keys and items from the array back to the Dictionary
    For i = LBound(Arr, 1) To UBound(Arr, 1)
        Dict.Add Key:=Arr(i, 0), Item:=Arr(i, 1)
    Next i
    
    'Build a list of keys and items from the Dictionary
    For i = 0 To Dict.Count - 1
        Txt = Txt & Dict.Keys(i) & vbTab & Dict.Items(i) & vbCrLf
    Next i
    
    '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 "SortDictionaryByItem".
  3. Click/select "Run".

Sample Workbook: Download

Home | Excel | VBA | Blog | About | Contact