VBA Dictionary: Search by Key and Check Whether It Exists

VBA & macros · Updated November 16, 2026

To search a VBA Dictionary by key, call its Exists method before retrieving the value: use If dict.Exists(searchKey) Then, then read dict.Item(searchKey) inside that condition. This avoids treating a missing key as a result and gives you a clear way to handle “not found.” The steps below use Excel for Microsoft 365 on Windows and a late-bound dictionary, so you do not need to add a VBA reference.

What You’ll Need

  • Excel desktop for Windows, with a workbook saved as a macro-enabled .xlsm file if you want to keep the VBA code.
  • Access to the Visual Basic Editor (VBE). On Windows, press Alt+F11; if your keyboard requires it, include the Fn key.
  • A key and value to use in your example. A dictionary stores pairs, such as an item code and its description.

Excel for the web cannot create or run VBA macros. Mac support for Scripting.Dictionary can depend on the Office environment; the Windows CreateObject approach below may not work on a Mac. If it fails there, use a Collection or a Mac-compatible custom class, or run the workbook in Excel for Windows.

Step-by-Step

  1. Open the VBA Editor and insert a module. In Excel for Windows, press Alt+F11. In the editor, choose Insert > Module. If the Developer tab is hidden, show it through File > Options > Customize Ribbon and select Developer. Excel for Mac uses Option+F11 on many keyboards; the shortcut can vary with keyboard settings, and the Developer tab is managed through Excel > Settings (or Preferences) > Ribbon & Toolbar.
  2. Start a procedure and declare the variables. Use an object variable for the dictionary, a string for the key you want to find, and a Variant for the value. A Variant is useful because dictionary items do not have to be strings; they can hold numbers, dates, or other supported values.
  3. Create the dictionary and add sample pairs. The code below uses late binding with CreateObject. It does not require you to select a library under Tools > References. The comparison mode is set before adding keys so the example treats uppercase and lowercase letters in keys as equivalent.
  4. Check the key with Exists, then retrieve its value. Put the lookup inside the If block. If the key is absent, show a not-found message instead of trying to use a value that is not there.
  5. Run the macro and inspect the result. Save a copy of the workbook first: macros cannot be undone with Ctrl+Z. In the VBE, click inside the procedure and press F5, or return to Excel and run it from Developer > Macros. The sample uses a message box, so a successful search should show “Found: Blue notebook.”

Here is a complete macro to paste into the module. Change searchKey to try another lookup, or change the pairs added to the dictionary to suit your data.

Sub SearchDictionaryByKey()
    Dim dict As Object
    Dim searchKey As String
    Dim foundValue As Variant

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

    ' Set this before adding keys; match keys without case sensitivity.
    dict.CompareMode = vbTextCompare

    ' Add example key-value pairs.
    dict.Add "A104", "Blue notebook"
    dict.Add "B205", "Desk lamp"
    dict.Add "C306", "Water bottle"

    ' Try a key; text comparison also matches a104 with A104.
    searchKey = "a104"

    ' Check first, then retrieve the matching item.
    If dict.Exists(searchKey) Then
        foundValue = dict.Item(searchKey)
        MsgBox "Found: " & CStr(foundValue), vbInformation
    Else
        MsgBox "Key not found: " & searchKey, vbExclamation
    End If
End Sub

To test the not-found branch, set searchKey to "X999" and run the macro again. Because the key is not in the dictionary, the message box reports that it was not found. In your own macro, you might instead write a status to a worksheet, skip the current record, or ask the user to check the key.

If your dictionary is already populated elsewhere in your code, you do not need to rebuild it for each search. Keep the same pattern: use the existing dictionary object, check Exists, and only read Item when the key is present. For example, with dict already filled and searchKey assigned, the central test is If dict.Exists(searchKey) Then. Remember that Exists tests keys, not values: it answers whether a key is present, not whether some item happens to contain the text you are searching for.

Common Mistakes

  • Reading an item before checking it. Accessing dict.Item(searchKey) for a missing key can create that key with an empty item in a Scripting.Dictionary. That may make a later search appear successful even though you never deliberately added the key. Check Exists first whenever absence is possible.
  • Confusing a key search with a value search. dict.Exists("A104") asks whether A104 is a key. It does not scan dictionary values for the phrase “Blue notebook.” To find a value, loop through the keys and compare each corresponding item, or arrange your data so the value you need to search becomes a key.
  • Adding a duplicate key with Add. A dictionary cannot contain two identical keys under its current comparison mode. Calling dict.Add for a key that already exists raises an error. Check with Exists before adding, or assign to an existing item when your intention is to update its value.
  • Setting CompareMode too late. Set it immediately after creating the dictionary and before adding any keys. Changing it after keys have been added can cause an error. The default comparison is binary, so letter case can matter unless you choose vbTextCompare.
  • Searching a key with extra spaces. A key such as "A104 " is not the same as "A104". Leading and trailing spaces, punctuation, and other characters are part of the key. Clean both the stored keys and the search input consistently if spaces are accidental.

Tips

  • Use vbTextCompare when keys should match regardless of letter case; use vbBinaryCompare when case should be significant. Choose deliberately, and set the mode before adding entries.
  • Normalize keys in the same way when storing and searching them. For example, trim unwanted spaces from imported text on both sides of the lookup. Do not normalize only the search term if the stored keys still contain inconsistent characters.
  • Use descriptive names such as searchKey and foundValue. This makes the difference between a key and its associated item clearer when you revisit the macro.
  • If you need to process every entry, use the dictionary’s Keys collection to loop over keys, then access each corresponding item. A dictionary is designed for key-based lookup; it is not a worksheet range and does not search its values automatically.
  • For a large set of lookups, create and fill the dictionary once, then reuse it. Rebuilding it for every row adds unnecessary work. Also consider whether keys must be unique before loading the data.

Troubleshooting

“ActiveX component can’t create object” or a related error at CreateObject: Confirm you are running desktop Excel on Windows and that the code uses CreateObject("Scripting.Dictionary") exactly. This Windows COM approach may not be available in Excel for Mac, and it will not run in Excel for the web. On a Mac, use an alternative data structure or a compatible implementation rather than assuming the Windows object is installed.

The macro will not run: Check that the file is saved as .xlsm, the code is in a standard module, and macros are permitted by your organization’s security settings. Do not enable all macros indiscriminately; follow your organization’s policy and only run code you trust. If Excel shows a compile error, use Debug in the error dialog to jump to the highlighted line and check spelling, quotes, and block endings.

The key is reported as missing even though it looks present: Compare the actual strings, not just how they appear in a cell. Check for leading or trailing spaces, different punctuation, nonbreaking spaces from copied data, and case sensitivity. Test with a short key entered directly in code, then inspect or clean the imported key values consistently.

A duplicate-key error appears: The same key was added more than once. Before adding a new entry, check dict.Exists(key) and decide whether to skip it, report it, or replace its existing item. Do not silently overwrite unless that is the intended rule for your data.

Frequently Asked Questions

What is the VBA method for checking whether a dictionary key exists?
Use dict.Exists(key). It returns True when the key is present and False when it is absent.

How do I get the value after finding the key?
Inside the If dict.Exists(key) Then block, read dict.Item(key) and assign it to a variable. Use a Variant if you do not know whether the item is text, numeric, or another type.

Can I search dictionary keys without regard to capitalization?
Yes. Set dict.CompareMode = vbTextCompare immediately after creating the dictionary and before adding any keys. With that mode, "a104" can match "A104".

Does Exists search the dictionary’s values too?
No. It checks keys only. To search values, loop through the keys and compare each associated item, taking care to handle the item type and comparison rules you need.

Can this macro run in Excel for the web or on a Mac?
Excel for the web does not run VBA. The code shown uses a Windows COM object, so it is intended for Excel desktop on Windows. Mac support for that object is not guaranteed; use a compatible alternative if CreateObject is unavailable.

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.