To check whether a key is in a VBA Dictionary, use its Exists method inside an If statement: If dict.Exists(key) Then. It returns True when the key is present and False when it is not, letting your macro safely decide whether to read, update, or add an item.
What “If” Means with a VBA Dictionary
A VBA Dictionary stores pairs: a unique key and an associated item. For example, a customer number can be the key and a customer name the item. The If statement is ordinary VBA conditional logic; Exists is the Dictionary method that answers the membership question.
The basic pattern is:
If dict.Exists(searchKey) Then
' The key is already in the dictionary.
Else
' The key is not in the dictionary.
End If
This is different from an Excel worksheet IF formula. It is VBA code that runs in a procedure or function, and the dictionary must already have been created and assigned to dict. The method checks keys, not item values: if a value appears somewhere in the dictionary but is stored under a different key, Exists does not find it.
Create a Dictionary and Check a Key
In Excel for Microsoft 365 on Windows, a convenient approach is late binding. It creates the Dictionary at runtime, so you do not need to set a reference in the VBA editor. The following complete example looks for an invoice key and reads its item only after confirming that the key exists.
Save a copy of your workbook before running any macro. VBA changes cannot be undone with Ctrl+Z, even if a particular example only displays a message.
Sub CheckInvoiceKey()
Dim dict As Object
Dim lookupKey As String
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "INV-104", "Paid"
lookupKey = "INV-104"
If dict.Exists(lookupKey) Then
MsgBox "Status: " & CStr(dict.Item(lookupKey))
Else
MsgBox "Invoice key was not found."
End If
End Sub
Here the key is INV-104 and its item is Paid, so the first branch displays “Status: Paid.” If you change lookupKey to a key that was never added, the Else branch runs instead. In Excel for Windows, open the VBA editor with Alt+F11. On a Mac, the editor shortcut is commonly Option+F11, sometimes with Fn as well, depending on the keyboard; the Windows scripting component used by this example may not be available on every Mac setup.
Why Exists Is Safer Than Reading First
A common mistake is to try to read an item before checking whether its key is present. A Dictionary is not a worksheet range, and looking up an absent key is not a dependable way to test membership. In particular, assigning an item through a missing key can create a new entry, which may quietly change the dictionary rather than answer the question you intended to ask.
Use Exists first when the next action depends on membership:
- If the key exists, retrieve or update its item.
- If the key does not exist, add it, skip the record, or report the missing key.
- If either outcome is an error for your task, handle each branch explicitly rather than relying on a failed lookup.
For example, If dict.Exists(k) Then result = dict.Item(k) avoids trying to read the item when k is absent. Use Item or the shorter parenthesized default property form only after the membership check when you need predictable lookup behavior.
Add New Keys or Update Existing Items
One of the most useful patterns is to treat an existing key differently from a new one. The Dictionary’s Add method is for inserting a new key-item pair; attempting to add a duplicate key raises an error. If you want to update an item, first check for the key and then assign a new item.
If dict.Exists(lookupKey) Then
dict.Item(lookupKey) = "Reviewed"
Else
dict.Add lookupKey, "New"
End If
This approach is also useful for counting or totaling records. For instance, with category names in column A and numeric amounts in column B of a worksheet named Data, the following macro keeps a running total for each category. It assumes the first row contains headings and the remaining category and amount cells contain usable data.
Sub TotalByCategory()
Dim totals As Object
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
Dim category As String
Dim amount As Double
Set ws = ThisWorkbook.Worksheets("Data")
Set totals = CreateObject("Scripting.Dictionary")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For rowNum = 2 To lastRow
category = CStr(ws.Cells(rowNum, 1).Value2)
amount = CDbl(ws.Cells(rowNum, 2).Value2)
If totals.Exists(category) Then
totals.Item(category) = CDbl(totals.Item(category)) + amount
Else
totals.Add category, amount
End If
Next rowNum
End Sub
For example, if “Travel” appears on three rows with amounts 25, 40, and 15, its stored total is 80. This sample builds the totals in memory; it does not write them back to the worksheet. Check or clean the input data first if cells might be blank, contain errors, or hold non-numeric amounts, because converting such values with CDbl can cause a runtime error.
Key Comparison and Text Case
Dictionary key comparison can matter when keys are text. By default, text keys are compared using binary comparison, so strings with different capitalization can be treated as different keys. If you want ordinary text comparisons that ignore case, set CompareMode to vbTextCompare immediately after creating the Dictionary and before adding any entries.
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict.Add "North", 1
If dict.Exists("NORTH") Then
' This branch runs with text comparison.
End If
Set the comparison mode early. Changing CompareMode after the Dictionary contains data can raise an error. For identifiers where capitalization is meaningful, retain binary comparison instead. The key’s type and the comparison rules should match the data you consider equivalent; normalizing text yourself, such as trimming accidental spaces, may also be necessary because “North” and “North ” are not the same string.
Common Errors and How to Avoid Them
Several issues can make a correct-looking If check fail or produce an unexpected result. Check these points when debugging:
- The object was not set: Create it with
Set dict = CreateObject("Scripting.Dictionary")or an early-bound declaration. Otherwise, callingExistson the variable causes an object-related error. - The key has a different value than expected: Leading or trailing spaces, different punctuation, or a different data type can make a lookup miss. Inspect the actual key and lookup value, not just how they appear in a cell.
- A duplicate was added:
dict.Add key, itemraises an error if that key already exists under the Dictionary’s comparison rules. UseExiststo choose between adding and updating. - Case rules do not match your intent: Set
CompareModebefore adding entries if case-insensitive text matching is required. - The item is not the key:
Existschecks the key collection. To search items instead, you need a different approach, such as looping through the Dictionary’s items.
When a lookup unexpectedly returns false, it can help to inspect the number of entries with dict.Count and review the keys returned by dict.Keys. Those checks can distinguish an empty Dictionary from a mismatch in spelling, spaces, or case.
Early Binding, Late Binding, and Alternatives
Late binding, used in the examples above, declares the object as Object and creates it with CreateObject. It avoids a project reference and is often convenient for distributing a Windows workbook. Early binding instead declares a variable as Scripting.Dictionary and requires the Microsoft Scripting Runtime reference to be enabled in the VBA project. Early binding can provide editor assistance and type checking, but a missing reference can cause problems when a project moves between systems.
The Dictionary is particularly handy for fast key-based lookups, checking whether a key has been seen, and storing one item per unique key. A VBA Collection can also store keyed items, but it does not provide the same built-in Exists method. Worksheet lookup functions are often more suitable when results need to remain visible and update with worksheet data; a Dictionary is an in-memory VBA object suited to code-driven work.
On Mac, Excel supports VBA, but Windows COM automation components are not universally available. A call to CreateObject("Scripting.Dictionary") can fail on a Mac installation. If your workbook must run on both platforms, test on the target Mac version and consider a cross-platform design, such as a Collection with controlled error handling or worksheet-based lookup logic, rather than assuming the Windows object is present.
Frequently Asked Questions
What is the syntax for checking a VBA Dictionary key?
Use If dict.Exists(key) Then, followed by the code for a found key, and optionally an Else branch for a missing key. Exists returns a Boolean value.
Does Dictionary.Exists check the item or the key?
It checks whether the specified key is present. It does not search the item values. To find a matching item, loop through the Dictionary’s items or use a data structure designed for that lookup.
How can I make a Dictionary lookup ignore capitalization?
Set dict.CompareMode = vbTextCompare after creating the object and before adding any keys. This makes text key comparisons case-insensitive.
Can I use Add without checking Exists first?
Yes, if you know the key is new. Adding a duplicate key raises an error, so use Exists first when duplicates are possible or when you need to update an existing item.
Why does CreateObject fail on my Mac?
Scripting.Dictionary relies on a Windows scripting component that may not be available in a Mac installation of Excel. Test the workbook on the intended platform and use a compatible alternative if it must run on both Windows and Mac.
Related
- Paste Dates as Text in Excel Without Changing Them
- How to Paste Data Into Filtered Cells in Excel Safely
- How to Paste as Text in Excel Without Source Formatting
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Delete First Row: Code and Safe OptionsTo delete row 1 of a worksheet with VBA, use ws.Rows(1).Delete, where ws is the worksheet you intend…
- Excel VBA Loop Over an Array: Examples and PatternsTo loop over a VBA array in Excel, use For Each when you need to process every item,…
- Clear Function in Excel VBA: Add a Button to Clear CellsTo clear cells with a button in Excel VBA, use the ClearContents method in a macro, then assign…
- VBA AutoFilter Sort: Filter and Sort Rows in ExcelTo filter and sort rows with VBA, apply an AutoFilter to the data range, then use the worksheet’s…
- Excel VBA Sleep: Pause a Macro SafelyTo pause an Excel VBA macro on Windows, declare the Windows API function Sleep in a standard VBA…
- VBA Loop Through a PivotTable: Examples and TipsTo loop through a PivotTable in Excel VBA, assign it to a PivotTable variable and use a For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.