VBA Dictionary For Each Key: How to Loop Through Items

VBA & macros · Updated November 18, 2026

To loop through every key in a VBA dictionary, use For Each key In dict.Keys, declaring key as a Variant. Inside the loop, use dict(key) to retrieve the value associated with that key.

What a VBA dictionary contains

A dictionary stores pairs: a key and its associated item. For example, a key such as a product code can point to a product name, a number, or another value. Unlike a worksheet range, a dictionary is held in memory while the macro runs, and it offers a direct way to look up an item by key.

In Windows Excel for Microsoft 365, VBA commonly uses the Scripting.Dictionary object. It is not a VBA language keyword; it is an object provided by the Microsoft Scripting Runtime. A dictionary is useful when code needs to check whether a value has appeared before, count occurrences, or associate one piece of information with another.

The word “key” can be confusing because it is also used in other programming contexts. Here, it means the identifier on the left side of a key-item pair. If a dictionary contains the pair "North", 125, then "North" is the key and 125 is its item or value.

The basic For Each pattern

The Keys property supplies the dictionary’s keys as an array. VBA’s For Each loop can visit each element of that array in turn. This minimal macro creates a dictionary, adds two pairs, and prints each key and its corresponding item in the Immediate window.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z.

Option Explicit

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

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

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

To see the Immediate window in the Visual Basic Editor, press Ctrl+G on Windows. On Mac, use the Visual Basic Editor’s View menu and choose Immediate Window; keyboard shortcuts can vary by Excel version and keyboard settings. The output for this example contains the two keys and their values. The order reflects the dictionary’s enumeration order; do not rely on it as an alphabetical or worksheet sort.

Loop through keys, values, or both

Use dict.Keys when the key itself matters, for example when you need to write identifiers to a report or perform a lookup. In each iteration, dict(key) retrieves the item for that key. This is often the most useful pattern because it keeps the relationship between each key and value explicit.

If you only need the values, the dictionary also has an Items property. You can loop through that array, but the loop variable represents an item rather than a key. If you need both parts of each pair, loop over the keys and look up the item, as in the first example. That avoids accidentally treating an item as an identifier.

For example, if the dictionary maps department names to totals, looping over keys lets you print a department and its total together. Looping only over Items would give you the totals but not tell you which department each total belongs to. The choice depends on what the next step in the macro needs.

Count repeated worksheet values

A common practical use is counting how often each value appears in a column. The following macro assumes the active worksheet has text entries in A2 downward, with a header in A1. It writes each distinct entry and its count to columns D and E, beginning at row 1. Blank cells are skipped; the example assumes there are no Excel error values in the source column.

Save a copy before running it: this macro writes to the active sheet and replaces any existing content in columns D and E in the output area.

Option Explicit

Sub CountValuesWithDictionary()
    Dim ws As Worksheet
    Dim dict As Object
    Dim lastRow As Long
    Dim r As Long
    Dim item As String
    Dim key As Variant
    Dim outputRow As Long

    Set ws = ActiveSheet
    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        item = CStr(ws.Cells(r, "A").Value2)
        If Len(item) > 0 Then
            If dict.Exists(item) Then
                dict(item) = dict(item) + 1
            Else
                dict.Add item, 1
            End If
        End If
    Next r

    ws.Range("D:E").ClearContents
    ws.Cells(1, "D").Value = "Value"
    ws.Cells(1, "E").Value = "Count"
    outputRow = 2

    For Each key In dict.Keys
        ws.Cells(outputRow, "D").Value = key
        ws.Cells(outputRow, "E").Value = dict(key)
        outputRow = outputRow + 1
    Next key
End Sub

For instance, if A2:A5 contains “Pear,” “Apple,” “Pear,” and “Pear,” the dictionary holds two keys: Pear with a count of 3, and Apple with a count of 1. The output order is not guaranteed to be alphabetical. Sort the resulting range separately if a particular display order is required.

Adding keys safely and choosing case rules

Calling Add with a key that already exists raises an error. When duplicates are possible, check first with Exists, as the counting example does, and then either update the existing value or add a new pair. This is also a good pattern for importing data where repeated identifiers may occur.

By default, dictionary string keys are compared without treating uppercase and lowercase letters as equivalent. If a key such as “Pear” should match “PEAR,” set CompareMode to vbTextCompare immediately after creating the dictionary and before adding any keys. Setting it after keys have been added can cause an error. Use vbBinaryCompare when letter case should distinguish keys. Choose deliberately: case-insensitive matching can combine values that your data considers different.

Keys must be unique under the dictionary’s comparison rules. If an application needs multiple records for the same identifier, store a collection or another suitable structure as the item, or use a different data model. A dictionary is not a worksheet table that automatically keeps duplicate rows.

Early binding and late binding

The examples use late binding: Dim dict As Object followed by CreateObject("Scripting.Dictionary"). This approach avoids setting a compile-time reference to the library, which can make a workbook easier to move between compatible Windows computers. The trade-off is that the VBA editor offers less specific type checking and IntelliSense for the dictionary.

With early binding, the variable is declared as Scripting.Dictionary and the project references Microsoft Scripting Runtime. In the Visual Basic Editor on Windows, choose Tools > References and select the Microsoft Scripting Runtime entry if it is available. Early binding can make code easier to explore and catch some mistakes earlier, but a missing reference on another computer can produce a “MISSING” reference or prevent the project from compiling. For code shared across Windows installations, late binding is often the simpler option.

Common errors and practical limits

  • “Object required” or an uninitialized object: Set the dictionary with Set dict = CreateObject("Scripting.Dictionary") before using its properties.
  • Duplicate-key error: Test with dict.Exists(key) before calling Add, or decide how existing entries should be updated.
  • Unexpected case matching: Set CompareMode before adding any keys and verify that the chosen comparison rule matches the data.
  • Wrong loop variable type: Declare the key variable as Variant. The Keys property returns an array, and a Variant is the flexible choice for iterating its contents.
  • Unexpected output order: Treat dictionary enumeration as a way to visit entries, not as a sorting method. Sort a separate array or the output range if order matters.

Avoid adding or removing entries from a dictionary while iterating its keys. Even when code appears to work, changing the collection during enumeration can make the loop’s behavior difficult to reason about. A safer design is to collect the keys that need changing, finish the loop, and then make the changes in a separate step.

Windows and Mac compatibility

Scripting.Dictionary is a Windows COM object, so the CreateObject("Scripting.Dictionary") approach shown here is intended for Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros. Excel for Mac does not provide Windows COM automation in the same way, so this exact object-creation method should not be assumed to work there.

If a workbook must run on Mac or in Excel for the web, consider a VBA collection or a compatible VBA dictionary implementation, and check that option against the target versions before building the workbook around it. A Collection is built into VBA but does not provide the same convenient Exists method, so lookups and duplicate handling require different code. For browser-based automation, Office Scripts is a separate technology from VBA and uses a different programming model.

Frequently Asked Questions

How do I get a value from a key in a VBA dictionary?
Use dict(key) after confirming the key exists with dict.Exists(key) when it may be absent. For example, a key named “North” retrieves its associated item with dict("North").

Can I write For Each key In dict instead?
The dictionary’s enumerator generally visits keys, but For Each key In dict.Keys makes the intent clear and explicitly loops through the keys array. That form is easier for readers to recognize and maintain.

Why does my dictionary loop return items in a different order?
A dictionary is designed for keyed lookup, not sorting. Do not depend on its enumeration order for alphabetical, numerical, or insertion-order output. Sort the keys or sort the worksheet results when a defined order is important.

How can I loop through a dictionary’s values?
Use dict.Items to access an array of values. If you need to know which key belongs with each value, loop through dict.Keys and retrieve the matching value with dict(key).

Does this VBA dictionary code work in Excel for Mac?
The Windows Scripting.Dictionary COM object and its CreateObject setup should not be relied on in Mac Excel. Use a Mac-compatible alternative and verify it in the specific Excel version where the workbook will run.

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.