VBA Loop Through Dictionary Items: Keys, Values, and Tips

VBA & macros · Updated November 18, 2026

To loop through a VBA dictionary’s entries, use For Each on its keys, then retrieve each associated value with dict(key). To loop through values only, use For Each on dict.Items; the key-based form is usually best when you need to keep each key paired with its value.

What a VBA dictionary contains

A dictionary stores pairs: a unique key and the value associated with that key. For example, a key might be a product name and its value a quantity, or an employee ID and a name. Unlike a worksheet range, a dictionary is an in-memory object that you can use in a macro to organize and look up data.

The key is unique within the dictionary. Adding a key that already exists raises an error; to change an existing value, assign to that key instead. A dictionary can hold different kinds of values, including text, numbers, dates, and object references. Its Keys and Items properties return arrays containing the dictionary’s keys and values.

“Loop through dictionary items” can mean either loop through the values or process every key-value pair. Those are different tasks. If you need to print or total values alone, a values loop is concise. If you need to identify which value belongs to which key, loop through keys and look up each value.

Set up the Scripting.Dictionary object

In Excel for Microsoft 365 on Windows, a common way to create a dictionary is late binding. It does not require adding a reference in the Visual Basic Editor, and it is convenient for a macro that you may share with another Windows user:

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

    ' Create a dictionary without setting a project reference.
    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "Apples", 12
    dict.Add "Oranges", 8

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

To open the Visual Basic Editor on Windows, press Alt+F11; on a Mac, Option+F11 may open it, sometimes with the Fn key as well, depending on the keyboard. You can also use the Developer tab’s Visual Basic command. Before running a macro, save a copy of the workbook: VBA actions generally cannot be undone with Ctrl+Z.

Another Windows option is early binding: in the Visual Basic 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 code completion and compile-time type checking. The tradeoff is that the reference must be available to the workbook’s users.

Loop through keys and retrieve each value

For most tasks, this is the clearest pattern:

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

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Apples", 12
    dict.Add "Oranges", 8
    dict.Add "Pears", 5

    ' Visit each key and retrieve its matching value.
    For Each key In dict.Keys
        Debug.Print "Key: " & key & "; value: " & dict(key)
    Next key
End Sub

The loop variable is declared as Variant because dictionary keys can have different data types, and For Each expects a suitable variable for the elements it visits. In each pass, key contains one key; dict(key) returns that key’s current value. In the example, the Immediate window shows the three names and their quantities. Open it in the Visual Basic Editor with Ctrl+G on Windows.

Do not assume that a dictionary is sorted. The loop follows the order supplied by the dictionary, but the Scripting.Dictionary interface does not provide a sorting operation or promise a meaningful alphabetical or numerical order. If output order matters, copy the keys into an array and sort that array, or sort the output after writing it to a worksheet.

Loop through values only

If the keys are irrelevant and you need only the values, iterate over Items:

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

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Apples", 12
    dict.Add "Oranges", 8

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

This prints 12 and 8, but it does not tell you which number belongs to which fruit. A frequent source of confusion is treating a dictionary’s “items” as complete key-value records. In this object model, Items returns the values, while Keys returns the keys. Use the key loop when you need both sides of a pair.

For a calculation, you can perform the work inside the same loop. For example, to total numeric values, declare a variable such as total As Double, add each item to it, and print or use the final total after the loop. This assumes every stored value is numeric; mixed or text values need validation before addition.

Use a dictionary loop for a practical task

Suppose a macro builds a dictionary of item names and quantities, then needs to report entries below a reorder threshold. The key-based loop gives the macro both the name and the quantity:

Sub ListItemsToReorder()
    Dim dict As Object
    Dim key As Variant
    Dim quantity As Long
    Dim threshold As Long

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Apples", 12
    dict.Add "Oranges", 3
    dict.Add "Pears", 5
    threshold = 5

    ' Report entries whose quantity is below the threshold.
    For Each key In dict.Keys
        quantity = CLng(dict(key))
        If quantity < threshold Then
            Debug.Print key & " needs reordering: " & quantity
        End If
    Next key
End Sub

With these sample values, the Immediate window reports Oranges because 3 is below 5. Pears is not reported: the condition is strictly “less than,” so a quantity equal to the threshold does not qualify. Change < to <= if the intended rule is “at or below the threshold.” The conversion with CLng is appropriate here because the example stores whole-number quantities. Choose a conversion that matches the data, or validate it before converting if values come from user input or worksheet cells.

Change values or remove entries safely

Updating a value while traversing keys is often useful. For example, if each value is numeric, you can assign dict(key) = dict(key) + 1 inside the key loop. The keys remain the same, and the value associated with the current key changes. If the dictionary has been populated with values of mixed types, check their types before doing arithmetic.

Removing keys is more delicate. Do not rely on changing the collection of keys while a loop is actively traversing that same collection. A safer approach is to capture the keys first, then use that array to decide which dictionary entries to remove:

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

    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Apples", 12
    dict.Add "Oranges", 3
    dict.Add "Pears", 5

    ' Capture the keys before changing the dictionary.
    keys = dict.Keys
    For Each key In keys
        If CLng(dict(key)) < 5 Then
            dict.Remove key
        End If
    Next key
End Sub

Here the saved array supplies the keys to examine while the dictionary itself loses matching entries. This example removes Oranges. If the code later tries to look up a key that has already been removed, it can raise an error; check dict.Exists(key) before accessing a key when its presence is uncertain.

Common errors and useful safeguards

  • “Object required” or an object-variable error: Make sure the dictionary was successfully created and assigned with Set before using it. An object variable declared as Object is initially Nothing.
  • Duplicate-key error: Use dict.Exists(key) to check before calling Add. If the key already exists, assign a replacement or updated value with dict(key) = newValue.
  • Wrong loop-variable type: Use a Variant for a general key or item loop. A fixed type such as String may fail or require conversion when keys are not strings.
  • Unexpected key matching: The default comparison is generally binary, so text keys with different capitalization can be distinct. Set dict.CompareMode = vbTextCompare before adding keys if case-insensitive text matching is required. Changing CompareMode after entries exist can cause an error.
  • Missing key: A lookup such as dict(key) assumes that the key exists. Use If dict.Exists(key) Then when the key may be absent.

An empty dictionary has no entries to process, so a normal key loop performs no useful iterations. You can check dict.Count if the next operation requires at least one entry. Keep in mind that an empty dictionary and a dictionary containing a key whose value is empty are not the same situation: Count measures keys, not whether their values contain data.

Dictionary loops compared with other approaches

A dictionary is useful when you need fast lookups by a unique key, such as finding a quantity from a product name. A VBA Collection can also hold values and, in some uses, keyed entries, but it does not offer the same Keys, Items, and Exists interface. Arrays are suitable for ordered data and can be more straightforward when you need position-based processing, but they do not provide the same direct key lookup.

If your data is already in a worksheet and the task is simply to display or calculate values, a worksheet range may be easier to inspect and maintain than moving everything into a dictionary. A dictionary is especially helpful when a macro must aggregate repeated records, test whether a key has appeared, or retrieve a value by key many times. It is not automatically the best choice for every loop; choose it for the lookup behavior, not just because it is available.

Windows and Mac considerations

The examples use CreateObject("Scripting.Dictionary"), which is commonly used in Excel VBA on Windows. The Scripting Runtime is a Windows component, and this COM-based approach should not be assumed to work consistently in Excel for Mac. A workbook intended for both platforms should be checked on each target version rather than relying on a Windows-only reference or object being present.

For cross-platform code, consider whether a VBA Collection, arrays, or a custom class better fits the task. Each alternative has different lookup and key-handling behavior, so changing the object type may require changing the loop and error checks too. Excel for the web does not run VBA macros; use desktop Excel to run or edit this VBA code.

Frequently Asked Questions

How do I loop through all keys in a VBA dictionary?
Declare a key variable as Variant, then use For Each key In dict.Keys. Inside the loop, retrieve the paired value with dict(key).

How do I loop through dictionary values only?
Use a Variant item variable and write For Each item In dict.Items. This visits the values without providing their corresponding keys.

Can I use For Each item In dict?
For clear, portable code, loop over the explicit dict.Keys or dict.Items property. Use dict.Keys when you need to retrieve values by key; use dict.Items for values alone.

How can I check whether a dictionary key exists?
Call dict.Exists(key). It returns True when the key is present, allowing you to avoid an invalid lookup or a duplicate-key attempt.

Does a VBA dictionary loop sort entries?
No. Do not depend on the loop to produce alphabetical or numerical order. Sort the keys separately if the order of your output matters.

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.