VBA Dictionary Add Item: Syntax and Examples

VBA & macros · Updated November 18, 2026

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: Add cannot add a key that is already present. Use Exists and 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 CompareMode before the first entry is added.
  • Missing key creates confusing results: Check Exists before 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.