To loop through every key in a VBA dictionary, use For Each key In dict.Keys, declaring key as a Variant. Inside the loop, use dict(key) to retrieve the value associated with that key.
What a VBA dictionary contains
A dictionary stores pairs: a key and its associated item. For example, a key such as a product code can point to a product name, a number, or another value. Unlike a worksheet range, a dictionary is held in memory while the macro runs, and it offers a direct way to look up an item by key.
In Windows Excel for Microsoft 365, VBA commonly uses the Scripting.Dictionary object. It is not a VBA language keyword; it is an object provided by the Microsoft Scripting Runtime. A dictionary is useful when code needs to check whether a value has appeared before, count occurrences, or associate one piece of information with another.
The word “key” can be confusing because it is also used in other programming contexts. Here, it means the identifier on the left side of a key-item pair. If a dictionary contains the pair "North", 125, then "North" is the key and 125 is its item or value.
The basic For Each pattern
The Keys property supplies the dictionary’s keys as an array. VBA’s For Each loop can visit each element of that array in turn. This minimal macro creates a dictionary, adds two pairs, and prints each key and its corresponding item in the Immediate window.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z.
Option Explicit
Sub ShowDictionaryKeys()
Dim dict As Object
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 98
For Each key In dict.Keys
Debug.Print key, dict(key)
Next key
End Sub
To see the Immediate window in the Visual Basic Editor, press Ctrl+G on Windows. On Mac, use the Visual Basic Editor’s View menu and choose Immediate Window; keyboard shortcuts can vary by Excel version and keyboard settings. The output for this example contains the two keys and their values. The order reflects the dictionary’s enumeration order; do not rely on it as an alphabetical or worksheet sort.
Loop through keys, values, or both
Use dict.Keys when the key itself matters, for example when you need to write identifiers to a report or perform a lookup. In each iteration, dict(key) retrieves the item for that key. This is often the most useful pattern because it keeps the relationship between each key and value explicit.
If you only need the values, the dictionary also has an Items property. You can loop through that array, but the loop variable represents an item rather than a key. If you need both parts of each pair, loop over the keys and look up the item, as in the first example. That avoids accidentally treating an item as an identifier.
For example, if the dictionary maps department names to totals, looping over keys lets you print a department and its total together. Looping only over Items would give you the totals but not tell you which department each total belongs to. The choice depends on what the next step in the macro needs.
Count repeated worksheet values
A common practical use is counting how often each value appears in a column. The following macro assumes the active worksheet has text entries in A2 downward, with a header in A1. It writes each distinct entry and its count to columns D and E, beginning at row 1. Blank cells are skipped; the example assumes there are no Excel error values in the source column.
Save a copy before running it: this macro writes to the active sheet and replaces any existing content in columns D and E in the output area.
Option Explicit
Sub CountValuesWithDictionary()
Dim ws As Worksheet
Dim dict As Object
Dim lastRow As Long
Dim r As Long
Dim item As String
Dim key As Variant
Dim outputRow As Long
Set ws = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
item = CStr(ws.Cells(r, "A").Value2)
If Len(item) > 0 Then
If dict.Exists(item) Then
dict(item) = dict(item) + 1
Else
dict.Add item, 1
End If
End If
Next r
ws.Range("D:E").ClearContents
ws.Cells(1, "D").Value = "Value"
ws.Cells(1, "E").Value = "Count"
outputRow = 2
For Each key In dict.Keys
ws.Cells(outputRow, "D").Value = key
ws.Cells(outputRow, "E").Value = dict(key)
outputRow = outputRow + 1
Next key
End Sub
For instance, if A2:A5 contains “Pear,” “Apple,” “Pear,” and “Pear,” the dictionary holds two keys: Pear with a count of 3, and Apple with a count of 1. The output order is not guaranteed to be alphabetical. Sort the resulting range separately if a particular display order is required.
Adding keys safely and choosing case rules
Calling Add with a key that already exists raises an error. When duplicates are possible, check first with Exists, as the counting example does, and then either update the existing value or add a new pair. This is also a good pattern for importing data where repeated identifiers may occur.
By default, dictionary string keys are compared without treating uppercase and lowercase letters as equivalent. If a key such as “Pear” should match “PEAR,” set CompareMode to vbTextCompare immediately after creating the dictionary and before adding any keys. Setting it after keys have been added can cause an error. Use vbBinaryCompare when letter case should distinguish keys. Choose deliberately: case-insensitive matching can combine values that your data considers different.
Keys must be unique under the dictionary’s comparison rules. If an application needs multiple records for the same identifier, store a collection or another suitable structure as the item, or use a different data model. A dictionary is not a worksheet table that automatically keeps duplicate rows.
Early binding and late binding
The examples use late binding: Dim dict As Object followed by CreateObject("Scripting.Dictionary"). This approach avoids setting a compile-time reference to the library, which can make a workbook easier to move between compatible Windows computers. The trade-off is that the VBA editor offers less specific type checking and IntelliSense for the dictionary.
With early binding, the variable is declared as Scripting.Dictionary and the project references Microsoft Scripting Runtime. In the Visual Basic Editor on Windows, choose Tools > References and select the Microsoft Scripting Runtime entry if it is available. Early binding can make code easier to explore and catch some mistakes earlier, but a missing reference on another computer can produce a “MISSING” reference or prevent the project from compiling. For code shared across Windows installations, late binding is often the simpler option.
Common errors and practical limits
- “Object required” or an uninitialized object: Set the dictionary with
Set dict = CreateObject("Scripting.Dictionary")before using its properties. - Duplicate-key error: Test with
dict.Exists(key)before callingAdd, or decide how existing entries should be updated. - Unexpected case matching: Set
CompareModebefore adding any keys and verify that the chosen comparison rule matches the data. - Wrong loop variable type: Declare the key variable as
Variant. The Keys property returns an array, and a Variant is the flexible choice for iterating its contents. - Unexpected output order: Treat dictionary enumeration as a way to visit entries, not as a sorting method. Sort a separate array or the output range if order matters.
Avoid adding or removing entries from a dictionary while iterating its keys. Even when code appears to work, changing the collection during enumeration can make the loop’s behavior difficult to reason about. A safer design is to collect the keys that need changing, finish the loop, and then make the changes in a separate step.
Windows and Mac compatibility
Scripting.Dictionary is a Windows COM object, so the CreateObject("Scripting.Dictionary") approach shown here is intended for Excel for Microsoft 365 on Windows. Excel for the web does not run VBA macros. Excel for Mac does not provide Windows COM automation in the same way, so this exact object-creation method should not be assumed to work there.
If a workbook must run on Mac or in Excel for the web, consider a VBA collection or a compatible VBA dictionary implementation, and check that option against the target versions before building the workbook around it. A Collection is built into VBA but does not provide the same convenient Exists method, so lookups and duplicate handling require different code. For browser-based automation, Office Scripts is a separate technology from VBA and uses a different programming model.
Frequently Asked Questions
How do I get a value from a key in a VBA dictionary?
Use dict(key) after confirming the key exists with dict.Exists(key) when it may be absent. For example, a key named “North” retrieves its associated item with dict("North").
Can I write For Each key In dict instead?
The dictionary’s enumerator generally visits keys, but For Each key In dict.Keys makes the intent clear and explicitly loops through the keys array. That form is easier for readers to recognize and maintain.
Why does my dictionary loop return items in a different order?
A dictionary is designed for keyed lookup, not sorting. Do not depend on its enumeration order for alphabetical, numerical, or insertion-order output. Sort the keys or sort the worksheet results when a defined order is important.
How can I loop through a dictionary’s values?
Use dict.Items to access an array of values. If you need to know which key belongs with each value, loop through dict.Keys and retrieve the matching value with dict(key).
Does this VBA dictionary code work in Excel for Mac?
The Windows Scripting.Dictionary COM object and its CreateObject setup should not be relied on in Mac Excel. Use a Mac-compatible alternative and verify it in the specific Excel version where the workbook will run.
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
- Copy and Paste Dynamically Using VBA in ExcelTo copy a range that changes size as your data grows, use VBA to find its last row…
- VBA Go to Next Item in a For LoopVBA does not have a Continue For statement. To skip the rest of the current pass and move…
- VBA Dictionary For Each: Loop Through Keys and ItemsIn VBA, use For Each key In dict.Keys to loop through a Scripting.Dictionary’s keys, then read each value…
- VBA Delete Current Row: Safe Methods and ExamplesTo delete the current row in Excel with VBA, delete the entire worksheet row that contains the active…
- Quick Sort in VBA: Built-In Function or Custom Code?No: VBA does not include a built-in procedure or keyword named QuickSort or quick sort. Call is a…
- VBA UserForms: What They Are and How to Use ThemA VBA UserForm is a custom dialog window you build in the Visual Basic for Applications editor and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.