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
.xlsmfile 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 theFnkey. - 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
- Open the VBA Editor and insert a module. In Excel for Windows, press
Alt+F11. In the editor, chooseInsert > Module. If the Developer tab is hidden, show it throughFile > Options > Customize Ribbonand selectDeveloper. Excel for Mac usesOption+F11on many keyboards; the shortcut can vary with keyboard settings, and the Developer tab is managed throughExcel > Settings(orPreferences) >Ribbon & Toolbar. - 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.
- Create the dictionary and add sample pairs. The code below uses late binding with
CreateObject. It does not require you to select a library underTools > References. The comparison mode is set before adding keys so the example treats uppercase and lowercase letters in keys as equivalent. - Check the key with
Exists, then retrieve its value. Put the lookup inside theIfblock. If the key is absent, show a not-found message instead of trying to use a value that is not there. - 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 pressF5, or return to Excel and run it fromDeveloper > 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 aScripting.Dictionary. That may make a later search appear successful even though you never deliberately added the key. CheckExistsfirst whenever absence is possible. - Confusing a key search with a value search.
dict.Exists("A104")asks whetherA104is 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. Callingdict.Addfor a key that already exists raises an error. Check withExistsbefore adding, or assign to an existing item when your intention is to update its value. - Setting
CompareModetoo 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 choosevbTextCompare. - 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
vbTextComparewhen keys should match regardless of letter case; usevbBinaryComparewhen 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
searchKeyandfoundValue. 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
Keyscollection 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
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Access VBA Dictionary: Create and Use Scripting.DictionaryYes. In Access VBA, you can use the Scripting.Dictionary object to store pairs of keys and values, check…
- Tiny Macro Recorder: What TinyTask Can and Can’t DoIf you mean TinyTask, it is a small Windows macro recorder that captures mouse and keyboard actions and…
- VBA Find: Search Excel Ranges and Handle Results SafelyIn Excel VBA, Find is a method of a Range, not a separate object: it searches a specified…
- Excel VBA Find a Value in an Array: Loop, Match, and TipsTo find a value in a VBA array, loop from LBound to UBound and compare each element with…
- VBA Loop Statements: Types, Examples, and When to Use ThemA VBA loop statement repeats a block of code, either for a set number of passes or while…
- Excel VBA If Statements: Syntax, Examples, and TipsIn Excel VBA, an If statement runs code only when a condition is true; use ElseIf for additional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.