VBA Dictionary Exists and Add: How They Work

VBA & macros · Updated November 18, 2026

Yes. In a VBA Dictionary, use Exists to check whether a key is already present and Add to insert a new key and its associated item; checking first prevents the duplicate-key error that Add raises. On Windows, you can create the Dictionary with late binding, so the basic pattern works without adding a reference in the VBA editor.

What a VBA Dictionary Is

A Dictionary is an object that stores pairs: a key and an item associated with that key. The key identifies an entry and must be unique within that Dictionary; the item is the value you want to retrieve or update. For example, a product code can be a key and its stock count can be the item. Unlike a worksheet lookup table, a Dictionary is held in memory while the macro runs.

In Excel VBA on Windows, the commonly used object is Scripting.Dictionary, provided by the Microsoft Scripting Runtime. You can create it with a reference (early binding) or with CreateObject (late binding). Late binding is convenient for sharing a workbook because a user does not need to set the reference manually, but VBA cannot offer the same compile-time type checking or member suggestions for the object.

What Exists and Add Do

Exists accepts a key and returns True if that key is present, or False if it is not. Add inserts a key-item pair. These methods solve different parts of the task: Exists answers whether an entry is already there; Add creates an entry that is not there yet. Add does not mean “add one to the current value.” For that, retrieve and update the item after checking whether the key exists.

A key can have an item that is a number, text, date, object, or another supported Variant value. Different keys can point to the same item value. What must remain unique is the key, not the item. This makes a Dictionary useful for associating each distinct label with a count, a row number, or a more complex value.

A Safe Exists-Then-Add Pattern

This complete example reads labels from A2:A100 on a worksheet named Sheet1 and counts how many times each nonblank label occurs. Change the sheet name and range to match your workbook. The first occurrence creates the entry with a count of one; later occurrences update the existing item instead of calling Add again.

Option Explicit

Sub CountLabels()
    ' Save a copy of the workbook before running a macro.
    Dim dict As Object
    Dim cell As Range
    Dim key As String
    Dim itemKey As Variant

    Set dict = CreateObject("Scripting.Dictionary")

    For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("A2:A100").Cells
        key = CStr(cell.Value2)

        If Len(key) > 0 Then
            If dict.Exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell

    For Each itemKey In dict.Keys
        Debug.Print itemKey, dict(itemKey)
    Next itemKey
End Sub

The results appear in the Immediate window in the VBA editor, which you can open with Ctrl+G on Windows. This example counts exact text keys under the Dictionary’s default comparison setting. A macro can change workbook data and cannot be undone with Ctrl+Z, so save a separate copy before running or adapting code.

Choosing Early or Late Binding

With late binding, declare the variable as Object and create it with CreateObject, as in the example. This avoids a manual reference setting and is often practical when distributing a Windows workbook to colleagues whose VBA references may differ. The trade-off is that the editor may not list Dictionary members while you type, and some errors that early binding could flag during compilation appear only when the code runs.

For early binding on Windows, open the Visual Basic Editor, choose Tools > References, and select Microsoft Scripting Runtime. Then declare the variable as Scripting.Dictionary and create it as a new Scripting.Dictionary. The reference makes the type and its members visible to the editor, but another computer may need the same reference available and selected. If you use this approach, check references on the destination computer when a project reports a missing reference.

Keys, Case Sensitivity, and CompareMode

By default, Dictionary text keys are compared using binary comparison. In practical terms, uppercase and lowercase spellings such as North and north are treated as different keys. If a workbook should treat text keys without regard to case, set the Dictionary’s CompareMode to vbTextCompare immediately after creating it and before adding any keys. Changing CompareMode after entries have been added can raise an error.

Text comparison is not the same as cleaning or normalizing input. Extra spaces can still make two labels different, and text comparison does not automatically make differently spelled names equivalent. If the source contains accidental spaces, decide explicitly whether to trim them before using each value as a key. Also decide whether blanks should be skipped, as the example does, or treated as meaningful entries.

Keys are not restricted to strings, but a consistent key type makes code easier to reason about. Converting worksheet values to text is often suitable for labels and codes. It may not be suitable for numeric identifiers when distinctions such as leading zeroes matter: a value stored as 0012 may already have been interpreted as the number 12 by Excel. Preserve such identifiers as text in the worksheet if the leading zeroes are significant.

Adding, Updating, and Reading Items

Use Add when creating a key for the first time. To change an existing item’s value, assign a new value to that key instead of calling Add again. In the counting example, the existing count is increased by one; the first occurrence uses Add to create the pair. The same pattern can store a running total, a status, or a reference to a worksheet row.

Check Exists before reading a key when the key might be absent. A useful safeguard is to avoid assuming that a missing lookup returns a meaningful default. In Scripting.Dictionary, using the Item property with a key that is not present can create an entry with an empty item. An accidental read can therefore alter the Dictionary. An Exists check makes the intent clear and prevents that surprise.

Other useful members include Count, which reports the number of key-item pairs, Keys, which returns the keys, Items, which returns the items, Remove, which deletes one key, and RemoveAll, which clears the object. Keys and Items can help when looping through stored data, but the position of a pair in those returned arrays should not be treated as a permanent identifier. Use the key itself to retrieve the associated item.

Common Errors and How to Avoid Them

  • Run-time error 457: This indicates that a key already exists when Add is called. Check Exists first, or choose an explicit update rule for duplicates.
  • Object variable not set: The Dictionary was declared but not created. Assign the object with CreateObject or New before calling Exists, Add, or another member.
  • Unexpectedly separate text keys: Default binary comparison distinguishes case. Set CompareMode before adding entries if case-insensitive matching is the intended rule.
  • Unexpectedly combined keys: With text comparison enabled, keys differing only by case are treated as the same key. Consider whether that fits the data before using it.
  • Wrong or missing counts: Inspect how the code converts cell values to keys and handles blanks, spaces, errors, and numbers stored as text. A Dictionary matches keys; it does not clean inconsistent source data for you.
  • Reference errors: Early-bound projects depend on the selected library reference. Use late binding on Windows when that dependency is undesirable, or repair the reference when the project specifically needs early binding.

Windows, Mac, and Alternatives

The Scripting.Dictionary examples here target Excel for Microsoft 365 on Windows. It is a Windows scripting component exposed through COM automation, so do not assume that CreateObject with Scripting.Dictionary will work in Excel for Mac. Excel for the web cannot run VBA macros at all. If a workbook must run on Mac or in a browser, verify the available platform-specific options before building the solution around this object.

For a small set of entries, VBA’s Collection can provide keyed storage, though its interface and duplicate-key handling differ from a Dictionary and it does not provide the same Exists method. A worksheet table may be easier to inspect and maintain when users need to see or edit the data. If the job is a worksheet calculation rather than in-memory macro logic, lookup or counting functions may be more appropriate. The best choice depends on whether the data needs fast key-based access inside VBA, visible storage on a sheet, or cross-platform execution.

Frequently Asked Questions

Does Dictionary.Exists add a key if it is missing?
No. Exists only checks for a key and returns True or False. Use Add to create a new pair, or apply a deliberate update rule when the key is already present.

Can I use Add to increase a Dictionary value?
No. Add creates a key-item pair and fails if that key already exists. Check Exists, then update the item’s value for an existing key; use Add for the first occurrence.

Why does Dictionary.Add give error 457?
The key being added is already present, possibly because it appeared earlier in the source data or because the comparison mode treats it as equivalent to an existing key. Check for duplicates and decide how they should be handled.

Is a VBA Dictionary case-sensitive?
By default, text keys use binary comparison, so differently cased text can be distinct. Set CompareMode to vbTextCompare before adding keys when case-insensitive matching is required.

Does Scripting.Dictionary work in Excel for Mac?
Do not rely on the Windows Scripting.Dictionary COM component for a Mac workbook. Excel for the web does not execute VBA. Choose and verify a Mac-compatible or worksheet-based alternative if cross-platform use is required.

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.