VBA Dictionary Add Key: Syntax, Examples, and Common Errors

VBA & macros · Updated November 18, 2026

To add a key and its associated value to a VBA Dictionary, call the dictionary’s Add method: dict.Add "key", value. For example, dict.Add "North", 125 stores the value 125 under the key North; the key must not already exist.

What a VBA Dictionary Is

A Dictionary is a key-value collection: each unique key identifies one item. It is useful when you want to look up a value by a meaningful label, such as a product code, employee name, category, or invoice number. Instead of finding an item by its position, as you would in an array, you ask for the item associated with its key.

In Excel VBA, the commonly used Dictionary is the Scripting.Dictionary object from the Microsoft Scripting Runtime. It is not a worksheet function or a special VBA language keyword. Your code creates an object, adds entries, and then reads or changes those entries through the object’s properties and methods.

A Dictionary is handy for tasks such as counting repeated values, checking whether a code has already appeared, and matching records in memory. It can also make some repeated lookups more convenient than scanning a worksheet range each time. It does not automatically connect to worksheet cells; your macro must read data from and write results to the workbook.

Basic Add Syntax and Example

The method takes two arguments: the key first and the item (the value) second. This example creates a late-bound Dictionary, adds three entries, and retrieves one value:

Sub AddDictionaryKeys()
    Dim dict As Object

    ' Create a Scripting.Dictionary object.
    Set dict = CreateObject("Scripting.Dictionary")

    ' Add each unique key and its associated item.
    dict.Add "North", 125
    dict.Add "South", 98
    dict.Add "West", 143

    ' Show the item stored under the North key.
    MsgBox dict("North")
End Sub

After these statements, the Dictionary contains three key-item pairs. The message box displays 125 because the item is retrieved using the key North. The key and item can be different data types; for example, a text key can point to a number, a date, or another value. In practical code, choose a stable key that uniquely identifies the record you want to retrieve.

Before running any macro that changes a workbook, save a copy. VBA macros cannot be undone with Ctrl+Z, so a backup gives you a way to recover if the macro changes data unexpectedly.

Creating the Dictionary: Late and Early Binding

The example uses late binding: Dim dict As Object and CreateObject("Scripting.Dictionary"). This avoids setting a project reference, which can make a workbook easier to share among Windows computers with the relevant scripting component available. The trade-off is that the VBA editor has less information about the object while you write code, so it may not provide the same method and property suggestions as it does for a specifically typed object.

With early binding, you add a reference in the Visual Basic Editor and declare the Dictionary using its library type. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11, choose Tools > References, and select Microsoft Scripting Runtime if it appears in the list. Then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can offer editor assistance, but another computer may need the same reference available for the project to compile correctly.

Both approaches use the same Add, Exists, Keys, and Items concepts. For a small macro that needs to be portable across Windows installations, late binding is often simpler. For a project maintained in a controlled Windows environment, early binding can make the code easier to explore in the editor.

Adding a Key Only When It Is Missing

Add requires a new key. If the key is already in the Dictionary, VBA raises run-time error 457, which means that the key is already associated with an element. When repeated input is possible, check for the key with Exists before adding it:

Sub AddIfMissing()
    Dim dict As Object
    Dim keyName As String

    Set dict = CreateObject("Scripting.Dictionary")
    keyName = "North"

    If Not dict.Exists(keyName) Then
        dict.Add keyName, 125
    End If
End Sub

Exists returns True if the key is present and False otherwise. This pattern avoids an error when duplicate input should simply be ignored. If duplicates should instead be treated as a data problem, leave out the check and let Add expose the duplicate so that you can handle it deliberately.

Do not confuse an existing key with an existing value. Two different keys can point to the same item, and the same key cannot be added twice under the Dictionary’s comparison rules. If the input data has duplicate identifiers, decide whether to keep the first item, replace it, combine the items, or report the duplicate. The appropriate behavior depends on what those identifiers mean.

Adding, Updating, and Retrieving Items

Use Add when you intend to create a new key-item pair and want VBA to signal a duplicate. You can also assign through the Item property, commonly written as dict(key) = value. If the key exists, that assignment replaces its item; if it does not exist, assigning the Item property creates an entry for it. This can be concise, but it can also conceal an unexpected missing key.

For example, when processing a list of category names and totals, you may want to create a total the first time a category appears, then add later amounts to it. The following pattern separates those two cases:

Sub AddOrUpdateTotal()
    Dim dict As Object
    Dim category As String
    Dim amount As Double

    Set dict = CreateObject("Scripting.Dictionary")
    category = "Office"
    amount = 35.5

    If dict.Exists(category) Then
        dict(category) = dict(category) + amount
    Else
        dict.Add category, amount
    End If
End Sub

Here the first occurrence creates the Office key with a total of 35.5. A later occurrence adds its amount to the stored total. This is a common use of a Dictionary, but the sample handles just one amount; a complete worksheet macro would need to loop through its source rows and read each category and amount.

To retrieve an item, use the key in parentheses, as in dict("Office"). Check Exists first if the key might be absent: requesting an item for a missing key through the Item property can create an entry with an empty item. That behavior can make a simple-looking lookup change the contents of the Dictionary, so existence checks are helpful when a missing key should remain missing.

Key Comparison and Text Case

By default, a Dictionary uses binary comparison for string keys, so keys that differ only by capitalization can be distinct: North and NORTH may be treated as separate keys. If your data should treat capitalization as irrelevant, set CompareMode to vbTextCompare immediately after creating the Dictionary and before adding any entries.

Sub AddCaseInsensitiveKeys()
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    dict.Add "North", 125

    If dict.Exists("NORTH") Then
        MsgBox dict("NORTH")
    End If
End Sub

Because this example uses vbTextCompare, the Exists check finds the North key even though the capitalization differs. If you try to add NORTH as a second key after adding North, it will be considered a duplicate. Set the comparison mode before inserting entries; changing it after the Dictionary contains data can raise an error. The default binary comparison is appropriate when capitalization genuinely distinguishes identifiers, such as certain codes or passwords.

Looping Through Keys and Items

After adding entries, you can inspect them with the Dictionary’s Keys and Items properties, or loop over the keys. A key-based loop makes the relationship between a key and its value explicit:

Sub PrintDictionaryEntries()
    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

The output appears in the Immediate window in the Visual Basic Editor; open it with Ctrl+G on Windows. Declaring the loop variable as Variant is useful because the Keys property returns an array of keys. For example, the loop prints North with 125 and South with 98. Do not assume that the Dictionary is automatically sorted. If you need a particular order, sort the keys separately or write the results to a range and sort them there.

Common Problems and Practical Checks

  • Duplicate-key error: Run-time error 457 usually means the code called Add with a key that already exists. Check with Exists, or decide explicitly how duplicates should be handled.
  • Object variable not set: A Dictionary variable declared as an object must be initialized with Set dict = CreateObject("Scripting.Dictionary") before calling Add or another method.
  • Unexpected capitalization behavior: Set CompareMode before adding keys and choose binary or text comparison according to the identifiers in your data.
  • Missing key changes the Dictionary: Reading or assigning through dict(key) can create an entry when that key is absent. Use Exists before a lookup when you do not want that side effect.
  • Reference or creation failure: Early-bound code depends on the Microsoft Scripting Runtime reference being available. Late-bound code avoids the project reference, but still relies on the Scripting.Dictionary component being supported on the computer running the macro.

When a macro reads worksheet data, also check for blank keys and inconsistent values. A blank string can become a key, which may unintentionally combine records that lack an identifier. Trim or normalize source text if spaces should not distinguish keys, and make that choice consistently before calling Add.

Windows, Mac, and Alternatives

Scripting.Dictionary is associated with the Windows scripting runtime and COM. Excel for Microsoft 365 on Windows is the expected environment for the CreateObject("Scripting.Dictionary") examples here. Excel for the web does not run VBA macros. On Mac, the Windows scripting runtime is not generally available in the same way, so this object-creation approach should not be assumed to work; test on the exact Mac Excel version and deployment environment rather than relying on Windows compatibility.

If your VBA solution must run on Mac as well as Windows, consider a VBA Collection, arrays with a search routine, or a custom class-based structure, and verify the design in both environments. A Collection supports keyed items but does not offer the same convenient Exists method, so missing-key handling takes extra care. Arrays can be straightforward for small datasets, while repeated linear searches may become less suitable as the data grows. Choose based on the scale of the data and the platforms your users actually need.

For a Windows-only macro that needs fast, convenient key lookup, a Dictionary is often a practical option. For data that belongs in the workbook and must remain visible to users, a worksheet table may be easier to inspect and maintain. These approaches are not interchangeable: a Dictionary is an in-memory VBA object, whereas a table is stored in the workbook and can be filtered, edited, and reviewed directly.

Frequently Asked Questions

What is the VBA syntax to add a key to a Dictionary?
Use dict.Add key, item, such as dict.Add "North", 125. Create and initialize the Dictionary first, and make sure the key is not already present.

How do I add a key if it does not already exist?
Use If Not dict.Exists(key) Then dict.Add key, item. This prevents a duplicate-key error while leaving an existing item unchanged.

Does assigning dict(key) add a new key?
Yes. Assigning to the Item property with dict(key) = value creates the entry if the key is missing and replaces its item if it is already present. Use Add when you want duplicate keys to be reported instead.

How do I make Dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare after creating the Dictionary and before adding any keys. With this setting, capitalization differences do not create separate text keys.

Why does Add give run-time error 457?
The key being added is already present under the Dictionary’s comparison mode. Check with Exists, change the key if appropriate, or update the existing item instead of calling Add again.

Can I use Scripting.Dictionary in Excel for Mac?
Do not assume that the Windows Scripting Runtime object is available in Mac Excel. Test the target Mac setup, or choose a VBA Collection, array, or another cross-platform design if the macro must run on both platforms.

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.