Excel VBA: Check Whether a Dictionary Key Exists

Use a VBA Dictionary’s Exists method to check for a key: If dict.Exists(keyToFind) Then. It returns True when the key is present and False when it is not, so you can test safely before reading or adding an item.

What a VBA dictionary does

A dictionary stores pairs: a key and its associated item. For example, a key might be an invoice number and its item might be the invoice status. Keys are unique within one dictionary, while items can be values such as text, numbers, dates, or objects. This makes a dictionary useful when you need to look up a value by an identifier rather than search through a worksheet row by row.

In Excel VBA, the commonly used dictionary is Scripting.Dictionary. Its Exists method answers a specific question: “Does this key already belong to this dictionary?” It does not check whether a worksheet cell contains a value, whether an item is nonblank, or whether another dictionary contains the same key. The dictionary itself must already have been created and populated with the keys you want to test.

For example, if a dictionary holds customer IDs, checking dict.Exists("C104") tells you whether the key C104 is in that dictionary. It says nothing about whether the associated item is blank or whether the customer appears in a separate list.

Basic VBA example

This example creates a dictionary, adds one key, and checks whether that key exists. The code uses late binding, so it does not require you to select a reference in the VBA editor.

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

    ' Create a Scripting.Dictionary at run time.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "INV-104", "Paid"

    keyToFind = "INV-104"
    If dict.Exists(keyToFind) Then
        MsgBox "The key exists. Its item is " & dict(keyToFind)
    Else
        MsgBox "The key does not exist."
    End If
End Sub

Because the dictionary contains INV-104, the first message appears and displays Paid. Change keyToFind to INV-105 and the second message appears. In a real macro, the dictionary would often be filled from a worksheet or another data source before this check.

Before running a macro that changes a workbook, save a copy. VBA macros cannot be undone with Ctrl+Z. On Windows, open the VBA editor with Alt+F11; on Mac, the equivalent shortcut is Option+F11, although some keyboards require the Fn key as well. Excel for the web can store a macro-enabled workbook, but it cannot run VBA macros.

How Exists works in a condition

Exists is a method that returns a Boolean value. Use it directly in an If statement, or assign its result to a Boolean variable if you need to use the result more than once. The method checks keys, not items: if an item is an empty string or zero, the key can still exist.

A useful pattern is to check first and then retrieve the item only in the branch where the key is present. This separates “is the key present?” from “what item is stored under that key?” and makes the intent of the code easy to read. Avoid using an item’s value as a substitute for the existence test. A valid key could map to an empty value, and a missing key is a different condition.

Likewise, the dictionary’s Count property only tells you how many entries the entire dictionary contains. It cannot tell you whether a particular key is present. For an individual key, Exists is the direct test.

Creating a dictionary: late and early binding

The first example uses late binding: the variable is declared As Object and the dictionary is created with CreateObject. This avoids setting a VBA reference and is convenient when distributing a macro to Windows users whose reference settings may differ. The trade-off is that the editor provides less help with Dictionary-specific methods while you type, and some errors are detected only when the code runs.

On Windows, you can instead use early binding. In the Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime. Then declare the variable with the Dictionary type and create it with New:

Sub CheckWithEarlyBinding()
    Dim dict As Scripting.Dictionary
    Dim keyToFind As String

    ' Create the dictionary after enabling its reference.
    Set dict = New Scripting.Dictionary
    dict.Add "C104", "North"

    keyToFind = "C104"
    If dict.Exists(keyToFind) Then
        MsgBox "Customer found: " & dict(keyToFind)
    End If
End Sub

Early binding gives the editor more information about the object and can make code easier to develop. However, a missing reference can prevent a project from compiling on another computer. Late binding avoids that particular reference requirement, but it does not remove the Windows dependency of the COM-based Scripting Runtime.

Case sensitivity and key matching

By default, a Scripting.Dictionary uses binary comparison for string keys. As a result, keys such as North and north are treated as different keys. If your identifiers should be treated as case-insensitive text, set CompareMode to vbTextCompare before adding any keys.

Sub CheckTextComparison()
    Dim dict As Object
    Dim keyToFind As String

    ' Set the comparison rule before adding keys.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    dict.Add "North", 1

    keyToFind = "north"
    If dict.Exists(keyToFind) Then
        MsgBox "A matching key exists."
    End If
End Sub

Here, the check succeeds because text comparison ignores the difference in letter case. Set the comparison mode before inserting entries: changing it after the dictionary contains data can raise an error. Also remember that text comparison does not automatically clean or standardize keys. A trailing space, a different punctuation mark, or a different identifier format can still make two strings unequal. If worksheet values may contain accidental spaces, trim or normalize them consistently when both adding and checking keys.

Check before adding or retrieving

A frequent use of Exists is to prevent duplicate additions. Calling Add with a key that is already in the dictionary causes an error, so test first when duplicates should be ignored or handled separately.

Sub AddKeyIfMissing()
    Dim dict As Object
    Dim newKey As String

    ' Create a dictionary and demonstrate a safe add.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A17", "Existing item"
    newKey = "A17"

    If Not dict.Exists(newKey) Then
        dict.Add newKey, "New item"
    Else
        MsgBox "That key is already in the dictionary."
    End If
End Sub

In this example, A17 is already present, so the code reports the duplicate instead of trying to add it again. If the key is absent, it adds it. In a data-loading macro, you might replace the message with a decision to update the item, skip the row, or record the duplicate for review.

Check before retrieving an item when the key may be absent. An item lookup is not a reliable replacement for Exists: an empty item does not prove that the key is missing, and assignment through an item lookup can create or alter entries. Keeping the existence test explicit helps avoid accidental changes and makes missing-key behavior deliberate.

Common problems and troubleshooting

“Object variable or With block variable not set”: the dictionary variable has not been created. Use Set dict = CreateObject("Scripting.Dictionary") before calling Exists.
“ActiveX component can’t create object”: Windows could not create the requested COM object. Confirm that the code is running in a supported Windows desktop environment and that the Scripting Runtime is available.
A duplicate-key error: the code called Add for a key already in the dictionary. Use Exists to choose whether to skip, update, or handle that entry.
A key that looks identical is not found: compare the actual values for case, spaces, punctuation, and data type. For example, a numeric key and a string containing digits may not be treated as the same key.
Comparison mode cannot be changed: set CompareMode before adding the first key, or create a new dictionary with the desired setting.

When building a dictionary from worksheet data, decide how blank cells should be handled. A blank key may not represent a meaningful identifier, and repeated blank keys can cause duplicate-key errors. It is often clearer to validate each source value before adding it, then use Exists to detect duplicates among valid identifiers.

Windows, Mac, and alternatives

The examples above target Excel for Microsoft 365 on Windows. Scripting.Dictionary is a Windows COM component, so code that creates it with CreateObject or references Microsoft Scripting Runtime is not a portable assumption for Excel on Mac. Mac Excel supports VBA, but Windows COM automation dependencies may not work there. If a workbook must run on both platforms, test the exact code on each target version and consider a VBA Collection, arrays with a search routine, or a custom dictionary implementation that does not rely on COM.

Those alternatives involve trade-offs. A Collection can store keyed items, but its lookup and error-handling behavior differs from a Dictionary and does not provide the same Exists method. Searching an array may be straightforward for a small list, but repeated searches can take longer as the list grows. A dictionary is often convenient for repeated lookups on Windows, while a portable design may favor arrays or a deliberately written cross-platform helper.

Excel for the web is another boundary: it does not run VBA. If the task must execute in a browser, consider whether a worksheet formula or Office Scripts can meet the requirement. Those are different tools with different capabilities; VBA Dictionary code cannot simply be pasted into a cell or run from the web version of Excel.

Frequently Asked Questions

What is the VBA syntax to check whether a dictionary key exists?
Use If dict.Exists(keyToFind) Then. The expression is True if the key is in that dictionary and False otherwise.

Does Dictionary.Exists check the item value?
No. It checks the key only. A key can exist even if its associated item is an empty string, zero, or another value you consider blank.

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

Can I use Scripting.Dictionary in Excel for Mac?
Do not assume Windows COM-based dictionary code will work on Mac. For a workbook that must run on both platforms, test the target Excel versions and use a portable alternative if necessary.

Why does adding a key fail when I already checked it?
The dictionary may have changed between the check and the add, or the code may be checking a different key value than it adds. Ensure both operations use the same normalized key and handle duplicates at the point where they are added.

Can Excel for the web run a VBA dictionary macro?
No. Excel for the web does not execute VBA macros. Use desktop Excel for VBA, or evaluate a web-compatible approach such as formulas or Office Scripts.

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.