Excel VBA Dictionary For Each: Looping Through Keys

VBA & macros · Updated November 21, 2026

In Excel VBA, For Each loops through the keys of a Scripting.Dictionary when you write For Each key In dict. Declare the loop variable as a Variant, then use each key to retrieve its associated value, for example with dict(key).

What For Each does with a dictionary

A dictionary stores pairs: a unique key and its corresponding item, often called a value. A key might be an invoice number, product code, or employee ID; its item might be a status, price, or name. Unlike a worksheet range, a dictionary is designed for looking up an item by key and checking whether a key exists.

When VBA enumerates a Scripting.Dictionary directly, the loop variable receives one key at a time. It does not receive the item automatically. This is why a useful loop commonly has two parts: get the current key, then use that key to access the matching item. Keys are unique in a dictionary, but values do not have to be unique.

The loop variable should usually be declared As Variant. A dictionary can contain different kinds of keys, and VBA’s For Each control variable must be a Variant or an object reference for this kind of enumeration. Declaring it as String can cause a compile error.

A basic loop through keys and values

The following Windows VBA example creates a dictionary, adds two records, and prints each key alongside its value in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G to see the output.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, even if the macro changes worksheet data.

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

    ' Create a late-bound Scripting.Dictionary.
    Set dict = CreateObject("Scripting.Dictionary")

    ' Add a unique key and its associated value.
    dict.Add "A-101", "Open"
    dict.Add "A-102", "Closed"

    ' For Each enumerates dictionary keys.
    For Each key In dict
        Debug.Print CStr(key) & " - " & CStr(dict(key))
    Next key
End Sub

The Immediate window displays A-101 - Open and A-102 - Closed. The example uses Debug.Print so it does not write to a worksheet. To write to cells instead, you could assign the key and item to cells inside the loop, while keeping track of the output row.

In this example, dict(key) retrieves the item whose key is currently in the loop variable. CStr converts the key and item to strings for display; it is not required when using values in their original types.

Loop through items instead of keys

If you need the values but not their keys, use the dictionary’s Items property. It returns an array of the stored items, which VBA can enumerate with For Each. Declare the loop variable as a Variant here too, particularly if the items may include different data types.

Sub LoopThroughDictionaryItems()
    Dim dict As Object
    Dim itemValue As Variant

    ' Create and populate the dictionary.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A-101", "Open"
    dict.Add "A-102", "Closed"

    ' Items returns the dictionary's values as an array.
    For Each itemValue In dict.Items
        Debug.Print CStr(itemValue)
    Next itemValue
End Sub

This prints Open and Closed, without identifying which key belongs to each status. If you need both pieces of information, looping through keys and looking up dict(key) is generally clearer. Values can repeat, so a values-only loop also cannot tell you whether two identical results came from different records.

Using Keys and Items explicitly

The Keys property returns an array of dictionary keys, and Items returns an array of the corresponding values. These properties can be useful when you want to save the list for another operation or pass it to code that expects an array. A direct loop over dict is shorter when you only need to visit the keys.

For example, For Each key In dict.Keys iterates over the array returned by Keys. This is not a separate way to enumerate dictionary values: the loop variable still receives keys. To get the associated item, use dict(key). The key and item arrays correspond by position, but using the dictionary lookup makes the relationship more explicit and avoids confusing the two arrays.

Do not rely on a dictionary’s iteration order for a report that must be sorted. A dictionary is for keyed lookup, not a promise of alphabetical or worksheet-row order. If order matters, retrieve the keys and sort them separately, or place the results in a range and use Excel’s sorting tools.

Creating a dictionary in Excel for Windows

The example uses late binding: CreateObject("Scripting.Dictionary") creates the object at run time, and the variable is declared As Object. This avoids setting a project reference and is convenient when sharing a macro between Windows computers with different reference configurations.

With early binding, you can declare a variable as Scripting.Dictionary and receive editor assistance such as completion for its members. In the Visual Basic Editor on Windows, choose Tools > References and select Microsoft Scripting Runtime. Early binding depends on that reference being available and correctly set in the project. If another computer cannot resolve it, the project may report a missing reference; late binding avoids that specific compile-time dependency.

These examples target desktop Excel for Microsoft 365 on Windows. VBA does not run in Excel for the web. On Mac, do not assume that the Windows COM object created by CreateObject("Scripting.Dictionary") is available; the Scripting Runtime is Windows-focused and support can differ. A VBA Collection or a cross-platform approach may be more suitable when the same workbook must run on both platforms.

Checking keys and choosing comparison rules

Use the dictionary’s Exists method to test whether a key is present before using it. This matters because retrieving an item through the Item property with a missing key can create that key with an empty item. A seemingly harmless lookup can therefore change the dictionary.

For example, a safe pattern is If dict.Exists(key) Then followed by reading dict(key). To add a new key only when it is absent, check Exists before calling Add. Calling Add with a duplicate key raises an error, so this check is useful when input data may contain repeated identifiers.

Dictionary key comparison is controlled by CompareMode. The default is binary comparison, so text keys such as "North" and "north" are treated as different keys. If you want case-insensitive text matching, set dict.CompareMode = vbTextCompare after creating the dictionary but before adding any entries. Changing the comparison mode after entries have been added can raise an error. Choose the rule deliberately: treating differently capitalized IDs as the same key may be wrong for some data.

Changing a dictionary while looping

Avoid adding or removing entries from a dictionary while directly enumerating it. Altering the collection during enumeration can disrupt the loop or cause a run-time error, and the result is harder to reason about. If you need to remove selected keys, first save the keys, then loop over that array and remove entries from the dictionary.

For example, you can assign dict.Keys to a Variant array before the loop, then test each saved key and remove matching entries. The array represents the keys captured at that point; it is not a live view of later dictionary changes. If other code can change the dictionary during the operation, plan for keys that may no longer exist and check Exists before removing them.

Updating the item for a key you are currently processing is different from adding or removing keys, but keep the loop’s purpose clear. If you are transforming many entries, a second pass or a separate output dictionary is often easier to maintain than changing the structure mid-loop.

Common errors and practical fixes

  • Compile error on the loop variable: Change a String loop variable to Variant, such as Dim key As Variant.
  • Duplicate-key error: Check dict.Exists(key) before calling Add, or deliberately update the existing item rather than adding a duplicate.
  • Unexpected empty result: Confirm that the key exists and that its spelling, spaces, capitalization, and data type match the key that was added.
  • Missing reference: If using early binding, check Tools > References in the Visual Basic Editor for a missing Microsoft Scripting Runtime reference. Alternatively, use late binding on Windows.
  • Macro does not run: Check that the workbook is macro-enabled, such as an .xlsm file, and that macros are allowed under your organization’s security settings. Do not enable macros in an untrusted workbook just to make its code run.

Also distinguish a dictionary key from a worksheet row number. A key is an identifier chosen by your code; it does not automatically refer to the row where the source data appeared. If you need to return a row, store the row number as the item, or store a record containing the information you need.

Dictionary versus Collection or worksheet lookup

A dictionary is useful when you need unique keys, quick existence checks, and direct access to the item associated with a key. A VBA Collection can also hold items and can be accessed by a key, but it does not provide the same built-in Exists method. A collection can suit simpler tasks or code that needs to work in environments where the Windows Scripting Runtime is unavailable, though checking for a missing key takes extra handling.

For data that should remain visible and editable in a workbook, a worksheet table may be a better store than an in-memory dictionary. Excel lookup functions can also retrieve values from ranges without VBA. Choose a dictionary when the code needs to build or query a temporary keyed structure; choose a table or formula when users need to inspect, filter, or update the source data directly. A dictionary’s contents exist in memory and are not automatically saved to cells or to the workbook.

Frequently Asked Questions

Does For Each loop through dictionary keys or values?
When used directly as For Each key In dict, it loops through the keys. Use dict(key) to get each associated item, or loop through dict.Items when you only need values.

What variable type should I use for a dictionary loop?
Use a variable declared As Variant for keys or items that may be different data types. This is the standard safe choice for a For Each loop over a Scripting.Dictionary.

Can a dictionary contain duplicate keys?
No. Each key must be unique. Use Exists before adding a key if input data may repeat it; values, unlike keys, can be duplicated.

How do I loop through dictionary values only?
Use For Each itemValue In dict.Items, with itemValue declared as a Variant. This visits the values but does not tell you which key belongs to each one.

Will Scripting.Dictionary work in Excel for the web or on Mac?
Excel for the web does not run VBA macros. The Scripting.Dictionary approach shown here targets Excel for Windows; because it relies on a Windows COM object, do not assume it will work on Mac. Consider a Collection or another cross-platform design if Mac support is required.

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.