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
Setbefore using it. Declaring an object variable alone does not create the object. - Key already exists: do not call
Addwith a duplicate key. UseExiststo decide whether to add or update. - Wrong loop-variable type: declare the key or item variable as
Variantunless you have a specific, controlled reason to use another type. - Unexpected matching behavior: set
CompareModebefore adding entries if text keys should be compared without regard to letter case. - Missing key error: check
Existsbefore 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
- Excel Split Contents of a Cell Into Two Cells
- Excel Split Multiline Cell Into Rows
- Excel Split Empty Cells Into Multiple Rows?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find Position of a Substring in a StringIn VBA, use InStr to find a substring’s position within a string: it returns the position of the…
- Excel UserForm Keeps Changing Size? Causes and FixesAn Excel UserForm usually changes size because VBA or its AutoSize setting changes its dimensions, or because Windows…
- Excel VBA: Delete Rows Based on CriteriaTo delete Excel rows that meet a condition, loop through the relevant rows from bottom to top and…
- Excel VBA: Disable the X Close Button?Yes—Excel VBA can stop a workbook from closing when someone clicks its X by handling the Workbook_BeforeClose event…
- Excel VBA Resize: Change a Range’s Size with VBA CodeIn Excel VBA, use the Resize property to refer to a range with a different number of rows…
- VBA Find the Last Used Cell When Filters Are OnTo find the last used cell in Excel VBA while a filter is active, search the worksheet with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.