To get a value from a VBA Scripting.Dictionary, use its key as the item index: value = dict.Item(key) or the shorter value = dict(key). If the key might be absent, check dict.Exists(key) first; reading a missing item can create that key with an empty value rather than simply reporting “not found.”
What a VBA dictionary does
A dictionary stores pairs: a unique key and the value associated with it. You choose a key that identifies an item, then use that key to retrieve, replace, or test the stored value. For example, a dictionary could map product codes to prices, employee IDs to names, or region names to monthly totals.
In Excel VBA on Windows, the commonly used implementation is Scripting.Dictionary, provided by the Windows scripting runtime. A dictionary is useful when you need direct lookup by an identifier and do not want to search through a worksheet range or a collection of parallel arrays each time. Keys must be unique, but values do not: several keys can point to the same value.
A dictionary is not a worksheet function. You create and use it in VBA code, usually in a standard module or within a procedure. The value can be a string, number, date, Boolean, Variant, or—in suitable code—an object. The retrieval statement is short, but checking whether a key exists and understanding the dictionary’s comparison rules make the difference between a reliable lookup and a hard-to-find bug.
Basic syntax for getting a value
The Item property returns the value associated with the supplied key. VBA also lets you omit the property name and use parentheses after the dictionary variable. These forms perform the same lookup:
result = dict.Item("North")result = dict("North")
Here is a complete example using late binding. It creates the dictionary, adds two values, looks up one key, and displays the result in the Immediate window. In the VBA editor, open that window with Ctrl+G on Windows.
Sub GetDictionaryValue()
' Save a copy of the workbook before running a macro; Ctrl+Z cannot undo it.
Dim dict As Object
Dim searchKey As String
Dim result As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 90
searchKey = "North"
result = dict.Item(searchKey)
Debug.Print result ' Prints 125
End Sub
The result is stored as a Variant so it can hold the value type currently in the dictionary. If you know the value will always be numeric, you can declare a suitable numeric variable instead, but you should ensure the stored value really is numeric before assigning it. Otherwise, a type mismatch or unwanted conversion can occur.
Late binding means the code declares the dictionary as an Object and creates it with CreateObject. It avoids setting a reference manually, which can make a workbook easier to move between Windows computers. The trade-off is that the VBA editor cannot provide the same compile-time member assistance as it can with an early-bound variable.
Check for a key before retrieving it
Use the dictionary’s Exists method when a lookup key may not be present. This is especially important when a miss should leave the dictionary unchanged. The method returns True if the key exists and False otherwise, so you can decide what to do before requesting its value.
Sub GetValueOnlyIfPresent()
' Save a copy of the workbook before running a macro; Ctrl+Z cannot undo it.
Dim dict As Object
Dim searchKey As String
Dim result As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
searchKey = "West"
If dict.Exists(searchKey) Then
result = dict.Item(searchKey)
Debug.Print result
Else
Debug.Print "No value is stored for " & searchKey
End If
End Sub
In this example, “West” is not added as a side effect because the code tests it first. You can replace the message with any suitable fallback: skip a row, assign a default, record an error, or ask the user to correct the key. Avoid treating a blank returned value as proof that a key is missing. A key can exist and intentionally have an empty string, zero, or an Empty value; Exists answers the separate question of whether the key is present.
Why a missing-key lookup can be surprising
One important behavior of Scripting.Dictionary is that requesting an item for a key that does not exist can create that key and leave its item empty. Consequently, this apparently simple line is not a safe “try to find it” test when the key may be absent: result = dict(searchKey). Afterward, the dictionary may contain the key even though no meaningful value was originally added.
This behavior also explains why checking the value itself is not a dependable presence test. If a lookup returns an empty value, that could mean the item is missing and was just created, or that the key was already present with an empty value. Test with Exists before accessing the item whenever the distinction matters. If you deliberately want a get-or-create pattern, make that intent explicit and initialize the item to a known default.
For example, this kind of defaulting logic should first check Exists, then add a value only when necessary. It is generally clearer than relying on the automatic creation behavior, because a reader can see exactly when the dictionary changes. Be particularly cautious in loops over worksheet data: an unnoticed missing-key access can gradually fill the dictionary with unintended entries and make later counts or iterations misleading.
Use a lookup in a practical worksheet task
A common pattern is to build a dictionary from a lookup table and then use it to find values for another list. Suppose column A contains product codes and column B contains prices on a reference sheet, while another procedure needs the price for a particular code. The dictionary makes the relationship explicit: the code is the key, and the price is the item. The snippet below demonstrates the retrieval step; it assumes the dictionary has already been populated with those code-price pairs.
Sub LookUpProductPrice()
' Save a copy of the workbook before running a macro; Ctrl+Z cannot undo it.
Dim prices As Object
Dim productCode As String
Dim price As Variant
Set prices = CreateObject("Scripting.Dictionary")
prices.Add "P-104", 19.95
prices.Add "P-208", 42.5
productCode = "P-208"
If prices.Exists(productCode) Then
price = prices.Item(productCode)
Debug.Print Format$(price, "0.00") ' Prints 42.50
Else
Debug.Print "Product code not found"
End If
End Sub
In a larger macro, you would typically load many table rows into the dictionary once, then perform lookups as you process the other data. That can be more convenient than repeatedly scanning the source range in VBA. It does not mean a dictionary is always faster or better: for a small one-off lookup, a worksheet formula or a direct range search may be simpler. Also decide what duplicate codes mean before loading data. Calling Add for a duplicate key raises an error, while assigning through the item property can add a new key or replace the value for an existing key.
Key matching, capitalization, and data types
By default, dictionary string keys are compared using binary comparison. In practical terms, strings that differ only in letter case can be treated as different keys: for example, “North” and “north” may identify separate entries. If your data should match without regard to case, set CompareMode to vbTextCompare before adding the first key.
Choose the comparison mode when creating and preparing the dictionary, not after it already contains entries. Changing CompareMode after adding keys can raise an error. The mode also affects whether a later Exists check or lookup recognizes a differently capitalized string. Keep the same normalization policy for keys throughout the procedure; for instance, do not inconsistently trim spaces or change capitalization only on one side of a lookup.
Keys that look alike may also have different underlying types or characters. A numeric key such as the number 123 is not necessarily the same thing as a text key such as "123". A trailing space, a nonbreaking space copied from a web page, or a leading zero in a code can likewise prevent a match. If identifiers are meant to preserve formatting—postal codes, SKU codes, or account numbers, for example—store and look them up consistently as text rather than converting them casually to numbers.
Getting values of different kinds
For ordinary scalar values, assign the item to a variable of a compatible type. A Variant is often a practical choice if the dictionary can contain different kinds of values or if the data type is not guaranteed. A string value can be assigned to a String variable, and a numeric value to an appropriate numeric variable, once the code has established that the value is of the expected kind.
When dictionary items are objects, retrieval needs object assignment. For example, if an item stores a worksheet or a custom object, use Set retrievedObject = dict.Item(key), with the destination declared as an Object or the relevant object type. Omitting Set asks VBA to assign a value rather than an object reference and can cause an error. Likewise, storing an object generally uses Set dict.Item(key) = someObject. Keep object-valued and scalar-valued code distinct so the assignment syntax is clear.
You can also retrieve a key’s value for use in an expression, but avoid repeating the lookup if you need to validate or use the result several times. Store it in a variable after checking the key. That makes the control flow easier to read and gives you one place to inspect the value when debugging.
Common errors and ways to prevent them
- Object variable not set: The dictionary was declared but never initialized. Use
Set dict = CreateObject("Scripting.Dictionary")before callingAdd,Exists, orItem. - Key not found or an unexpected blank: Check
Existsbefore retrieval and decide deliberately what a missing key should mean. - Duplicate-key error:
Addrequires a key that is not already present. CheckExistsfirst if duplicates should be ignored, or assign throughdict.Item(key) = valueif replacing an existing value is intended. - Unexpected case mismatch: Set
CompareModebefore loading entries, and make sure the chosen text comparison matches the data rules. - Type mismatch: Confirm whether the item is a scalar or an object, and use a compatible destination type and assignment form.
- Different-looking keys fail to match: Inspect spaces, number-versus-text differences, leading zeros, and characters copied from external sources.
During debugging, the Locals and Watch windows in the VBA editor can help inspect variables and values. You can also print a key’s existence and the dictionary’s Count to the Immediate window. If the count changes unexpectedly after a lookup, review every item access for a missing-key read that should have been guarded with Exists.
References, Windows, and alternatives
On Windows, you can use late binding as shown above, or early binding by adding a reference to the Microsoft Scripting Runtime in the VBA editor through Tools > References. With early binding, declare the variable as Scripting.Dictionary and instantiate it with New Scripting.Dictionary. The editor can then provide member suggestions and check member names during compilation. The reference must be available on other computers that run the workbook, so late binding is often chosen for portability.
Excel for Mac differs because Scripting.Dictionary is a Windows scripting-runtime COM component, not a built-in, cross-platform VBA collection. Do not assume that Windows CreateObject("Scripting.Dictionary") code will work on a Mac. If the workbook must run on both platforms, consider a VBA Collection, arrays with a search routine, or another deliberately cross-platform design, and verify the chosen approach in the target Excel versions. A Collection can use keyed items, but it does not provide the same Exists method; checking for a missing key therefore needs different error-handling logic.
For users who do not need to write a macro, an Excel lookup formula may be a better alternative for a worksheet-based task. For example, XLOOKUP can retrieve a matching value in Microsoft 365 or Excel 2021 and later. A dictionary is more appropriate when VBA needs to perform many keyed operations in memory, store non-worksheet objects, or build logic around keys. Choose based on where the work belongs, whether the workbook must support Mac, and how much error handling the task requires.
Frequently Asked Questions
How do I get a value from a VBA dictionary by key?
Use value = dict.Item(key) or the shorthand value = dict(key). If the key might not exist, call dict.Exists(key) first.
How do I check whether a key exists in a VBA dictionary?
Use If dict.Exists(key) Then. This tests for the key without retrieving its item, so it is the safest way to distinguish an absent key from a present key with an empty value.
What happens if I get a value for a missing key?
With Scripting.Dictionary, requesting a missing item can create that key with an empty item. Check Exists first if you do not want a lookup to change the dictionary.
Can a VBA dictionary ignore uppercase and lowercase differences?
Yes. Set dict.CompareMode = vbTextCompare before adding keys. The default binary comparison can treat differently capitalized strings as distinct keys.
Can I use Scripting.Dictionary in Excel for Mac?
It is a Windows scripting-runtime component and is not a dependable cross-platform choice for Mac VBA. For a workbook that must run on Mac, choose and verify a cross-platform alternative such as a Collection or an array-based lookup.
Should I use Item or the parentheses shortcut?
Both forms retrieve through the same Item property. dict.Item(key) is explicit and often easier for a beginner to recognize; dict(key) is shorter. Use one style consistently, and guard potentially missing keys either way.
Related
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Copy Paste Special: Values, Formats, and MoreIn Excel VBA, copy a range and call PasteSpecial on the destination, specifying what to paste. For example,…
- How to Print a Range in VBA: Excel Setup and ExamplesTo print a specific Excel range with VBA, assign it to a Range variable and call its PrintOut…
- Conditional Formatting VBA: Rules, Examples, and TipsIn Excel for Microsoft 365 on Windows, VBA creates conditional formatting by adding a rule to a range’s…
- VBA Position UserForm on Screen: Centering and ControlTo position a VBA UserForm, set its StartUpPosition property to 1 - CenterOwner or 2 - CenterScreen for…
- VBA Pivot Table Update: Refresh Tables and ReportsTo update a PivotTable with VBA, call its RefreshTable method; to refresh every PivotTable and external data range…
- VBA Close Workbook: Save, Close, and Avoid Common ErrorsTo close a workbook with VBA, call its Close method: use Workbooks("Report.xlsx").Close SaveChanges:=True for a named workbook, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.