VBA Loop Through a Dictionary: Keys, Items, and Examples

To loop through a VBA Dictionary, use For Each key In dict.Keys, then read each value with dict(key). Declare the loop variable as a Variant; the example below uses late binding so it does not require setting a reference in the VBA editor.

What a VBA Dictionary Contains

A Dictionary stores pairs: a key identifies an entry, and an item is the value associated with that key. For example, a dictionary might pair a department name with a budget, a product code with a description, or a person’s name with a phone number. Unlike a collection indexed by position, a dictionary is designed for looking up an item by its key.

Dictionary keys must be unique under the dictionary’s comparison rules. Items do not have to be unique: two different keys can both refer to the same value. The Count property reports the number of key-item pairs. The common methods and properties used while looping are:

Keys returns an array containing the dictionary’s keys.
Items returns an array containing its values.
Exists(key) checks whether a key is present.
Item(key), commonly written as dict(key), gets or sets the value for a key.

These examples use the Scripting.Dictionary object in Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros. On Mac, availability of the Windows scripting runtime and COM-based object creation differs; do not assume that CreateObject("Scripting.Dictionary") will work in every Mac installation. If a project must run on both platforms, consider a Collection or a cross-platform approach and verify it in the target Excel versions.

A Basic Loop Through Dictionary Keys

The most useful pattern is to iterate over the keys and use each key to retrieve its item. This small, complete macro creates a dictionary and prints each pair in the Visual Basic Editor’s Immediate window. In the editor, open View > Immediate Window to see the output.

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

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

    ' Add sample key-item pairs.
    dict.Add "North", 125
    dict.Add "South", 98
    dict.Add "West", 143

    ' Visit each key and print its associated item.
    For Each key In dict.Keys
        Debug.Print CStr(key) & ": " & CStr(dict(key))
    Next key
End Sub

The output contains one line per entry, such as North: 125. For Each takes each key in turn; it does not supply the key and item as two separate loop variables. The expression dict(key) retrieves the value for the current key. A Variant is a good loop-variable type because dictionary keys may be strings, numbers, or other supported values.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so keeping a backup is especially important if a loop changes worksheet cells or dictionary contents.

Loop Through Values Instead

If the keys do not matter and the task concerns values alone, loop through dict.Items. This pattern is useful for totals, checks, or processing every stored value. The variable should still be a Variant, since the values might not all be the same data type.

Sub PrintDictionaryItems()
    Dim dict As Object
    Dim item As Variant

    ' Create a dictionary and add sample values.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 125
    dict.Add "South", 98
    dict.Add "West", 143

    ' Print each value without its key.
    For Each item In dict.Items
        Debug.Print item
    Next item
End Sub

Use a key loop when the relationship between a key and its value matters. A values-only loop loses that relationship: if you need to know which department has a particular budget, iterate over keys and retrieve each corresponding item. Also remember that repeated values will appear repeatedly in an items loop; the Dictionary does not automatically make values unique.

Loop Through Keys and Values by Index

For a loop that needs an index, save the arrays returned by Keys and Items, then use the same array index for each. This can be convenient when passing data to another procedure or preparing paired output. Check Count first: attempting to get the bounds of an empty array can cause an error.

Sub PrintPairsByIndex()
    Dim dict As Object
    Dim keys As Variant
    Dim items As Variant
    Dim i As Long

    ' Create a dictionary with sample entries.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "North", 125
    dict.Add "South", 98

    ' Only read array bounds when entries exist.
    If dict.Count > 0 Then
        keys = dict.Keys
        items = dict.Items

        For i = LBound(keys) To UBound(keys)
            Debug.Print CStr(keys(i)) & ": " & CStr(items(i))
        Next i
    End If
End Sub

The arrays from one call to Keys and one call to Items correspond by position when the dictionary has not changed between those calls. Avoid changing the dictionary between retrieving the arrays and using them; the arrays represent a captured set of entries, not live views that update as the dictionary changes.

Using a Dictionary Loop With Worksheet Data

A common Excel use is to build a dictionary from rows and then process each unique key once. For example, suppose column A contains a region and column B contains a number. A dictionary can store the total for each region. The following macro assumes data begins in row 2, uses row 1 for headers, and reads the active sheet. It prints the resulting region totals in the Immediate window rather than writing over worksheet cells.

Sub TotalByRegion()
    Dim dict As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim region As String
    Dim key As Variant

    ' Read the active sheet and create a dictionary.
    Set ws = ActiveSheet
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    ' Find the last used row in column A.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Add each row's amount to its region total.
    For r = 2 To lastRow
        region = Trim$(CStr(ws.Cells(r, "A").Value))
        If Len(region) > 0 And IsNumeric(ws.Cells(r, "B").Value) Then
            If dict.Exists(region) Then
                dict(region) = CDbl(dict(region)) + CDbl(ws.Cells(r, "B").Value)
            Else
                dict.Add region, CDbl(ws.Cells(r, "B").Value)
            End If
        End If
    Next r

    ' Print one total for each region.
    For Each key In dict.Keys
        Debug.Print CStr(key) & ": " & CStr(dict(key))
    Next key
End Sub

For example, if rows contain North with 10, South with 7, and North with 4, the loop builds totals of North = 14 and South = 7. The Exists check is important: the macro adds a new key on its first occurrence and updates the existing item on later occurrences. This example ignores blank region names and nonnumeric amounts; change those rules if such rows should be reported or handled differently.

Key Comparison and Duplicate Keys

By default, string keys are compared using binary comparison, so capitalization can distinguish keys: North and north may be separate entries. To treat text keys as case-insensitive, set CompareMode to vbTextCompare immediately after creating the dictionary and before adding the first key. The worksheet example does this. Setting the comparison mode after entries have been added can raise an error.

Adding a key that already exists with Add raises an error. When the key might already be present, use Exists to decide whether to update or add it. Another option is assigning through the item property, as in dict(key) = value: this updates an existing entry and can add an entry if the key is new. That behavior is useful, but it can also hide a spelling mistake by silently creating an unintended key. Use Exists when you need explicit control.

Order, Sorting, and Changing Entries During a Loop

A Dictionary is for key-based lookup, not for guaranteeing a particular display order. Do not rely on a loop returning keys alphabetically or in the order your worksheet rows were read. If output must be sorted, copy the keys to an array and sort that array separately, then retrieve values using each sorted key. Sorting mixed data types needs extra care; a simple text sort may not produce the intended numeric order.

Avoid adding or removing entries while directly enumerating the dictionary. Modifying a collection during enumeration can disrupt the loop or produce unreliable behavior. If you need to delete entries, first capture the keys, then iterate over that captured array and check that each key still exists before deleting it. This also makes the intended behavior clearer: the captured list represents the entries considered at the start of the operation. If you add entries after capturing the keys, those new entries will not be included in that pass.

Early Binding, Late Binding, and Platform Considerations

The examples use late binding: Dim dict As Object and CreateObject("Scripting.Dictionary"). This avoids choosing a Dictionary library reference in the VBA editor, which can make a workbook easier to move between Windows computers with different reference settings. The trade-off is that the editor provides less specific compile-time information about the object.

On Windows, early binding is another option. In the VBA editor, choose Tools > References and select Microsoft Scripting Runtime. Then declare the variable as Dim dict As Scripting.Dictionary and create it with Set dict = New Scripting.Dictionary. Early binding can provide clearer type information and editor assistance, but a missing reference on another computer can produce a “User-defined type not defined” error. The Windows scripting runtime is not a universally portable choice for Mac; test on the target Mac version or choose a different data structure if cross-platform execution is required.

Common Errors and How to Avoid Them

Object variable not set: Make sure the dictionary has been assigned with Set dict = CreateObject(...) or Set dict = New Scripting.Dictionary before calling its methods.
Duplicate-key error: Check dict.Exists(key) before using Add, or deliberately assign an item when updating is intended.
Unexpected new entry: Reading dict(key) for a missing key can create that key with an empty item. Use Exists before reading when the key may be absent.
Type mismatch: Use a Variant for keys or values that may vary in type, and convert explicitly only when the data supports that conversion.
Empty-array bounds error: Test dict.Count > 0 before calling LBound or UBound on arrays returned by Keys or Items.
Macro does not run: Excel for the web cannot execute VBA, and security settings may block macros in desktop Excel. Use a trusted workbook and follow the organization’s macro policy.

When a Collection or Array Is a Better Fit

Choose a Dictionary when you need fast, repeated lookup by a unique key or need to avoid processing the same key multiple times. A Collection can suit a simple ordered list of objects, though it does not provide the same Exists method. An array may be simpler when the data is a fixed sequence and you mainly process rows by position. For worksheet-sized data, reading a range into a VBA array and processing it in memory can also reduce repeated worksheet access. The right structure depends on whether the task is about keyed lookup, ordered items, or bulk row processing.

Frequently Asked Questions

Can I use For Each key In dict?
Although Dictionary enumeration is commonly associated with its keys, For Each key In dict.Keys makes the intent explicit and is easier for readers to recognize. Use a Variant for the loop variable.

How do I get the value for the current key?
Inside a key loop, use dict(key). If a key might not exist, first check dict.Exists(key); reading a missing key through the item property may add it.

How do I loop through a Dictionary when it is empty?
A For Each key In dict.Keys loop simply has no entries to process. If you use indexed loops with LBound and UBound, check that dict.Count is greater than zero first.

Does a Dictionary keep keys in alphabetical order?
No. Do not assume alphabetical or insertion order for output. Copy the keys to an array and sort them separately when a specific order is required.

Can two Dictionary keys have the same value?
Yes. Keys must be unique according to the comparison mode, but values may repeat. A loop through Items will include each repeated value once for every entry that contains it.

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.