Excel VBA Dictionary Add: Syntax, Examples, and Tips

VBA & macros · Updated November 21, 2026

In Excel VBA, add an entry to a Dictionary with dict.Add key, item, after creating the Dictionary object. The key must be unique; if it is already present, use dict.Exists(key) to decide whether to update the item instead.

What Dictionary.Add does

A Dictionary stores pairs: a key that identifies an entry and an item that holds its associated value. For example, a product code can be the key and its stock count the item. Unlike a worksheet range, a Dictionary is held in memory while the macro runs, so it is useful for looking up values, counting occurrences, and collecting distinct values without repeatedly searching cells.

The Add method takes two arguments in this order: the key, then the item. The key has to be unique within that Dictionary. The item does not have to be unique; several keys can have the same item. A Dictionary also exposes properties and methods such as Count, Exists, Keys, Items, Remove, and RemoveAll.

For instance, dict.Add "P-104", 12 associates the key P-104 with the value 12. A later lookup by that key returns 12. This is a useful distinction: the key is how you find an entry, while the item is the information you store for it.

Create a Dictionary in Excel VBA

For Excel for Microsoft 365 on Windows, a common approach is late binding with CreateObject. It avoids requiring every user of the workbook to set a VBA reference manually:

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Put these lines inside a procedure, or use them as the start of a larger macro. The variable is declared as a generic Object because VBA does not resolve the Dictionary type at compile time with this approach. The actual object is supplied by the Windows Scripting Runtime.

Early binding is another option. In the Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime, then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can provide autocomplete and compile-time type checking. The tradeoff is that the reference must be available and correctly set on other computers running the workbook.

Dictionary.Add syntax and a basic example

The method’s general form is dict.Add key, item. For example, after creating dict, this adds a city and its population figure:

dict.Add "Riverton", 24500

Text keys need quotation marks when written as literals. A variable holding a key does not need quotation marks around the variable name. You can store numbers, text, dates, or other values as items. Choose a key that represents the identity of the record, such as an order number, rather than a value that may change or repeat.

Before running any macro that changes workbook data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is a practical safeguard even when the Dictionary itself is only in memory.

Count repeated values from a worksheet

This example counts nonblank names in column A of a worksheet named Data. It uses each name as a key and the number of occurrences as the item. The comparison mode is set before the first entry so names that differ only by letter case are treated as the same key.

Sub CountNames()
    Dim dict As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim nameKey As String

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    Set ws = ThisWorkbook.Worksheets("Data")

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For r = 2 To lastRow
        nameKey = CStr(ws.Cells(r, 1).Value2)
        If Len(nameKey) > 0 Then
            If dict.Exists(nameKey) Then
                dict(nameKey) = CLng(dict(nameKey)) + 1
            Else
                dict.Add nameKey, 1
            End If
        End If
    Next r

    MsgBox dict.Count & " distinct names found."
End Sub

The code assumes row 1 contains a heading and the names start in A2. Exists checks whether a name has already been added. If it has, the macro increases its count; if not, it uses Add to create the first entry. Here, Count reports the number of distinct keys, not the total number of nonblank worksheet rows.

Prevent and handle duplicate-key errors

Calling Add with a key that already exists raises a runtime error, commonly reported as error 457. This is expected behavior: a Dictionary cannot hold two separate entries with the same key. It does not silently replace the earlier item. If repeated keys are part of the data, decide what they should mean before adding them.

  • Keep the first value: add only when Exists is False.
  • Keep the latest value: assign a new item when the key exists, and call Add only when it does not.
  • Accumulate a total or count: check for the key, then update the existing item or add its starting value.

For a latest-value pattern, use If dict.Exists(key) Then dict(key) = newValue Else dict.Add key, newValue. The assignment updates the item for an existing key. This is different from calling Add again, which produces the duplicate-key error.

A related pitfall is using dict(key) to test whether a key exists. The Dictionary’s Item property can create a missing key when it is accessed, leaving an empty item. Use dict.Exists(key) for the test; it states your intent and avoids accidentally changing the contents.

Key comparison, capitalization, and data types

By default, string keys are compared using binary comparison, which distinguishes uppercase and lowercase text. If case should not matter, set dict.CompareMode = vbTextCompare immediately after creating the object and before adding any entries. For example, under text comparison, “North” and “north” refer to the same key, so adding both separately will cause a duplicate-key error.

Changing CompareMode after entries have been added is not permitted and can raise an error. Set the mode once during setup, before loading data. Use vbBinaryCompare when capitalization is meaningful or when you want the default distinction. Comparison mode affects string keys; it does not clean up spaces, punctuation, or inconsistent spelling. If values such as “North ” and “North” should match, normalize the input deliberately, for example by trimming whitespace before using it as a key.

When worksheet values become keys, consider their underlying data types and formatting. A cell that displays a number with leading zeroes may have a numeric value that does not retain those zeroes. If those digits identify a code, store or read it as text so that the key represents the code as displayed. Converting values consistently also prevents otherwise similar entries from being treated as different keys.

Retrieve, update, and loop through entries

Use the key in parentheses to retrieve an item, such as dict("P-104"). If the key exists, you can assign a new value through the same syntax. Check with Exists before retrieving an uncertain key so a missing lookup does not create an unintended entry.

To process all entries, the Dictionary’s Keys method returns an array of keys. A loop can use those keys to retrieve their corresponding items:

Dim key As Variant
For Each key In dict.Keys
    Debug.Print key, dict(key)
Next key

The loop variable is declared as Variant because the returned keys are provided as an array of Variants. In the Visual Basic Editor, the output appears in the Immediate window, which you can show with Ctrl+G on Windows. On a Mac, the Visual Basic Editor uses its own menus and shortcuts, which can vary by keyboard and Excel release; the VBA code itself is not changed by that shortcut difference.

You can also use dict.Items when you need the stored values as an array. Keys and items are returned in corresponding order, but a key-based loop is generally clearer when you need to associate each value with its identifier.

Binding choices, Mac, and Excel for the web

Late binding with CreateObject is convenient for Windows workbooks shared across machines because it does not require a checked reference in the VBA project. Early binding is helpful during development when you want the Dictionary type recognized by the editor. Both approaches use the Scripting.Dictionary object on Windows; choosing one does not change how Add handles duplicate keys.

Excel for Mac does not provide Windows COM automation in the same way, so a Windows CreateObject("Scripting.Dictionary") macro should not be assumed to work there. If the workbook must run on Mac, consider a VBA Collection, arrays, or a custom VBA class and test the chosen approach on the target Excel version. A Collection can associate items with keys, but it does not offer the same built-in Exists method or convenient key enumeration, so it may require additional error handling or supporting code.

Excel for the web cannot run VBA macros. A Dictionary-based macro therefore needs desktop Excel, even if the workbook is stored online. If the task needs to run in a browser, consider a compatible approach such as worksheet formulas or Office Scripts, depending on the workbook and the required workflow.

Common problems and practical checks

  • “ActiveX component can’t create object”: confirm the code is running in desktop Excel on Windows and that the object name is spelled correctly. It may also indicate an environment where the Windows Scripting Runtime is unavailable.
  • Duplicate-key error: check with Exists and choose whether to skip, replace, or combine the existing item.
  • Unexpected separate entries: inspect spaces, capitalization, and cell values. Apply consistent text cleanup and choose the comparison mode before adding entries.
  • Wrong number of entries: remember that dict.Count counts unique keys, not source rows. Blank cells may also need to be skipped or handled explicitly.

For a large import, it is often more efficient to read a worksheet range into a VBA array first and then build the Dictionary from that array, rather than accessing one cell at a time. That optimization is not necessary for every task, but it can reduce repeated communication between VBA and the worksheet when a macro processes many rows.

Frequently Asked Questions

What is the syntax for adding a Dictionary item in VBA?
Use dict.Add key, item. Create the Dictionary first, and make sure the key is not already present.

How do I add a key only if it does not exist?
Use If Not dict.Exists(key) Then dict.Add key, item. If an existing key should be updated instead, use an If/Else branch and assign the new item in the existing-key branch.

Can a VBA Dictionary have duplicate keys?
No. Each key must be unique within that Dictionary. Multiple entries can have identical items, but adding the same key twice with Add raises an error.

How do I make Dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare immediately after creating the Dictionary and before adding any keys. This makes string key comparison case-insensitive.

Does Scripting.Dictionary work in Excel for Mac?
Do not rely on the Windows Scripting.Dictionary COM object being available on Mac. Use and test a Mac-compatible alternative, such as a Collection or custom VBA structure. VBA macros also do not run in Excel for the web.

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.