Excel VBA: How to Loop Through a Dictionary

VBA & macros · Updated November 21, 2026

To loop through a VBA dictionary, use For Each key In dict.Keys, then retrieve each value with dict(key). A dictionary stores data as key–value pairs, so this pattern lets you process each key and its associated item without needing a numeric index.

What a VBA dictionary contains

A dictionary is an object that associates a unique key with a value. For example, a key might be a product code and its value the product name, or a person’s name and their total sales. Unlike a worksheet range, a dictionary is an in-memory data structure: it does not automatically read from or write to cells.

In Excel VBA on Windows, the commonly used dictionary is Scripting.Dictionary, provided by the Microsoft Scripting Runtime. It can hold different kinds of values, including strings, numbers, dates, arrays, and object references. Its keys must be unique. If a key already exists, adding it again with Add raises an error; you can instead check whether it exists or update its value.

Each dictionary entry has two useful parts:

  • Key: the unique identifier used to find an entry.
  • Item: the value associated with that key, accessed as dict(key).

That key-based lookup is the main reason a dictionary can be useful for matching, counting, and grouping data. When you loop, you typically enumerate the keys and use each one to retrieve its item.

The basic For Each loop

Declare the loop variable as a Variant. The Keys property returns the dictionary’s keys as an array, and For Each iterates over that array. For each key, use the key in parentheses to read the corresponding item:

Dim key As Variant

For Each key In dict.Keys
    Debug.Print key, dict(key)
Next key

This prints each key and value to the Immediate window in the VBA editor. To open that window on Windows, press Ctrl+G in the Visual Basic Editor. On Mac, the Visual Basic Editor and available shortcuts can vary by Excel release; use the editor’s View menu to show the Immediate window if the shortcut differs.

Use Variant for the key variable even when you expect text keys. Dictionary keys can be different data types, and the array returned by Keys is naturally handled by a Variant loop variable. You can give the variable a more descriptive name, such as productCode, provided it is still declared as a Variant when the key type is not fixed.

A complete example that creates and loops through a dictionary

This standalone procedure creates a dictionary, adds three entries, and prints the keys and values. It uses late binding, so it does not require you to select a reference in the VBA editor:

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

    ' Create a Scripting.Dictionary on Windows.
    Set dict = CreateObject("Scripting.Dictionary")

    ' Add unique keys and their values.
    dict.Add "A104", "Notebook"
    dict.Add "B207", "Pen"
    dict.Add "C315", "Folder"

    ' Print each key and its associated value.
    For Each key In dict.Keys
        Debug.Print key & ": " & dict(key)
    Next key

    Set dict = Nothing
End Sub

The output is one line per entry, such as A104: Notebook. The ampersand joins the key, separator, and value into a single string. If you adapt this example to change workbook data, save a copy of the workbook before running the macro: VBA macros cannot be undone with Ctrl+Z.

To put the result in worksheet cells instead, assign each output to a range inside the loop and advance a row counter. For example, initialize rowNumber to 2, write the key and value to columns A and B of that row, and increment the counter for each entry. Keep the output range separate from any source data you still need; writing over source cells can replace their contents.

Looping over values and looking up one key

If you need only the values, you can loop through dict.Items. Declare the loop variable as a Variant because the items may not all be the same type:

Dim item As Variant

For Each item In dict.Items
    Debug.Print item
Next item

This is convenient when the keys do not matter. If you need both parts of each entry, loop over keys and use dict(key); doing so keeps the relationship between a value and its identifier clear.

You do not have to loop to retrieve a known entry. If the key is A104, the item can be read directly with dict("A104"). To avoid an error when a key might be absent, first test dict.Exists("A104"). The Exists method returns True when the key is present and False otherwise. This check is useful when processing variable input, such as values imported from a worksheet.

Building a dictionary from worksheet data

A common pattern is to read rows from a worksheet into a dictionary, then loop through the dictionary to calculate or report results. For example, suppose column A contains product codes and column B contains quantities, starting at row 2. You could use each code as a key and its quantity as the item. Before adding a code, check whether it is already present; if it is, add the new quantity to the existing item. This turns repeated product codes into a total for each product.

When loading data, consider what a duplicate key means for your task. You might want to keep the first value, replace it with the latest value, add numeric values together, or flag duplicates as errors. A dictionary does not decide that business rule for you. Also account for blank cells and unexpected values: a blank key may be valid in some contexts but is often a sign of incomplete source data.

For larger ranges, it is often more efficient to read the worksheet range into a VBA array once, process the array and dictionary in memory, and write results back in a block. Repeated cell-by-cell access can be slower because it crosses between VBA and the worksheet object model many times. The best approach depends on the size of the range and what the macro needs to do; a dictionary is not automatically faster for every small task.

Binding options and platform limits

The complete example uses late binding: Dim dict As Object followed by CreateObject("Scripting.Dictionary"). It avoids a project reference and is often convenient when sharing a macro across Windows computers. The trade-off is that the VBA editor cannot provide the same compile-time type checking and member-list assistance it can for an early-bound object.

With early binding on Windows, in the Visual Basic Editor choose Tools > References, select Microsoft Scripting Runtime, and declare the variable as Dim dict As Scripting.Dictionary. Then create it with Set dict = New Scripting.Dictionary. The reference must be available on the computer running the workbook; a missing reference can appear as MISSING in the References list and prevent code from compiling.

Scripting.Dictionary relies on Windows scripting components and is not a portable choice for Excel for Mac. Mac Excel does not provide the same Windows COM-based creation route, so CreateObject("Scripting.Dictionary") may fail there. If a workbook must run on both platforms, consider a VBA Collection or a cross-platform dictionary implementation, and verify the chosen approach in the Excel versions you support. Excel for the web can open macro-enabled workbooks but cannot run VBA macros; use desktop Excel to execute this code.

Common errors and safe loop habits

Several problems recur when people first loop through dictionaries. These checks can make a macro easier to diagnose:

  • Object variable not set: create the dictionary with Set before using it. Declaring an object variable alone does not create the object.
  • Key already exists: do not call Add with a duplicate key. Use Exists to decide whether to add or update.
  • Wrong loop-variable type: declare the key or item variable as Variant unless you have a specific, controlled reason to use another type.
  • Unexpected matching behavior: set CompareMode before adding entries if text keys should be compared without regard to letter case.
  • Missing key error: check Exists before reading a key that may not have been added.

For text keys, CompareMode can be set to vbTextCompare for case-insensitive matching or vbBinaryCompare for binary comparison. Set it immediately after creating the dictionary and before adding keys. If case-insensitive comparison is used, keys such as north and NORTH are treated as the same key, so adding both can cause a duplicate-key error.

Avoid adding or removing dictionary keys while you are enumerating its keys. Changing a collection while it is being traversed can make loop behavior unreliable or cause an error. If the loop needs to decide which entries to remove, first collect those keys in a separate array or collection, finish the enumeration, and then remove them in a second pass. Updating an existing item is different from changing the set of keys, but keeping loop logic simple is still a good practice.

Dictionary versus Collection and worksheet ranges

Use a dictionary when you need to find an item by a unique key, test whether a key exists, or group repeated records efficiently in memory. A VBA Collection also stores items and can associate items with keys, but it does not offer the same built-in Exists method or the same dictionary-specific features. A range is preferable when the data needs to remain visible and editable on a worksheet, or when worksheet formulas and filters are the more natural tools.

These options are not mutually exclusive. A macro can read a range into a dictionary for fast key-based processing and then write a report to a new range. The dictionary is temporary unless your code writes its contents somewhere persistent; closing Excel or ending the procedure does not turn its in-memory entries into worksheet data.

Frequently Asked Questions

Can I use For Each key In dict?
For a Scripting.Dictionary, enumeration is commonly used to visit its keys, but For Each key In dict.Keys makes the intent explicit and is easier to understand. Use a Variant for the loop variable.

How do I loop through a dictionary in reverse?
A dictionary is not an indexed list with a documented reverse-order traversal pattern. If you need to process keys in a particular order, copy the keys to an array and sort or otherwise arrange that array, then loop through the arranged keys.

Does a dictionary keep entries in the order they were added?
Do not rely on insertion order for a report or calculation that requires a defined order. Arrange the keys explicitly when order matters rather than treating enumeration order as a sorting guarantee.

How can I check whether a key exists?
Call dict.Exists(key). It returns True if the key is present. Use that result before reading a key that may not exist, or before adding a key that might be a duplicate.

Can VBA dictionaries have duplicate keys?
No. A key identifies one entry and must be unique within the dictionary under its comparison rules. To represent repeated records, combine their values under one key, or use a different structure such as a collection of records.

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.