In Excel VBA, a dictionary key is a unique identifier used to store and retrieve a corresponding value. Add a key-value pair with the dictionary’s Add method, check whether a key exists with Exists, and retrieve its value by using the key in brackets, such as dict("INV-104").
What a dictionary key represents
A VBA dictionary is a key-value collection: each entry has a key and an item (the value associated with that key). Think of the key as a label or lookup value. If a dictionary stores product codes and stock counts, a code such as SKU-482 can be the key and the number of units can be its item. Unlike a worksheet row number, a key identifies an entry by its contents.
Keys must be unique within a dictionary. If you try to add a second entry with a key that already exists, Add raises an error rather than keeping two entries with the same key. The item does not have to be unique: several keys can have the same value. For example, two product codes can both have a stock count of zero.
Dictionary keys are useful when code needs fast, direct access to values or needs to recognize duplicates while processing data. They can represent customer IDs, invoice numbers, file names, category labels, or other identifiers. Choose a key that genuinely distinguishes one record from another; a person’s first name, for instance, is often not unique enough to identify a customer.
Using the Scripting.Dictionary object
In Excel for Microsoft 365 on Windows, the common VBA dictionary is the Scripting.Dictionary object. It is provided by the Microsoft Scripting Runtime. You can use early binding by adding a reference in the Visual Basic Editor through Tools > References and selecting Microsoft Scripting Runtime. Then declare a variable as Scripting.Dictionary. Early binding offers editor assistance and compile-time checking for the object’s members.
Alternatively, use late binding by declaring the variable as Object and creating it with CreateObject. This avoids setting a project reference, which can make a workbook less prone to missing-reference problems when shared among Windows computers. The following example uses late binding:
Sub CreateDictionaryExample()
Dim dict As Object
' Create a Scripting.Dictionary on Windows.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "INV-104", 275
dict.Add "INV-105", 0
Debug.Print dict("INV-104")
End Sub
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. To open the Visual Basic Editor in Windows, press Alt+F11; on Mac, the equivalent is usually Option+F11, sometimes with Fn depending on the keyboard. The Windows Scripting Runtime is not a dependable cross-platform choice: CreateObject("Scripting.Dictionary") may fail on Mac because the required Windows COM component is not available. If a workbook must run on Mac, use a suitable alternative or test the exact deployment environment rather than assuming the Windows object will work.
Adding, reading, and updating keys
Add makes a new key-value pair and reports an error if that key already exists. Bracket notation reads the item associated with a key. You can also assign a new item to an existing key using the same notation. A useful pattern is to test with Exists before choosing whether to add a new entry or update an existing one:
Sub AddOrUpdateDictionaryEntry()
Dim dict As Object
Dim lookupKey As String
' Save a workbook copy before running VBA macros.
Set dict = CreateObject("Scripting.Dictionary")
lookupKey = "SKU-482"
If dict.Exists(lookupKey) Then
dict(lookupKey) = dict(lookupKey) + 1
Else
dict.Add lookupKey, 1
End If
End Sub
Here the item is a count. The first occurrence of a product code creates a key with value 1; later occurrences increase that value. This is a common way to count repeated values while reading rows from a worksheet. Notice that the example uses Exists before reading the item, so it does not depend on what a missing key might return.
In the Scripting.Dictionary object, assigning an item through dict(key) can also create an entry if the key is not already present. Even so, an explicit Exists check is clearer when the code must distinguish between adding and updating, or when a missing key should be treated as an error. Use Add when duplicate keys should be detected rather than silently overwritten.
Choosing key types and comparison rules
Dictionary keys can be values such as strings or numbers. In practice, text identifiers are often the clearest choice, especially when an identifier may contain leading zeros. A code such as 00127 should usually be stored as text if the zeros are meaningful; converting it to a number would turn it into 127. Keep key formatting consistent when values come from worksheet cells. For example, the text AB-12 and AB-12 with a trailing space are not necessarily the same key.
Text comparison behavior is controlled by the dictionary’s CompareMode property. The default is binary comparison, which treats text with different capitalization as different keys. Setting CompareMode to vbTextCompare makes text-key comparisons case-insensitive, so North and north are treated as the same key. Set this property before adding any data; changing it after the dictionary contains entries raises an error.
Sub CaseInsensitiveKeysExample()
Dim dict As Object
' Save a workbook copy before running VBA macros.
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict.Add "North", "Region A"
Debug.Print dict.Exists("north") ' Prints True.
End Sub
Consider the source data before choosing a comparison mode. Case-insensitive matching can prevent accidental duplicates when capitalization varies, but it can also merge identifiers that are intentionally distinct. If spaces, punctuation, or other formatting differences should be ignored, normalize the key deliberately—for example, by trimming extra spaces—rather than assuming CompareMode handles every kind of difference.
Checking whether a key exists
Use dict.Exists(key) to determine whether the dictionary contains a key. It returns True or False, making it suitable for conditional logic. This check is especially important when a missing key must be handled differently from a key whose item is blank, zero, or otherwise empty.
A common misconception is that checking whether the item is empty is equivalent to checking whether the key exists. It is not. A valid entry may have an empty or zero value, and a lookup for a missing key may not give the distinction your application needs. Test key membership with Exists; then read or update the item only when the logic allows it.
For example, when looking up a department budget, an existing department with a budget of zero is not the same as a department absent from the data. An Exists check lets the macro report “department not found” separately from “budget is zero.” That distinction avoids misleading results in summaries and validation routines.
Listing keys and looping through entries
The Keys property returns an array of the dictionary’s keys, and Items returns an array of its values. These are useful for inspecting entries or transferring the results elsewhere. If the dictionary is empty, account for that case before assuming there is an array element to loop over.
For a straightforward loop, use a Variant variable with For Each. The associated item can be retrieved by using the current key:
Sub ListDictionaryEntries()
Dim dict As Object
Dim key As Variant
' Save a workbook copy before running VBA macros.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "INV-104", 275
dict.Add "INV-105", 0
For Each key In dict.Keys
Debug.Print CStr(key), dict(key)
Next key
End Sub
Dictionary enumeration is not a substitute for sorting. If the output must appear in alphabetical or numeric order, sort the keys or place the results in a worksheet and sort them there. Also avoid changing the dictionary’s contents while looping through its keys unless the code is designed to handle that change; a safer pattern is to collect the keys first and then process the collected list.
Changing or removing a key
Use Remove to delete an entry by its current key and RemoveAll to clear every entry. Before calling Remove, check Exists if a missing key is a normal possibility; otherwise, removal of an absent key can raise an error. Clearing a dictionary does not remove worksheet data—it affects only the entries held by that VBA object.
The dictionary also exposes a Key property that can change the key associated with an existing entry. This is different from changing its item: changing dict(oldKey) changes the value, while changing the entry’s key changes how the value is identified. Check that the replacement key does not conflict with another key, and update any other code or worksheet references that still expect the old identifier.
For many tasks, removing an entry and adding it again under a new key is easier to understand. If you do that, preserve the old item before removal and verify that the new key is available. Whichever approach you use, treat a key change as a data-integrity operation: a typo or collision can make later lookups fail or associate a value with the wrong identifier.
Common errors and practical safeguards
- Duplicate key error:
Addfails if that key already exists under the dictionary’s comparison rules. CheckExistsfirst when duplicates are expected, or decide explicitly whether to skip, count, or replace them. - Missing object or automation error: On Windows, confirm that the Scripting Runtime is available if using early binding, or that late binding can create the object. A Windows COM dependency may prevent the same code from working on Mac.
- Unexpectedly different keys: Check leading or trailing spaces, capitalization, data types, and leading zeros. Standardize values when loading them, and do not convert meaningful text identifiers to numbers.
- CompareMode error: Set the comparison mode before the first entry is added. If the dictionary already contains data, create a fresh dictionary with the desired mode and repopulate it.
- Wrong result after a lookup: Use
Existsto distinguish a missing entry from an entry whose item is blank or zero. Confirm that the key used to look up an item has the same representation as the key used when adding it.
When dictionary keys come from worksheet cells, decide how to handle blank cells and errors before adding them. A blank identifier is rarely a reliable unique key, and an Excel error value may not behave like ordinary text or a number. Validate the source data and convert values intentionally rather than allowing inconsistent cell contents to become dictionary keys unnoticed.
When a dictionary is the right tool
A dictionary is a good fit when code needs to retrieve a value by a unique label, test whether a value has appeared before, or build a summary while scanning rows. For example, a macro can use customer IDs as keys and running totals as items. Each row either starts a new total or adds to the existing one. This avoids repeatedly searching all previously processed rows in VBA code.
Use a worksheet lookup formula instead when the goal is a visible, maintainable result that should recalculate as data changes. Excel’s XLOOKUP can search for a value in a worksheet table in Microsoft 365 and Excel 2021 or later, while a VBA dictionary is most useful inside a macro’s logic. A dictionary exists in memory while the code runs; it does not automatically update when a cell changes, and its contents are not saved as a worksheet table unless the macro writes them out.
A VBA Collection is another built-in option, but it does not provide the same convenient Exists method. A collection can use keyed items, yet checking whether a key is present generally requires error handling or a separate lookup strategy. If your code needs frequent membership checks and key-based updates, a dictionary is usually clearer on Windows. If cross-platform support is essential, weigh a collection-based design or a custom lookup approach against the added implementation work.
Frequently Asked Questions
Can a VBA dictionary have duplicate keys?
No. Each key must be unique under the dictionary’s comparison rules. Use a different key for each entry, or combine fields into a composite key if one field alone is not unique.
How do I get all keys from a dictionary?
Use the Keys property, which returns an array of keys. You can loop through it with a Variant variable and For Each, or store the returned array for further processing.
How do I make dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare before adding entries. With that setting, text keys that differ only by capitalization are treated as the same key.
Does Scripting.Dictionary work in Excel for Mac?
Do not assume that it does. The common Scripting Runtime dictionary is a Windows COM component and is not reliably available in Mac Excel. Choose a cross-platform alternative if the workbook must run on both systems.
What is the difference between a key and an item?
The key identifies an entry; the item is the value stored under that identifier. In dict("INV-104") = 275, INV-104 is the key and 275 is the item.
Related
- Excel Split Cell Content Into Columns: Methods That Work
- Excel Split Cell Contents by Delimiter
- Excel Split Cell Based on Character: Methods That Work
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Delete Rows With a Specific Cell Value in ExcelTo delete Excel rows when a cell in a chosen column contains a specific value, loop upward from…
- Is the Excel Macro Recorder Portable?Yes—the Excel Macro Recorder can create macros that travel with a workbook, but the recorder itself is a…
- Excel VBA: Loop Through All Open Workbooks in a MacroTo loop through every workbook currently open in desktop Excel, use a For Each loop over Application.Workbooks. Declare…
- Best Excel Macro to Get: Useful Tools and Safe PicksThe best Excel macro to add first is a timestamped backup macro: it saves a dated copy of…
- VBA Format Date String: Examples, Patterns, and FixesIn VBA, use Format or Format$ to turn a date value into a text string: for example, Format$(Date,…
- MS Access VBA Error Handling: Guide, Patterns, and TipsIn Microsoft Access VBA, handle runtime errors with an On Error statement: use On Error GoTo HandlerLabel for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.