Sort a Dictionary by Key

The following procedure sorts the Dictionary by Key, 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 SortDictionaryByKey()

    '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 TempDict As Scripting.Dictionary
    Dim KeyVal As Variant
    Dim Arr() As Variant
    Dim Temp 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)
    
    'Fill the array with the keys from the Dictionary
    For i = 0 To Dict.Count - 1
        Arr(i) = Dict.Keys(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
    
    'Create an instance of the temporary Dictionary
    Set TempDict = New Dictionary
    
    'Add the keys and items to the temporary Dictionary,
    'using the sorted keys from the array
    For i = LBound(Arr) To UBound(Arr)
        KeyVal = Arr(i)
        TempDict.Add Key:=KeyVal, Item:=Dict.Item(KeyVal)
    Next i
    
    'Set the Dict object to the TempDict object
    Set Dict = TempDict
    
    'Build a list of keys and items from the original 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 "SortDictionaryByKey".
  3. Click/select "Run".

Sample Workbook: Download

Home | Excel | VBA | Blog | About | Contact