In VBA, read a Scripting.Dictionary pair by using its key to retrieve the value, as in dict(key), or loop through dict.Keys and read dict.Item(key) for each key. If a key might be missing, call dict.Exists(key) first; a lookup through the Item property can add a missing key to the dictionary.
What a VBA dictionary stores
A dictionary is a collection of unique keys, with one associated item, or value, for each key. For example, a key might be a product code and its item the product name, or a region name and its sales total. Unlike a worksheet range, a dictionary is held in memory while the macro runs, so VBA can use it to look up a value without repeatedly searching cells.
In Excel VBA on Windows, the commonly used dictionary is Scripting.Dictionary. It has a Count property for the number of pairs and methods such as Add, Exists, and Remove. A dictionary’s keys are unique: adding an existing key with Add raises an error. Values do not have to be unique, and they can be different Variant-compatible types.
A dictionary is useful when you need a direct lookup or need to group data by a distinct label. It is not a worksheet function or a range: reading a dictionary pair means asking the object for the item associated with a key.
Read one value from a known key
When you know the key, use the dictionary’s Item property. The shorter bracket-like syntax, dict(key), is also valid VBA and refers to the same item. For example, if the dictionary contains the key "North" with a value of 125, then dict("North") returns 125.
Here is a safe lookup pattern. It avoids treating an absent key as though it had a normal value:
Sub ReadOneDictionaryValue()
Dim dict As Object
Dim lookupKey As String
Dim result As Variant
' Create a dictionary and add a sample pair.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
lookupKey = "North"
If dict.Exists(lookupKey) Then
result = dict.Item(lookupKey)
Debug.Print lookupKey & " = " & result
Else
Debug.Print "Key not found: " & lookupKey
End If
End Sub
In this example, the Immediate window displays North = 125. Open the VBA editor’s Immediate window with Ctrl+G on Windows. This sample reads and prints a value; it does not write to a worksheet.
Loop through every key-value pair
When you do not know the keys in advance, iterate through the keys and use each one to retrieve its item. The following example prints every pair in the Immediate window:
Sub ReadAllDictionaryPairs()
Dim dict As Object
Dim key As Variant
' Create sample data for the demonstration.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 98
dict.Add "West", 143
For Each key In dict.Keys
Debug.Print CStr(key) & " = " & CStr(dict.Item(key))
Next key
End Sub
The output contains one line per pair, such as North = 125. The loop variable is declared as a Variant because dictionary keys can be different data types. The example converts the key and value to text for display; if your values are objects or special values, handle them according to their actual type rather than assuming that every value can be converted with CStr.
You can also write For Each key In dict to enumerate dictionary keys. Using dict.Keys makes the intent explicit, especially for readers who are learning the object model.
Read keys and items as arrays
The Keys and Items properties each return an array containing the dictionary’s keys or values. This is useful when you want to process entries by position, or pass a set of values to another routine. The arrays correspond by index: the key at a given position matches the item at that same position.
Sub ReadDictionaryArrays()
Dim dict As Object
Dim keys As Variant
Dim items As Variant
Dim i As Long
' Add pairs, then retrieve both arrays.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 98
If dict.Count > 0 Then
keys = dict.Keys
items = dict.Items
For i = LBound(keys) To UBound(keys)
Debug.Print CStr(keys(i)) & " = " & CStr(items(i))
Next i
End If
End Sub
The count check matters. An empty dictionary has no key or item elements to index, so do not assume that LBound and UBound can be used for a useful loop when there are no entries. Also, do not assume the key array is sorted alphabetically. If order matters, sort the copied array or arrange the output separately.
Creating the dictionary in Excel VBA
The examples use late binding: CreateObject("Scripting.Dictionary") creates the object at run time, and the variable is declared As Object. This avoids setting a reference in the VBA project and is often convenient for a macro shared between Windows computers.
On Windows, another option is early binding. In the VBA editor, choose Tools > References, select Microsoft Scripting Runtime, and declare the variable as Scripting.Dictionary. Early binding can provide editor autocomplete and compile-time checking, but a workbook can fail to compile on a computer where that reference is unavailable or broken. Late binding avoids that particular project reference, though the object still needs to be available in the environment.
These examples target Excel for Microsoft 365 on Windows. CreateObject uses Windows COM automation and is not a portable assumption for Excel for Mac. Availability of the Scripting Runtime can differ by Mac version and setup; if a workbook must run on Mac, verify the object on the target installation or choose a cross-platform alternative such as a Collection or an array-based lookup.
Check for missing keys and understand empty values
A frequent mistake is to use dict(key) as a test for whether a key exists. The Item property is for retrieving an item, not for checking membership. When an Item lookup refers to a key that is not present, the Scripting.Dictionary can create that key with an empty item. This can change the dictionary’s contents unexpectedly.
Use dict.Exists(key) to test membership before reading an optional key. This also separates two different cases: a key that is absent, and a key that exists but has an empty value. A value of zero, an empty string, or Empty does not necessarily mean that the key is missing. If those values have meaning in your data, check existence rather than trying to infer it from the returned value.
For example, when processing a list of customer IDs, an ID not present in the dictionary may need a “not found” message, while an ID present with a blank value may mean the customer record exists but has no assigned category. Treating those cases separately prevents quiet data errors.
Key comparison, duplicates, and data types
By default, dictionary key comparisons are case-sensitive binary comparisons. That means text keys such as "North" and "north" can be treated as different keys. If you want case-insensitive text matching, set CompareMode to vbTextCompare before adding any keys. Changing the comparison mode after the dictionary contains data can raise an error.
Sub SetDictionaryComparisonMode()
Dim dict As Object
' Set comparison rules before adding keys.
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict.Add "North", 125
Debug.Print dict.Exists("north")
End Sub
With this setting, the final line prints True. Choose the comparison rule to match the data, rather than changing the text in an ad hoc way during each lookup. If the dictionary already has entries, create a new dictionary with the intended mode and add the entries again.
Keys must be unique under the chosen comparison rules. Use Exists before Add if duplicates are possible, or decide how duplicates should be handled: skip them, replace the existing item, or combine the values. The right approach depends on the meaning of the data. For example, duplicate transaction IDs may signal a problem, while repeated department names may be expected when totaling rows.
Write pairs to a worksheet
A dictionary can be used to build a two-column output, with keys in one column and matching items in the next. First gather the pairs in an array or write them one at a time to cells. For larger outputs, preparing a two-dimensional array and assigning it to a range in one operation is generally more efficient than making a separate worksheet write for every pair. The dictionary itself does not automatically display or export its contents.
Before writing results, decide whether the output should preserve the dictionary’s iteration order or be sorted. A dictionary is designed for key-based lookup, not as a sorting tool. If the output is for a report, sort the keys or output rows explicitly so the presentation order is deliberate. Also decide what to do with values such as dates, errors, or objects; a simple text-printing example is not a complete worksheet-export routine for every data type.
Macros that write to a workbook cannot be undone with Ctrl+Z. Save a copy of the workbook before running a macro that changes cells, and check the destination range so existing data is not overwritten. The examples above only print to the Immediate window, but a worksheet-writing version should be tested on a copy first.
Dictionary compared with other VBA options
A dictionary is a strong choice when you need fast lookups by unique keys, need to test whether a key exists, or want to accumulate information by category. For instance, a macro can read rows of sales data and use each region name as a key to maintain a running total. A Collection can also hold keyed items, but it does not provide the same built-in Exists method, so handling missing keys is less direct.
For simple worksheet tasks, a worksheet formula or a direct range operation may be easier to maintain than VBA. For data that must remain visible, sortable, and editable by users, a table is often a better storage location than an in-memory object. An array is a useful alternative when you need to process a fixed batch of values or when portability matters more than convenient key lookup. Choose a dictionary for the lookup pattern, not merely because the data happens to consist of pairs.
Frequently Asked Questions
How do I get a value from a VBA dictionary?
Use dict.Item(key) or its shorter equivalent, dict(key). If the key might not exist, check dict.Exists(key) first.
How do I print all keys and values in a VBA dictionary?
Loop through dict.Keys with a Variant variable, then print each key and dict.Item(key). The Immediate window is a convenient place to see output during debugging.
Does a VBA dictionary have a Values property?
The Scripting.Dictionary exposes its values through the Items property, which returns an array. For a key-value loop, use dict.Keys and retrieve each matching value with dict.Item(key).
Why does my dictionary lookup add a key?
Reading a nonexistent key through the Item property can create that key with an empty item. Use Exists to check membership before retrieving optional keys.
Can I use Scripting.Dictionary in Excel for Mac?
Do not assume Windows COM-based creation with CreateObject("Scripting.Dictionary") will work on Mac. Check the target Excel installation, or use a Collection or array-based design if the macro needs to be portable.
For a basic read, the essential pattern is simple: identify a key, check that it exists when necessary, and retrieve its item. For a full readout, loop over the keys or use the paired arrays returned by Keys and Items.
Related
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to List All Files in a Folder Using Excel VBATo list files directly inside a folder in Excel for Microsoft 365 on Windows, run a VBA macro…
- Delete a Specific Row With a VBA MacroYes. In Excel for Microsoft 365 on Windows, a VBA macro can delete a specific worksheet row with…
- Auto Macro Recorder in Excel: How to Record and Use ItExcel’s macro recorder captures supported actions as VBA code, letting you replay a routine instead of repeating it…
- Call a Macro from an Excel UserForm ButtonYes. In Excel for Microsoft 365 on Windows, put a call to a Public Sub in the UserForm…
- Microsoft VBA for Excel: What It Is and How It WorksMicrosoft VBA for Excel is the programming language built into desktop Excel for automating repetitive tasks, creating custom…
- Make a Plot with a Dictionary in VBA: Excel GuideYes. In Excel for Microsoft 365 on Windows, use a VBA Scripting.Dictionary to group records by category, write…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.