VBA Sort Dictionary Keys: Methods and Examples

VBA & macros · Updated November 18, 2026

To sort VBA dictionary keys, copy them into the array returned by Dictionary.Keys, sort that array, and loop through it. A Scripting.Dictionary does not provide a method that sorts its keys or guarantees that its own enumeration order is sorted; sorting the separate array leaves the dictionary intact.

What a VBA dictionary does—and does not—sort

A dictionary stores key-value pairs so code can retrieve a value using its key, for example dict("pear"). In Excel VBA on Windows, the commonly used implementation is Scripting.Dictionary. Its Keys method returns an array of the current keys, which is the convenient starting point for sorting.

There is an important distinction between sorting a list of keys and sorting the dictionary itself. A sorted array can control the order in which your macro prints, writes, or processes entries. It does not rearrange the dictionary, and you should not rely on the order returned by a dictionary as a sorting guarantee. Keep the dictionary for lookups and use a separate ordered array when sequence matters.

This approach is useful for tasks such as producing an alphabetized report, writing keys to a worksheet in order, or processing numeric identifiers from smallest to largest. The right comparison rule depends on the keys: alphabetical text, numeric values, and text that contains digits can each sort differently.

Sort text keys with a reusable example

VBA does not include a built-in array sort for this use, so the example below uses a quicksort routine. It sorts text keys alphabetically without regard to case, while retaining the original key text for dictionary lookups. The example uses late binding, so it does not require adding a reference to the Microsoft Scripting Runtime on Windows.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a macro that writes results to cells may overwrite existing data. This example only prints results in the Immediate window, which you can open in the VBA editor with Ctrl+G.

Option Explicit

Sub PrintSortedDictionaryKeys()
    Dim dict As Object
    Dim keys As Variant
    Dim i As Long

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare ' Set before adding keys.

    dict.Add "pear", 3
    dict.Add "Apple", 1
    dict.Add "plum", 2

    If dict.Count > 0 Then
        keys = dict.Keys
        If dict.Count > 1 Then
            QuickSortText keys, LBound(keys), UBound(keys)
        End If

        For i = LBound(keys) To UBound(keys)
            Debug.Print keys(i), dict(keys(i))
        Next i
    End If
End Sub

Private Sub QuickSortText(ByRef items As Variant, _
                          ByVal first As Long, ByVal last As Long)
    Dim low As Long
    Dim high As Long
    Dim pivot As String
    Dim temp As Variant

    low = first
    high = last
    pivot = CStr(items((first + last) \ 2))

    Do While low <= high
        Do While StrComp(CStr(items(low)), pivot, vbTextCompare) < 0
            low = low + 1
        Loop
        Do While StrComp(CStr(items(high)), pivot, vbTextCompare) > 0
            high = high - 1
        Loop

        If low <= high Then
            temp = items(low)
            items(low) = items(high)
            items(high) = temp
            low = low + 1
            high = high - 1
        End If
    Loop

    If first < high Then QuickSortText items, first, high
    If low < last Then QuickSortText items, low, last
End Sub

For the sample, the Immediate window lists Apple, pear, then plum, with each value beside its key. The sort uses StrComp with vbTextCompare, so letter case does not affect the comparison. If you want case-sensitive ordering, replace both occurrences of vbTextCompare in the sort routine with vbBinaryCompare.

What the sorting routine is doing

The macro asks the dictionary for its keys once, then sorts the resulting array in place. Quicksort chooses a middle element as a pivot, moves smaller items to one side and larger items to the other, and repeats that process on the remaining sections. This avoids repeatedly modifying the dictionary and is suitable for ordinary lists of keys.

  • dict.Keys creates the array to sort; it does not return a live, automatically updating view.
  • LBound and UBound give the first and last array indexes, so the code does not assume that an array starts at zero.
  • The count checks avoid trying to sort an empty array. A one-key array needs no sorting.
  • dict(keys(i)) retrieves the associated value after sorting, so key-value relationships remain intact.

The routine is written for text keys. It converts each key to a string for comparison, which is appropriate when all keys are textual, but not a sound way to sort numeric keys: the string value "10" can come before "2" alphabetically.

Sort numeric keys numerically

If the dictionary uses numeric keys, compare them as numbers rather than converting them to text. The following routine expects every item in the array to be convertible to a number. For example, keys 2, 10, and 30 will be ordered as 2, 10, 30, not as the text sequence 10, 2, 30.

Private Sub QuickSortNumbers(ByRef items As Variant, _
                             ByVal first As Long, ByVal last As Long)
    Dim low As Long
    Dim high As Long
    Dim pivot As Double
    Dim temp As Variant

    low = first
    high = last
    pivot = CDbl(items((first + last) \ 2))

    Do While low <= high
        Do While CDbl(items(low)) < pivot
            low = low + 1
        Loop
        Do While CDbl(items(high)) > pivot
            high = high - 1
        Loop

        If low <= high Then
            temp = items(low)
            items(low) = items(high)
            items(high) = temp
            low = low + 1
            high = high - 1
        End If
    Loop

    If first < high Then QuickSortNumbers items, first, high
    If low < last Then QuickSortNumbers items, low, last
End Sub

To use it, assign dict.Keys to a Variant array and call QuickSortNumbers keys, LBound(keys), UBound(keys) when the dictionary has more than one key. Avoid mixing numbers and text in one set unless you define an explicit rule for how different data types should compare. Converting arbitrary mixed keys to numbers can raise a type-mismatch error or produce an unintended order.

Case, accents, and text that contains numbers

Sorting rules are a choice, not an inherent property of a dictionary. vbTextCompare performs a case-insensitive text comparison; vbBinaryCompare compares the underlying text values with case distinctions. The results can differ for uppercase and lowercase keys, so select the mode that matches the report or process you need.

Also distinguish the dictionary’s CompareMode from the sort routine’s comparison. CompareMode controls how the dictionary compares string keys for lookup and duplicate-key purposes. Set it before adding entries; changing it after the dictionary contains data can cause an error. The StrComp argument in the code controls the order of the separate keys array. Setting one does not automatically set the other.

Text containing digits is another common source of surprise. A normal alphabetical comparison may place Item 10 before Item 2, because it compares characters rather than interpreting the digit groups as numbers. If natural ordering is required, split the text into its nonnumeric and numeric parts and compare those parts deliberately, or store a separate numeric sort field. Do not assume that case-insensitive sorting also means natural sorting.

Write the sorted keys to a worksheet

After sorting the array, write it to cells in the loop rather than expecting the dictionary to change. For example, use a row counter and assign each key to a cell such as Cells(rowNumber, 1).Value = keys(i). To write the associated values beside them, use Cells(rowNumber, 2).Value = dict(keys(i)). Qualify Cells with the intended worksheet, such as Worksheets("Report").Cells(...), so the macro does not depend on whichever sheet is active.

If the keys are already in a worksheet range and you only need a sorted list, a worksheet formula may be simpler than a dictionary macro. With keys in A2:A6 in Excel for Microsoft 365 on Windows, enter =SORT(A2:A6) in an empty cell to return the keys in ascending order as a spilled array. SORT is available in Microsoft 365 and Excel 2021 or later; it sorts the worksheet range, not the contents of a VBA dictionary. Excel for the web can calculate worksheet formulas, but it does not run VBA macros.

Sort by dictionary values instead

Sometimes “sort dictionary keys” really means “show the entries in order of their values.” Sorting only the keys alphabetically will not do that. In that case, sort an array of keys using the corresponding dictionary value as the comparison field, then retrieve both the key and value during output. If values can tie, choose a tie-breaker—for example, compare the keys alphabetically when values are equal—so the output order is predictable.

For large or more complex data, it can be clearer to copy key-value pairs into a two-dimensional array or worksheet range and sort by the value column. That makes the sort field visible and accommodates multiple criteria. The dictionary remains useful for fast lookups, while the array or worksheet holds the ordered presentation. If you instead build a second dictionary by adding entries in sorted order, do not treat its enumeration order as a contractual guarantee; retain the sorted array when exact output order matters.

Common problems and practical fixes

  • “Object required” or creation fails: CreateObject("Scripting.Dictionary") uses a Windows COM component. Confirm that the macro is running in desktop Excel for Windows and that macros are permitted by your organization’s settings.
  • Empty dictionary errors: Do not call LBound or UBound before checking that the dictionary contains keys. The example guards the array operations with dict.Count > 0.
  • Duplicate keys: A dictionary cannot hold two entries that it considers the same key. With a text comparison mode, keys differing only by case may be treated as duplicates. Decide the comparison mode before adding data.
  • Unexpected numeric order: Use a numeric comparator for numeric keys. A text comparator sorts character strings, not numeric magnitudes.
  • Output appears unsorted: Check that the loop uses the sorted keys array, rather than looping over the dictionary itself. The sort routine changes the array only.
  • Macro changes cannot be reversed: Save a workbook copy before running code that writes to cells. Ctrl+Z does not undo VBA macro actions.

The examples target Excel for Microsoft 365 on Windows. Excel for Mac supports VBA, but the Windows COM-based Scripting.Dictionary object is not generally available there, so CreateObject may fail. For a Mac-compatible project, consider a VBA array with a sorting routine, or a suitable custom collection or class, and test it in the target Excel version. The same sorted-array principle still applies: build an ordered structure for output rather than expecting a dictionary to sort itself.

Frequently Asked Questions

Can a VBA dictionary sort its keys automatically?
No. Scripting.Dictionary has no built-in key-sorting method. Get its keys with Keys, sort that array, and use the array to control output order.

Does sorting the keys array change the dictionary?
No. The array returned by Keys is separate from the dictionary. Its order changes, but dictionary entries and their associated values remain unchanged.

How do I sort dictionary keys in descending order?
Reverse the comparison direction in the sort routine or sort in ascending order and traverse the array from its last index to its first. For descending text order, use the reverse loop comparison consistently.

How do I sort keys numerically instead of alphabetically?
Use a numeric comparison, such as comparing CDbl(items(i)) values in a numeric sort routine. This assumes every key can be converted to a number and that the keys are intended to be numeric.

Can I sort a dictionary by its values?
Yes, but compare the value associated with each key while sorting the key array, or copy the pairs into an array or worksheet and sort by the value field. Sorting keys alone does not sort values.

Will this CreateObject example work in Excel for Mac?
Not reliably. The example relies on the Windows Scripting.Dictionary COM object, which is generally unavailable in Mac Excel. A native array-based approach or a custom VBA structure is a safer cross-platform choice.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.