VBA Loop Through Dictionary Keys: Examples and Tips

VBA & macros · Updated November 18, 2026

To loop through the keys in a VBA Scripting.Dictionary, use For Each with a Variant control variable: For Each key In dict.Keys. The loop visits each key once; use dict(key) inside it when you also need the value stored under that key.

What a VBA dictionary contains

A dictionary stores pairs: a key and its associated item. Keys are used to find items, much like labels in a lookup table. For example, a dictionary might associate the key "North" with the value 125, or an employee ID with a name. Unlike a worksheet range, a dictionary is an in-memory object; its contents are not automatically written to cells or saved with the workbook.

In Excel VBA on Windows, the usual dictionary is Scripting.Dictionary, supplied by the Windows Scripting Runtime. VBA exposes its keys through the Keys method, which returns an array of keys. You can enumerate that array directly with For Each. The loop variable should be a Variant, because dictionary keys can have different data types.

The basic For Each pattern

This example creates a dictionary and prints its keys to the Immediate window in the Visual Basic Editor. It uses late binding, so it does not require you to add a reference manually.

Sub PrintDictionaryKeys()
    Dim dict As Object
    Dim key As Variant

    ' Create a Windows Scripting.Dictionary.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 125
    dict.Add "South", 98
    dict.Add "West", 143

    ' Visit each key and print it in the Immediate window.
    For Each key In dict.Keys
        Debug.Print key
    Next key
End Sub

To view the output, open the Visual Basic Editor and choose View > Immediate Window, or press Ctrl+G while the editor is active on Windows. On a Mac, the editor shortcuts can vary with the keyboard and Excel version; use the Visual Basic Editor’s View menu if a shortcut does not work. The order printed is not a sorting guarantee. Do not write code that depends on a dictionary returning keys alphabetically or in a particular order.

Before running a macro that changes a workbook, save a copy. VBA macros cannot be undone with Ctrl+Z. This example only prints text, but many real loops also edit cells or remove dictionary entries.

Loop through keys and read their values

The key identifies the corresponding item, so retrieve it with the dictionary’s item property. In VBA, dict(key) is the common shorthand for reading the item associated with that key. This version prints a key and its value on the same line:

Sub PrintDictionaryPairs()
    Dim dict As Object
    Dim key As Variant

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 125
    dict.Add "South", 98
    dict.Add "West", 143

    For Each key In dict.Keys
        Debug.Print CStr(key) & ": " & dict(key)
    Next key
End Sub

CStr converts the key to text for display. It is useful when keys might not already be strings, but it does not change the key stored in the dictionary. The item itself can be a number, text, object reference, or another kind of value, so code that performs calculations should confirm that the stored items are numeric before treating them as numbers.

For a worksheet task, the loop body could instead write values to cells, build a report, or call another procedure. Keep the key and item roles distinct: key is the lookup label, while dict(key) is the associated item. This distinction matters when a dictionary contains a key named "Total" whose item happens to be a number; the key is not the number itself.

Use a keys array when the loop needs a stable list

For a short read-only loop, For Each key In dict.Keys is concise. If you want to make the list of keys explicit, store the result of Keys in a variable first. This can make the code easier to inspect and is especially useful when the loop will remove entries from the dictionary.

Sub PrintKeysFromArray()
    Dim dict As Object
    Dim keys As Variant
    Dim key As Variant

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 125
    dict.Add "South", 98

    If dict.Count > 0 Then
        keys = dict.Keys
        For Each key In keys
            Debug.Print key
        Next key
    End If
End Sub

The count check makes the empty case explicit and avoids assuming that a returned key array has a usable first or last index. A frequent mistake is to use For i = LBound(dict.Keys) To UBound(dict.Keys) without accounting for an empty dictionary. Bounds operations on an empty result can fail. If you use numeric indexing for other reasons, first check dict.Count, assign dict.Keys to a variable, and then use that array’s bounds.

Removing entries and changing data during a loop

A common related task is deleting dictionary entries that meet a condition. Avoid adding or removing dictionary keys while enumerating the dictionary directly. Structural changes can disrupt enumeration, and code that appears to work for one set of data may fail or skip work with another. A safer pattern is to capture the keys first, then use that separate array while changing the dictionary:

Sub RemoveNegativeItems()
    Dim dict As Object
    Dim keys As Variant
    Dim key As Variant

    ' Save a copy of the workbook before running a macro that changes data.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A", 10
    dict.Add "B", -3
    dict.Add "C", 7

    If dict.Count = 0 Then Exit Sub
    keys = dict.Keys

    For Each key In keys
        If dict(key) < 0 Then
            dict.Remove key
        End If
    Next key
End Sub

The captured array is the list to visit; removing an entry from the dictionary does not remove that key from the already captured array. The example assumes each item is numeric. If items could be text, blank, or an object, add suitable checks before comparing them with zero. Updating an existing item’s value is different from adding or removing a key, but keeping dictionary structure unchanged during direct enumeration is the clearest general practice.

Be careful when changing keys. A key is the dictionary’s lookup identity, not merely a displayed label. If you rename a key during a loop over a saved array, the array still contains the original key; later code may need to check whether that old key still exists before trying to retrieve or remove it.

Key comparison, duplicate keys, and ordering

Dictionary behavior depends partly on CompareMode, which controls how text keys are compared. The default is binary comparison, where letter case matters: "North" and "north" can be different keys. To make text-key matching case-insensitive, set CompareMode to vbTextCompare before adding entries. Setting the mode after the dictionary already contains data can raise an error.

A dictionary cannot contain duplicate keys under its comparison rules. Adding a key that is already present raises an error; test with dict.Exists(key) if the key may already have been added. For example, a case-insensitive dictionary treats "North" and "north" as a duplicate. Establish the comparison mode before populating the dictionary so the behavior is predictable.

Dictionary enumeration is not a substitute for sorting. If your output must be alphabetical or follow a particular numeric order, collect the keys and sort them separately before processing, or write the output to a range and sort that range. Do not infer a permanent order from the order in which keys were added or from a particular run of the macro.

Late binding, early binding, and Mac considerations

The examples use late binding: Dim dict As Object and CreateObject("Scripting.Dictionary"). It avoids a compile-time reference setting and is convenient for sharing a workbook among Windows users. The trade-off is that the VBA editor offers less assistance with the object’s members, and some mistakes are only discovered at run time.

With early binding on Windows, in the Visual Basic Editor choose Tools > References and select Microsoft Scripting Runtime, then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. The reference provides type information and editor autocomplete. A workbook that depends on that reference can encounter a missing-reference problem on another computer if the library is unavailable or not registered there.

Excel for Mac does not provide the same Windows COM environment for the Scripting Runtime, so do not assume that CreateObject("Scripting.Dictionary") will work cross-platform. If the macro must run on both Windows and Mac, consider VBA’s built-in Collection or a design based on arrays and worksheet ranges, and verify the chosen approach in the target Excel versions. Excel for the web does not run VBA macros; use desktop Excel to run this code.

Common errors and practical checks

  • Using a String or Long loop variable: declare the For Each variable as Variant. This accommodates keys of different types and avoids a common compile-time error.
  • Reading a missing key: check dict.Exists(key) before retrieving an item when the key may not be present. Otherwise, code can raise an error or create unintended data if it assigns through an absent key.
  • Assuming a worksheet range is a dictionary: a range does not have a Keys method. First create and populate a dictionary, or use a range-specific loop instead.
  • Changing the dictionary while enumerating it: save the keys to an array before adding or removing entries, then process that snapshot deliberately.
  • Expecting sorted output: dictionary keys are not an ordered report. Sort separately if sequence matters.
  • Forgetting the object assignment: when setting an object variable, use Set, as in Set dict = CreateObject(...).

For debugging, inspect dict.Count before the loop and use Debug.Print key to confirm what the loop is visiting. If a loop behaves unexpectedly, check whether the dictionary is empty, whether the key was added with the expected data type and spelling, and whether CompareMode was set before entries were added.

Frequently Asked Questions

How do I loop through dictionary keys and values in VBA?
Use For Each key In dict.Keys, declare key as a Variant, and read the matching value with dict(key) inside the loop.

Can I use For Each directly on dict.Keys?
Yes. Keys returns an array that can be enumerated with For Each. Assign it to a Variant first when you want a clear snapshot or need to alter the dictionary during processing.

What type should the key variable be?
Use Variant for the variable controlling a For Each loop over dictionary keys. It works for string, numeric, and other permitted key types.

How do I check whether a dictionary has any keys?
Check dict.Count. If it is zero, skip the loop. This is also a useful guard before indexing the array returned by dict.Keys.

Can I loop through dictionary keys in alphabetical order?
Not by relying on dictionary enumeration. Copy the keys to an array or a worksheet range, sort them separately, and then process them in the required order.

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.