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
Setbefore using it. An object variable declared asObjectis initiallyNothing. - Duplicate-key error: Use
dict.Exists(key)to check before callingAdd. If the key already exists, assign a replacement or updated value withdict(key) = newValue. - Wrong loop-variable type: Use a
Variantfor a general key or item loop. A fixed type such asStringmay 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 = vbTextComparebefore 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. UseIf dict.Exists(key) Thenwhen 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
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range Variables: How to Declare and Use ThemA VBA Range variable is an object variable that refers to one or more cells in an Excel…
- VBA Close Workbook Without a Save PromptTo close an Excel workbook without a save prompt, specify what should happen to unsaved changes: use Workbook.Close…
- VBA Dictionary Count: What It Returns and How to Use ItIn VBA, read a dictionary’s item count with its Count property: dict.Count. It returns the number of key–item…
- Show Picture in a VBA UserFormTo show a picture in an Excel VBA UserForm, add an Image control and either set its Picture…
- Running Macros in Google Sheets: How to Run and Manage ThemTo run a macro in Google Sheets, open the spreadsheet in a web browser, choose Extensions > Macros,…
- Excel VBA Enum: How It Works and When to Use ItAn Excel VBA Enum is a named set of numeric constants: it lets code use readable labels such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.