In VBA, use For Each key In dict.Keys to loop through a Scripting.Dictionary’s keys, then read each value with dict(key). You can also loop through dict.Items when you need values alone; use a Variant variable for the loop control variable.
What “For Each” means with a dictionary
A dictionary stores pairs: a unique key and an associated item (often called a value). For example, a customer ID can be the key and a customer name the item. The key lets your code find the associated item without scanning through a numbered list.
With the Windows Scripting.Dictionary object, enumerating the dictionary itself enumerates its keys. That means For Each key In dict works like For Each key In dict.Keys. Writing dict.Keys explicitly makes the intent clear, particularly when you are learning or reviewing someone else’s code. To get the value corresponding to the current key, use dict(key).
“Item” can be ambiguous in VBA discussions: it may mean a dictionary’s stored value, or an entry in the key-value collection. The distinction matters. A loop over keys gives you a way to retrieve both parts of each pair; a loop over dict.Items gives you only the values.
Creating a dictionary in Excel VBA
For Microsoft Excel for Microsoft 365 on Windows, a convenient way to create a dictionary without changing project references is late binding. Open the VBA editor, insert a standard module, and use CreateObject("Scripting.Dictionary"). The following example creates three entries and prints each key and its value in the Immediate window.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. To open the VBA editor on Windows, press Alt+F11; on Mac, the shortcut is usually Option+F11, sometimes with Fn as well, depending on the keyboard.
Option Explicit
Sub LoopThroughDictionary()
Dim dict As Object
Dim key As Variant
' Create a late-bound Scripting.Dictionary.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A-101", "Open"
dict.Add "A-102", "In progress"
dict.Add "A-103", "Closed"
' Each key identifies its associated value.
For Each key In dict.Keys
Debug.Print key, dict(key)
Next key
End Sub
Run the macro from the VBA editor, then press Ctrl+G on Windows to show the Immediate window and see the results. On Mac, the Immediate window is available in the VBA editor; its menu location and shortcut can vary by Excel build. The output consists of one key-value pair per line, such as A-101 and Open.
Looping through keys and values
Choose the loop that matches what the code needs. A key loop is the most flexible because it can retrieve the value, check the key, or use the key to identify a worksheet row. A values-only loop is shorter if the keys are irrelevant.
- Keys and associated values: Loop over
dict.Keys, then usedict(key). - Values only: Loop over
dict.Itemsand assign each value to aVariant. - Keys only: Loop over
dict.Keysand use the key without looking up the item.
This separate example prints just the values. It is a complete procedure; it can be placed in the same standard module as the first example, but give each procedure a distinct name.
Option Explicit
Sub LoopThroughValues()
Dim dict As Object
Dim value As Variant
' Create and fill the dictionary.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A-101", "Open"
dict.Add "A-102", "In progress"
dict.Add "A-103", "Closed"
' Items returns the stored values.
For Each value In dict.Items
Debug.Print value
Next value
End Sub
Do not use a String or Long as the loop variable just because you expect every key or value to have that type. The dictionary can contain different kinds of values, and VBA’s For Each control variable should be a Variant or object variable. A Variant is the usual choice for keys and items.
Using the key to look up or update an item
The main benefit of iterating keys is that each key is already a lookup handle. For example, suppose a dictionary maps product codes to stock counts. Inside the loop, dict(key) gives the count for the current product code. You can use that value in a condition, send it to a worksheet cell, or pass it to another procedure.
Before reading a key that may not exist, check it with Exists. This is useful when a key comes from a cell or another external source. A dictionary lookup for a missing key can create a new entry with an empty value, so checking first helps prevent an accidental change in the dictionary.
Option Explicit
Sub ReadKnownDictionaryKey()
Dim dict As Object
Dim key As Variant
' Create a small example dictionary.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "P-10", 24
dict.Add "P-11", 8
For Each key In dict.Keys
If dict.Exists(key) Then
Debug.Print "Code=" & key & "; stock=" & dict(key)
End If
Next key
End Sub
Here, the loop’s key is guaranteed to exist because it came from the dictionary. The check is included to show the method to use when validating an outside key; in this particular loop it is not essential. The ampersand in the printed text joins values into a readable string.
Changing a dictionary while looping
Changing an existing value is different from changing the dictionary’s set of keys. You can generally assign a new value to an existing key while iterating, but avoid adding or removing keys during a For Each enumeration. Modifying the collection being enumerated can cause an error or make the loop’s behavior unreliable.
If you need to remove entries that meet a condition, first collect the keys to remove, then delete them after the loop. This two-pass approach avoids changing the key collection while VBA is walking through it. The same general approach is useful when adding keys based on results from a scan: finish the scan first, then add the new entries.
For a small dictionary, dict.Keys can also provide a snapshot array of keys to iterate. That can be useful when the code must change the dictionary during processing, but it does not remove the need to think carefully about which keys should be processed. For straightforward code, a separate collection of keys to add or remove is easier to reason about.
Common errors and practical fixes
Several common problems are about setup or assumptions rather than the loop syntax itself. Check these points when the code does not run as expected:
- “ActiveX component can’t create object”:
CreateObject("Scripting.Dictionary")depends on the Windows Scripting Runtime. The object may be unavailable or blocked in a particular environment. Check that Excel is running on Windows and that system or organizational restrictions are not preventing its use. - Duplicate key error:
Addfails if the key already exists. UseExiststo decide whether to add a new entry or update an existing one. - Wrong loop variable type: Declare the
For Eachvariable asVariant(or as an object for an object collection), not as a fixed numeric or string type. - Unexpected case handling: A dictionary’s
CompareModecontrols text comparisons. Set it before adding entries if you want case-insensitive text keys; changing it after entries have been added can raise an error. - Missing-value surprises: Use
Existsbefore looking up a key that may be absent, rather than assuming every requested key is present.
To avoid duplicate additions, the basic pattern is to test dict.Exists(key) first. If it returns True, update the existing item; otherwise, use Add. This makes the intended behavior explicit and avoids relying on an error handler to detect a duplicate.
Comparison rules and iteration order
Text keys may be compared using binary or text comparison rules. With binary comparison, differently cased strings such as North and north can be distinct keys. With text comparison, they are treated as equivalent. If you need case-insensitive keys, set dict.CompareMode = vbTextCompare immediately after creating the dictionary and before adding any entries. Choose the mode deliberately: case-insensitivity can also mean that two input codes differing only by capitalization collide.
Do not use the loop order as a substitute for sorting. A dictionary is designed for key-based lookup, and code should not depend on an assumed ordering of its entries. If output must be alphabetical or follow a business-defined sequence, collect the keys and sort them separately before displaying or processing them. That requirement is separate from the key-value lookup itself.
Windows, Mac, and alternatives
The examples use the Windows Scripting.Dictionary object. Excel for Mac’s VBA environment does not provide the same Windows COM scripting runtime in the same way, so late binding with CreateObject("Scripting.Dictionary") is not a portable Mac solution. A workbook that must run on both platforms needs a Mac-compatible data structure or a carefully selected cross-platform library; verify the exact method in the target Excel installations before relying on it.
For simpler collections, VBA’s built-in Collection can store items and retrieve them by key, but it does not expose the same dictionary interface or convenient Exists method. For a small fixed list, an array may be sufficient. If code depends on dictionary behavior such as fast key lookups and explicit key checks, document the platform requirement and test on every platform the workbook supports.
Frequently Asked Questions
Can I write For Each item In dict?
Yes, but for a Scripting.Dictionary that loop enumerates keys, not the stored values. Use a variable such as key to make that clear, then read the associated value with dict(key).
How do I loop through only the dictionary values?
Use For Each value In dict.Items and declare value as a Variant. This is suitable when you do not need to know which key each value belongs to.
Does For Each return a key and value together?
No. Each iteration supplies one key when enumerating the dictionary or its Keys property. Retrieve that entry’s value separately with dict(key).
Why does my dictionary loop fail on a Mac?
The Windows Scripting.Dictionary is based on a Windows scripting component that is not a reliably portable Mac dependency. Use a platform-compatible alternative or confirm a supported solution on the specific Mac Excel version and environment.
Can I delete a dictionary key inside For Each?
Avoid adding or deleting keys during enumeration. Collect the keys that need removal, finish the loop, and then delete them in a second pass to avoid modifying the collection being traversed.
Related
- Power Query Reference: What It Does and When to Use It
- Excel Data Validation: Can You Select Multiple Items?
- Protect Sheet in Excel: Allow Editing Specific Cells
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Sleep: Pause a Macro SafelyTo pause an Excel VBA macro on Windows, declare the Windows API function Sleep in a standard VBA…
- Excel VBA Variant: What It Is and When to Use ItIn Excel VBA, a Variant is a flexible data type that can hold different kinds of values, including…
- Excel Sheet Open VBA: Open and Activate a WorksheetTo open a worksheet that is already in an Excel workbook, use VBA to activate it: ThisWorkbook.Worksheets("Summary").Activate. If…
- Chrome Macro Recorder: Built-In Tools and ExtensionsChrome does not include a general-purpose macro recorder for automating any website, but it does have a built-in…
- Excel VBA InputBox Dropdown List: What Works InsteadNo: Excel VBA’s standard InputBox cannot display a dropdown list of choices. To offer a real dropdown, use…
- VBA Array Contains: How to Check for a Value in ExcelA VBA array contains multiple values stored under one variable name, with each value reached by an index…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.