VBA Dictionary Exists: Check Whether a Key Is Present

VBA & macros · Updated November 18, 2026

Yes. In VBA, a Scripting.Dictionary has an Exists method that returns True when a specified key is present and False when it is not. For example, after creating a dictionary named dict, check a key with dict.Exists("A-104").

What Dictionary.Exists Checks

A dictionary stores pairs: a unique key and an associated item, which is the value for that key. A key might be a product code, employee ID, filename, or other identifier; its item could be a name, status, total, or object. The Exists method asks only whether the key is in the dictionary. It returns a Boolean value and does not return the item itself.

That distinction matters when a key is present but its item is blank, zero, or Empty. Those values do not mean the key is absent. Exists still returns True because the key has been added. Conversely, a value that looks like a key in a worksheet is not in the dictionary until your code adds it.

A dictionary is not a built-in VBA language function like Len. It is an object provided by the Microsoft Scripting Runtime on Windows. Its Exists method is useful for branching safely: test first, then retrieve or update the associated item only when appropriate.

Basic Syntax and a Working Example

The method takes one argument, the key to look for. The usual pattern is If dict.Exists(key) Then. Here is a small complete example for Excel VBA on Windows:

Sub CheckDictionaryKey()
    Dim dict As Object
    Dim lookupKey As String

    ' Create a late-bound Scripting.Dictionary.
    Set dict = CreateObject("Scripting.Dictionary")

    ' Store a key and its associated item.
    dict.Add "A-104", "Open"
    lookupKey = "A-104"

    ' Exists returns True for this key.
    If dict.Exists(lookupKey) Then
        Debug.Print "Found: " & dict(lookupKey)
    Else
        Debug.Print "Key not found"
    End If
End Sub

The Immediate window displays Found: Open when the procedure runs. To see it in the Visual Basic Editor, press Ctrl+G on Windows. On Mac, the equivalent shortcut is Control+G in the Visual Basic Editor; shortcut behavior can vary with keyboard settings. Save a copy of the workbook before running any macro: VBA macros cannot be undone with Ctrl+Z.

In this example, dict.Exists(lookupKey) is the test, and dict(lookupKey) retrieves the item after the test succeeds. Keeping those operations separate makes the code’s intent clear and avoids accidentally treating a missing key as though it had a meaningful value.

Creating a Dictionary in Excel VBA

There are two common ways to declare and create a dictionary on Windows. The example above uses late binding: declare the variable as Object and create the object with CreateObject("Scripting.Dictionary"). This avoids selecting a reference in the VBA editor and is convenient when sharing a workbook across Windows computers with different reference configurations.

Early binding uses the specific Scripting.Dictionary type. In the Visual Basic Editor, choose Tools > References, select Microsoft Scripting Runtime, and then declare the variable with that type. Early binding can provide clearer type information and editor assistance, but the reference must be available and enabled on the computer running the code. A missing reference can cause a “MISSING” entry in the References list and prevent the project from compiling.

Both approaches create the same kind of dictionary and use the same Exists method. Whichever you choose, the object must be created before you call the method. If an object variable is still Nothing, attempting to use dict.Exists raises an object-related runtime error rather than returning False. A key that was never added is different: for an initialized dictionary, testing that key returns False.

How Exists Differs from Looking Up an Item

A common mistake is to test a key by reading dict(key) and checking whether the result is blank. That is unreliable because a valid key can have a blank item, and the item can be a value such as zero or Empty. It can also change the dictionary: with a Scripting.Dictionary, requesting the Item for a key that is not present can create that key with an empty item. Use Exists when the question is specifically whether the key has been stored.

Do not confuse Exists with Add or Item. Add inserts a key-item pair and raises an error if the key already exists. The Item property gets or sets the value associated with a key. Exists checks membership without asking for the value. A typical decision is:

  • Use Exists to check whether the key is present.
  • Use Add when you intend to insert a new key-item pair.
  • Use dict(key) to retrieve or update an item when you know how the key should be handled.

For example, when importing IDs that might repeat, test with Exists before calling Add. If the ID is already present, update its item or handle the duplicate; if it is new, add it. This prevents the duplicate-key error that an unconditional Add would produce.

Key Comparisons and Case Sensitivity

By default, a dictionary uses binary comparison for string keys, so capitalization can matter: "North" and "north" may be treated as different keys. If the application should treat text keys without regard to case, set the dictionary’s CompareMode to vbTextCompare before adding any entries. Changing the comparison mode after keys have been added can raise an error.

Choose the comparison rule to match the identifiers, not simply to make a lookup succeed. A case-sensitive code system may distinguish ab7 from AB7; a list of people or categories may not. Text comparison follows the system’s locale rules, so unusual characters can have language-specific comparison behavior. Decide the rule when the dictionary is empty and use it consistently for all keys.

Also make sure the key you test has the same intended representation as the key you inserted. A value read from a cell may contain leading or trailing spaces, or may be stored as a number rather than text. Exists checks the supplied key; it does not trim spaces, standardize capitalization, or repair inconsistent IDs for you. Normalize values before insertion and lookup when those differences should not matter.

Common Errors and Troubleshooting

If a call to Exists fails, first confirm that the dictionary was created successfully and that the variable still refers to it. With late binding, a failure at CreateObject can indicate that the required Windows component is unavailable or that the code is running in an environment that does not support it. With early binding, check the project’s reference settings.

Other common issues include:

  • Duplicate keys: Add does not overwrite an existing key. Check with Exists first if repeats are possible.
  • Unexpected false results: Compare the exact key, including spaces, punctuation, capitalization rules, and how cell values are represented.
  • Wrong comparison mode: Set CompareMode before inserting keys, then use a mode that fits the data.
  • Blank item mistaken for absence: A stored key may legitimately point to an empty value. Test the key with Exists, not by testing whether its item is blank.
  • Using a worksheet range as a key by accident: Pass a single, deliberate key value. When reading from a cell, assign its value to a variable and check that value.

For debugging, print or inspect the key immediately before the test and inspect the dictionary’s Count property. A nonzero count confirms that entries exist, but does not prove that the particular key is present. If the dictionary is built from worksheet data, check for blank rows and inconsistent formatting at the point where each key is added.

When a Dictionary Is Useful

Dictionaries are especially handy when code needs quick membership checks or repeated access by an identifier. For instance, a macro can read a column of approved product codes into a dictionary, then check each code in another list with Exists. This is often easier to reason about than searching the source range separately for every row.

They can also support counting and grouping. A macro processing transaction IDs might use each ID as a key and store a count as its item: if Exists is true, increase the existing count; otherwise, add the key with a starting count. For a lookup, the item might instead store a product description or another value. The dictionary itself does not impose a worksheet layout; VBA code is responsible for reading and writing cells.

For formulas or ordinary worksheet lookups, a dictionary is not automatically the best tool. Excel functions such as XLOOKUP can be more transparent to workbook users, and a table or PivotTable may suit a task that should remain visible and refreshable on the sheet. A dictionary is most useful when the logic is part of a macro and many checks or updates are needed during one run.

Windows, Mac, and Alternatives

This guidance targets Excel for Microsoft 365 on Windows. Scripting.Dictionary is a Windows scripting/COM component, not a portable VBA language feature. Excel for Mac supports VBA, but you should not assume that CreateObject("Scripting.Dictionary") will work there; the component may be unavailable. A workbook intended for both platforms needs a tested alternative or platform-specific code.

For small collections, VBA’s built-in Collection can be an alternative, though it does not offer the same Exists method. Code using a collection may need to handle a missing key through error handling or maintain a separate lookup structure. Another option is to use worksheet formulas or a range-based search when the data should stay on the sheet. For larger cross-platform VBA projects, a custom class or a carefully designed lookup routine can provide consistent behavior, at the cost of more code.

Before distributing a macro, test it on each platform and Excel version that matters. Do not assume that a workbook’s successful run on Windows guarantees that its COM-based object creation will work on Mac or in Excel for the web; Excel for the web does not run VBA macros.

Frequently Asked Questions

Does VBA have a built-in Dictionary.Exists function?
No. Exists is a method of the Scripting.Dictionary object, rather than a standalone VBA function. Create or obtain a dictionary object first, then call its method.

What does Dictionary.Exists return?
It returns a Boolean: True if the specified key is present and False if it is not. The result says nothing about whether the associated item is blank or nonblank.

How do I check whether a key is missing?
Use If Not dict.Exists(key) Then for the missing-key branch. This assumes dict has already been created; it does not handle an uninitialized object variable.

Can I use Exists to avoid duplicate-key errors?
Yes. Check whether the key exists before calling Add. If it does, decide whether to update the existing item, ignore the duplicate, or report it; if it does not, add the new pair.

Does Scripting.Dictionary work in Excel for Mac?
Do not rely on it being available. It is a Windows COM component, and object creation can fail on Mac. Use a portable alternative or test the exact Mac environment where the workbook will run.

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.