In VBA, a dictionary key is a unique identifier used to store and retrieve an associated value in a Scripting.Dictionary. Each key can appear only once in a dictionary; use .Exists to check for a key, and use the dictionary’s comparison mode to control how text keys are matched.
What a VBA dictionary key represents
A dictionary is a key-and-item collection: the key identifies an entry, and the item is the data associated with it. For example, a customer ID can be a key and the customer’s name can be its item. Unlike a worksheet row number, a key is meaningful because of its value, not its position in a list.
VBA does not provide a Dictionary keyword as part of the language itself. In Excel VBA on Windows, the commonly used dictionary is the Scripting.Dictionary object. It is useful when code needs quick lookups, unique identifiers, or a way to group values without repeatedly scanning a range.
- Keys are unique: adding an entry with a key already present raises an error.
- Items do not have to be unique: separate keys can point to the same item.
- Keys are not row numbers: entries are retrieved by key, not by their position.
- A key can be changed: the Dictionary object provides a
Keyproperty for renaming an existing key.
How keys are compared
For text keys, comparison behavior is controlled by the dictionary’s CompareMode property. The default is binary comparison, which treats uppercase and lowercase letters as different. Under that setting, "North" and "north" can be separate keys. With text comparison, those two strings are treated as equivalent, so adding both as separate keys is not allowed.
Set the mode before adding entries. Changing CompareMode after the dictionary contains data causes an error. The usual choices are vbBinaryCompare for case-sensitive matching and vbTextCompare for case-insensitive text matching. Choose deliberately: case-insensitive matching can prevent accidental duplicates, but it can also merge identifiers that are meant to differ by capitalization.
For example, employee codes may be intended to match regardless of capitalization, while passwords or case-sensitive system identifiers should not be normalized this way. If the source data has inconsistent spaces or punctuation, comparison mode does not clean those differences; "North" and " North " remain different strings. Clean or standardize input before using it as a key when that is part of the data rules.
Creating a dictionary on Windows
There are two common ways to create a Scripting.Dictionary. Late binding uses CreateObject and does not require a project reference. Early binding declares the specific Dictionary type and requires the Microsoft Scripting Runtime reference. Late binding is often easier when sharing a workbook across Windows computers with different reference configurations; early binding gives the VBA editor more information about the object while coding.
In the Visual Basic Editor on Windows, early binding can be enabled through Tools > References, then selecting Microsoft Scripting Runtime. The following late-bound example sets case-insensitive comparison before adding a key:
Sub CreateCustomerDictionary()
' Save a copy of the workbook before running a macro; macros cannot be undone with Ctrl+Z.
Dim customers As Object
Set customers = CreateObject("Scripting.Dictionary")
customers.CompareMode = vbTextCompare
customers.Add "C104", "Avery Chen"
Debug.Print customers("C104")
End Sub
The example prints Avery Chen in the Immediate window. The dictionary exists in memory for the duration of the procedure unless it is stored in a variable with a longer lifetime or its contents are written elsewhere. Creating a dictionary does not automatically save its entries into the workbook.
Adding, checking, and retrieving keys
Use .Add key, item when the code expects the key to be new. Use .Exists(key) when you need to test whether an entry is present before retrieving it or adding to it. The item can be a string, number, object, or other value suitable for the task; the key is commonly a string or number. Keep key types consistent when the same logical identifier might arrive in different forms, such as numeric 104 in one place and text "104" in another.
This example counts occurrences in a list. It checks for the key before accessing the item, so the first occurrence can be initialized safely:
Sub CountLabels()
' Save a copy of the workbook before running a macro; macros cannot be undone with Ctrl+Z.
Dim counts As Object
Dim labels As Variant
Dim label As Variant
Set counts = CreateObject("Scripting.Dictionary")
counts.CompareMode = vbTextCompare
labels = Array("Blue", "Red", "blue", "Green")
For Each label In labels
If counts.Exists(CStr(label)) Then
counts(CStr(label)) = counts(CStr(label)) + 1
Else
counts.Add CStr(label), 1
End If
Next label
Debug.Print counts("BLUE")
End Sub
With text comparison enabled, the printed result is 2, because "Blue" and "blue" match as keys. The conversion with CStr makes the example’s key type explicit. When using real data, choose a conversion that fits the identifier; converting every key to text is not always appropriate.
Key types, normalization, and duplicates
Keys should be stable, and they should express the identity of an entry rather than a description that may change. An account number, product code, or normalized email address is often a better key than a person’s display name, since two people can share a name and a name can change. If a key is assembled from multiple fields, use a delimiter or another unambiguous encoding so different combinations cannot accidentally produce the same text.
A duplicate-key error is not necessarily a VBA bug. It may indicate that the input has repeated identifiers, that text comparison is combining keys with different capitalization, or that the code is calling .Add without checking first. Decide what a repeat means before choosing a response:
- Reject it with an error if duplicates indicate invalid input.
- Skip it if the first occurrence should be retained.
- Replace the item if the newest value should win.
- Accumulate values, as in the counting example, if repeated records should be combined.
When keys come from cells, consider trimming leading and trailing spaces and applying consistent casing only when the business rules say those differences should not matter. Do not silently alter identifiers if capitalization or spaces are meaningful in the source system.
Enumerating and listing dictionary keys
The Keys property returns an array of the dictionary’s keys. A For Each loop is a convenient way to process them. Declare the loop variable as a Variant, because the returned keys may not all be strings. The following example prints each key and its associated item:
Sub PrintDictionaryEntries()
' Save a copy of the workbook before running a macro; macros cannot be undone with Ctrl+Z.
Dim data As Object
Dim key As Variant
Set data = CreateObject("Scripting.Dictionary")
data.Add "A12", "Paper"
data.Add "B07", "Pens"
For Each key In data.Keys
Debug.Print key, data(key)
Next key
End Sub
Do not treat the order of dictionary keys as a sorting guarantee. If output must be alphabetized or follow a particular sequence, copy the keys to an array and sort them, or use a separate ordered structure. The array returned by .Keys is also useful when transferring keys to a worksheet, but writing values to cells still requires explicit worksheet code.
Changing and removing keys
To update an item while keeping its key, assign to the item: for example, data("A12") = "Recycled paper". To rename a key, use the Dictionary object’s Key property, such as data.Key("A12") = "A13". A replacement key must not conflict with another existing key. If the new identifier is already present, the rename cannot create two entries with the same key.
Use .Remove key to delete one entry, and .RemoveAll to clear the dictionary. Check .Exists first if a missing key should be handled quietly; trying to remove a key that is not present can raise an error. Removing an entry from the dictionary does not delete a worksheet row or change any data that was previously written to cells.
Common problems and how to avoid them
One subtle trap is reading an item for a key that does not exist. With Scripting.Dictionary, using the Item property for a missing key can create that key with an empty item. That behavior can make a later key count unexpectedly larger. Use .Exists before retrieving an item when missing keys are possible, especially when reading untrusted or inconsistent input.
- “Key is already associated with an element”: the key exists already under the active comparison mode. Check with
.Existsor choose an explicit duplicate policy. - Unexpected case matches: inspect
CompareMode; set it before inserting entries. - CompareMode error: set the comparison mode immediately after creating the dictionary and before adding keys.
- Object variable not set: ensure the dictionary was created with
Setbefore using it. - Missing reference error: early-bound projects need the relevant reference; late binding can avoid that reference dependency on supported Windows systems.
Also distinguish an empty item from a missing key. An existing key can legitimately map to an empty string or an empty value. Testing whether the item is blank does not reliably tell you whether the key exists; .Exists answers that separate question.
Dictionary versus Collection and worksheet lookups
A VBA Collection can also hold items and optional string keys, and it is part of VBA. It is a reasonable choice for simple collections, especially when entries are mainly processed in insertion order. A Dictionary is often more convenient when code needs to test for a key with .Exists, replace an item by key, or retrieve a list of keys. The two objects have different interfaces, so switching between them requires adapting the code rather than changing only the declaration.
For data already on a worksheet, a worksheet lookup formula or a range-based approach may be clearer than writing VBA. A dictionary becomes useful when a macro performs many repeated lookups, needs to build a unique set, or groups records while processing them. It is an in-memory tool: it does not replace a table, database, or a formula when the desired result should update automatically as worksheet data changes.
Excel for Mac and portability
Scripting.Dictionary is commonly used through Windows scripting components, and Windows examples using CreateObject("Scripting.Dictionary") should not be assumed to work unchanged on a Mac. Mac Excel does not provide the same Windows COM and ActiveX environment, and availability of scripting components can differ. If a workbook must run on both platforms, check the target Excel versions and test the automation there rather than relying on a Windows-only object.
For cross-platform VBA, consider a built-in Collection or a dictionary implementation designed for VBA portability, and verify its licensing and behavior before adopting it. A Collection does not provide the same .Exists method, so code usually needs a helper routine or error-handling strategy to test for a key. For macros distributed to other people, document the platform requirement and avoid introducing a scripting reference that recipients may not have.
Frequently Asked Questions
Are VBA dictionary keys case-sensitive?
By default, text keys use binary comparison, so letter case matters. Set CompareMode = vbTextCompare before adding entries if case-insensitive matching is appropriate.
How do I check whether a dictionary key exists?
Call dict.Exists(key). It returns True when the key is present and False when it is absent, without relying on the item’s value.
Can two dictionary keys have the same value?
Yes. Keys must be unique, but items can repeat. For example, two product codes can both map to the item value "Office supplies".
How do I get all keys from a VBA dictionary?
Use dict.Keys, which returns an array, or loop with For Each key In dict.Keys. Use a Variant loop variable and sort separately if a specific order is required.
Why does adding a key cause an error?
The key may already exist, including under a case-insensitive comparison mode. Check .Exists before adding and decide whether a duplicate should be rejected, ignored, replaced, or combined.
Related
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Optional Parameters: Syntax and ExamplesYes. In VBA, add the Optional keyword to a procedure parameter and, for most typed parameters, provide a…
- Macro Recorder for Xbox: What Works and What Doesn’tIf you mean a device or feature that records a sequence of Xbox controller inputs and plays it…
- VBA Macros in Google Sheets: What Works InsteadNo. Google Sheets cannot run VBA macros: VBA is Microsoft Excel’s programming language, and Sheets uses Google Apps…
- Excel UserForm Events: What Fires, When, and Why in VBAExcel UserForm events are VBA procedures that run in response to a form’s life cycle or to actions…
- VBA Function As Boolean: How to Return True or FalseYes. In VBA, declare a function with As Boolean when it should return either True or False, then…
- Refresh Pivot Table Using VBA: Code and TipsTo refresh a PivotTable with VBA in Excel for Microsoft 365 on Windows, call its RefreshTable method; to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.