In Excel VBA, add an item to a Scripting.Dictionary with dict.Add key, item; create the dictionary first, and make sure the key is not already present. The key identifies the entry, while the item is the value stored under that key.
What a VBA dictionary is
A dictionary is a collection of key-and-value pairs. For example, a product code can be the key and its description the item. You can then look up a description by code without searching a worksheet row by row. Dictionaries are especially useful for counting repeated values, checking whether a value has been seen, and joining information from two lists.
Unlike a worksheet range, a dictionary exists in VBA while your code runs. It does not automatically read or write cells; your macro has to transfer data between the dictionary and the workbook. Its keys are unique, but several different keys can have the same item. A dictionary also has a Count property that reports how many key-item pairs it contains.
Create a dictionary in Excel VBA
On Excel for Microsoft 365 for Windows, the simplest setup is late binding. Declare the variable as Object and create the dictionary with CreateObject. This avoids adding a library reference to the workbook and is generally convenient when sharing a macro with other Windows users.
Sub CreateAndAddItem()
Dim dict As Object
' Create a Scripting.Dictionary object.
Set dict = CreateObject("Scripting.Dictionary")
' Add one key and its associated item.
dict.Add "SKU-104", "Blue mug"
Debug.Print dict("SKU-104")
End Sub
In the VBA editor, open it from Excel with Alt+F11 on Windows. On a Mac, use Option+F11 or the corresponding Visual Basic Editor command in the Tools menu; keyboard behavior can depend on the keyboard and macOS settings. The code itself is for Windows: Scripting.Dictionary relies on the Windows Scripting Runtime and is not a portable choice for Excel for Mac.
What the Add method does
The method takes two required arguments in this order: dict.Add Key, Item. Key is the unique identifier, and Item is the associated value. For instance, dict.Add "CA", "Canada" stores the text Canada under the key CA. You can use variables instead of literal values, and the item can be a number, text, or another value that VBA can store in a Variant.
Calling Add does not replace an existing pair. If the key is already in the dictionary, VBA raises an error rather than silently overwriting the item. That behavior is useful when a repeated key signals a problem in the source data, but it means code should check for an existing key when duplicates are expected.
For example, with product codes in a worksheet column, the macro might use each code as a key and a description from another column as its item. A key should be stable and specific enough to distinguish records. If a code is blank or repeated, decide what that means for the task instead of assuming the dictionary will resolve it automatically.
Check for a key before adding it
Use the Exists method to test whether a key is already present. It returns True when the key exists and False otherwise. This is usually clearer and safer than allowing an error to occur and then trying to handle it.
Sub AddOnlyIfMissing()
Dim dict As Object
Dim productCode As String
' Create the dictionary and set a sample key.
Set dict = CreateObject("Scripting.Dictionary")
productCode = "SKU-104"
' Add only when the key has not been stored yet.
If Not dict.Exists(productCode) Then
dict.Add productCode, "Blue mug"
End If
End Sub
This pattern keeps the first item already stored for a key. If you instead want the most recent value to win, check the key and assign to its item when it exists. Make that choice explicit: keeping the first value, replacing it, and combining duplicate records are different rules, and the appropriate one depends on the data.
Retrieve, update, and remove items
To retrieve an item, use the key in parentheses, as in dict("SKU-104"). You can assign that result to a variable or print it. Check Exists first when the key may be absent; asking for a missing key through the Item property can create an entry with an empty item, which may hide a misspelled key or unexpected lookup.
To change the value for a key that is already present, assign a new value through the Item property. To remove a pair, use Remove. The following complete example shows the basic operations and checks before looking up or changing an entry:
Sub ReadUpdateAndRemove()
Dim dict As Object
Dim productName As String
' Set up a dictionary with one entry.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "SKU-104", "Blue mug"
' Read or update only when the key exists.
If dict.Exists("SKU-104") Then
productName = dict("SKU-104")
dict("SKU-104") = "Navy mug"
Debug.Print productName
End If
' Remove the entry if it is present.
If dict.Exists("SKU-104") Then dict.Remove "SKU-104"
End Sub
Use RemoveAll when you want to empty a dictionary but keep the object available for reuse. Use Set dict = Nothing when you are finished with an object variable; it releases that reference. In a short procedure, VBA will also release local variables when the procedure ends, but setting an object to Nothing can make cleanup intentions clear.
Loop through dictionary entries
You can iterate through the keys and retrieve each corresponding item. The key variable should be a Variant, because dictionary keys are not restricted to one VBA data type. This example prints each pair to the Immediate window, which you can show in the Visual Basic Editor with Ctrl+G on Windows.
Sub PrintDictionaryEntries()
Dim dict As Object
Dim key As Variant
' Add sample pairs to the dictionary.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "CA", "Canada"
dict.Add "JP", "Japan"
' Visit each key and print its item.
For Each key In dict.Keys
Debug.Print key, dict(key)
Next key
End Sub
The Keys and Items properties return arrays of the dictionary’s keys and items. They can be useful when transferring results elsewhere, but the dictionary does not sort its contents for you. If a particular order matters, sort the keys or write the values to a range and sort that output using an appropriate method.
Choose key comparison behavior
For text keys, comparison behavior determines whether capitalization matters. The default binary comparison treats keys such as "Blue" and "blue" as different. Set CompareMode to vbTextCompare before adding entries if you want case-insensitive text matching. Attempting to change the mode after adding keys can raise an error, so set it immediately after creating the object.
Sub AddCaseInsensitiveKey()
Dim dict As Object
' Set comparison mode before adding any entries.
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict.Add "Blue", "First value"
' This lookup finds the key despite different capitalization.
Debug.Print dict.Exists("blue")
End Sub
Choose the comparison rule based on the meaning of the key. Case-insensitive comparison can suit country codes or names where capitalization is inconsistent, but it can also merge values that your data treats as distinct. For keys that are not text, consider how VBA represents the values and test the actual inputs rather than relying on a comparison setting to resolve inconsistent source data.
Common errors and how to avoid them
- Duplicate key error:
Addcannot add a key that is already present. UseExistsand decide whether to skip, update, or report the duplicate. - Object variable not set: An object variable must be initialized with
Set dict = CreateObject(...)or an equivalent object-creation statement before calling its methods. - Wrong argument order: The first argument is the key and the second is the item. Reversing them may store data under an unintended key.
- Comparison mode set too late: Set
CompareModebefore the first entry is added. - Missing key creates confusing results: Check
Existsbefore retrieving an item if the key might not be there. - ActiveX or automation error: The Windows Scripting Runtime may be unavailable or blocked in a particular environment. Confirm that the code is running in desktop Excel for Windows and that the object can be created.
When a macro reports an error, note the highlighted line and the error description. A failure on CreateObject points to object availability or environment; an error on Add often points to a repeated key; and an error on CompareMode may mean entries were added first. Debugging the specific operation is more useful than changing the reference or suppressing all errors.
Early binding, late binding, and platform limits
Late binding, shown above, uses Dim dict As Object and CreateObject. Early binding uses the specific Dictionary type, which can provide editor autocomplete and compile-time type checking. On Windows, enable the Microsoft Scripting Runtime reference in the VBA editor by choosing Tools > References and checking the matching library entry. Then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary.
Early binding requires the reference to be available and checked on the computer opening the project. Late binding avoids that reference dependency, but it does not make the Windows COM component available on other platforms. Neither form makes Scripting.Dictionary a cross-platform solution for Excel VBA. Before distributing a workbook to Mac users, choose an implementation supported on both platforms and test it in the target Excel version.
If you use VBA on a Mac, possible alternatives include a Collection, arrays with a separate search routine, or a custom class that stores key-value pairs. A Collection can associate an item with a string key, but its behavior and methods differ from a dictionary; it does not offer the same direct Exists method. Arrays may be suitable for small, simple datasets, while a custom approach requires more code. Choose based on the operations needed and the workbook’s supported platforms.
Frequently Asked Questions
How do I add an item to a VBA Dictionary?
After creating the object, call dict.Add key, item. For example, dict.Add "CA", "Canada" stores Canada under the key CA.
How do I add a key only if it does not exist?
Use If Not dict.Exists(key) Then dict.Add key, item. This avoids the duplicate-key error and lets you define how repeated source values should be handled.
Can I change an item after adding it?
Yes. If the key exists, assign the replacement through the Item property, such as dict(key) = newValue. Check for the key first when it may be missing.
Does VBA Dictionary work in Excel for Mac?Scripting.Dictionary depends on the Windows Scripting Runtime, so it is not a portable Mac solution. Use a supported alternative such as a Collection, array-based lookup, or custom class if the macro must run on Mac.
What is the difference between a key and an item?
The key is the unique label used to find an entry; the item is the value stored for that label. In a product lookup, the product code could be the key and its description the item.
Before running a macro that changes workbook data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the simplest way to preserve the original if the code behaves differently than expected.
Related
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Input Box to Select a Row by NumberTo select a worksheet row by its number, use VBA’s Application.InputBox with Type:=1, validate the entered number, then…
- Excel VBA: What It Is, How It Works, and When to Use ItYes—desktop Microsoft Excel includes VBA, or Visual Basic for Applications, which lets you automate tasks and create macros.…
- Excel VBA: Check If a String Contains a Substring in a CellIn Excel VBA, use InStr to check whether one string contains another: it returns a position greater than…
- 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,…
- VBA Coding Dictionary: How the Dictionary Object WorksA VBA Dictionary is an object that stores pairs of keys and values, letting an Excel macro look…
- Excel VBA: Match a Trendline Label to Its Line ColorYes. In Excel VBA, set the trendline label’s font color to the trendline’s line color: use Trendline.DataLabel.Font.Color and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.