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.Keyscreates the array to sort; it does not return a live, automatically updating view.LBoundandUBoundgive 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
LBoundorUBoundbefore checking that the dictionary contains keys. The example guards the array operations withdict.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
keysarray, 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
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA AutoFilter: Filter Text with Like PatternsTo filter rows in Excel VBA for text that is like a pattern, use the AutoFilter method with…
- Excel VBA: How to Delete a Worksheet ButtonTo delete a button from an Excel worksheet, select the button itself and press Delete; for a Form…
- VBA Function Statement: Syntax, Scope, and ExamplesThe VBA Function statement declares a procedure that performs a task and returns a value. You can call…
- VBA Excel String Variables: Types, Examples, and TipsIn Excel VBA, declare a string variable with Dim name As String; it stores text such as names,…
- VBA MsgBox Options: Buttons, Icons, and Return ValuesIn VBA, MsgBox displays a dialog with a prompt, optional buttons, an icon, a title, and other settings.…
- VBA Array Cells: Read and Write Ranges EfficientlyIn Excel VBA, you can read a block of worksheet cells into a Variant array with one assignment,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.