A VBA Dictionary Item is the value associated with a key in a Dictionary object. In Excel for Microsoft 365 on Windows, read it with dict.Item(key) or the shorter dict(key), and assign a value with the same syntax; check dict.Exists(key) first if the key might be missing.
What a Dictionary Item Means
A Dictionary stores information as key-and-item pairs. The key identifies an entry, and the item is the data attached to it. For example, a dictionary might pair an employee ID such as E204 with a name, or a product code such as P17 with a stock count. In those examples, E204 and P17 are keys; the associated names or numbers are items.
The word item can also mean an entry in the dictionary as a whole, so VBA terminology can be confusing. In the Scripting.Dictionary object, the Item property specifically gets or sets the value for a given key. It does not return the key itself. Use the Key property or the Keys method when you need key names.
Dictionary keys are unique: adding an already-used key with Add raises an error. Items do not have to be unique, so several different keys can point to the same value. A dictionary is useful when code needs to retrieve a value by a meaningful identifier instead of repeatedly searching a worksheet range.
How the Item Property Works
The long form is dict.Item(key); VBA also lets you use the default-property shorthand dict(key). For example, if a dictionary contains the key P17 with the number 8, then dict.Item("P17") and dict("P17") both retrieve 8. The shorthand is common, but using Item can make unfamiliar code easier to understand.
To change an existing entry, assign a new value to that key. With the dictionary variable named dict, dict.Item("P17") = 11 changes its item to 11. The assignment form also creates a new key when that key is not already present. This behavior differs from dict.Add, which is intended to add a new key and raises an error if that key already exists.
A key detail is that trying to read a key that is absent can create it with an empty item. That makes an unchecked read more than a harmless lookup: it can change the dictionary’s contents. Use Exists before reading a key that may not be present, especially when dictionary count or later processing depends on the set of keys.
Create a Dictionary and Add Items
On Windows, VBA can create a Scripting.Dictionary with late binding, which avoids setting a reference in the Visual Basic Editor. The following example creates two entries, updates one item, and safely reads a value. Save a copy of the workbook before running a macro; VBA changes cannot be undone with Ctrl+Z.
Sub DictionaryItemExample()
Dim dict As Object
Dim result As Variant
' Create a late-bound Scripting.Dictionary on Windows.
Set dict = CreateObject("Scripting.Dictionary")
' Add a key and its item.
dict.Add "P17", 8
dict.Add "P18", 3
' Replace the item for an existing key.
dict.Item("P17") = 11
' Check before reading a key that might be absent.
If dict.Exists("P17") Then
result = dict.Item("P17")
MsgBox result
End If
End Sub
Here the message box displays 11. Dim dict As Object and CreateObject are the late-bound approach: VBA does not need a compile-time reference to the Scripting Runtime. For early binding, add the Microsoft Scripting Runtime reference in the Visual Basic Editor using Tools > References, then declare the variable as Dim dict As Scripting.Dictionary and create it with Set dict = New Scripting.Dictionary. Early binding provides type information and editor-assisted completion, while late binding can be more convenient for distributing a workbook across Windows computers with different reference setups.
Read Values Without Creating Accidental Keys
Use Exists to distinguish a missing key from a key whose item is legitimately empty or zero. For example, an inventory count of zero is different from a product code that was never loaded. The following pattern avoids both a missing-key error assumption and the accidental creation associated with reading a nonexistent key:
Sub ReadDictionaryItemSafely()
Dim dict As Object
Dim lookupKey As String
Dim itemValue As Variant
' Create a dictionary and one example entry.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "P17", 0
lookupKey = "P17"
' Read only when the key is present.
If dict.Exists(lookupKey) Then
itemValue = dict.Item(lookupKey)
MsgBox "Found item: " & itemValue
Else
MsgBox "Key not found"
End If
End Sub
This example displays Found item: 0, because the key exists even though its item is zero. An item can be a string, number, date, Boolean, Empty value, or—in suitable VBA code—an object reference. Choose a predictable item type for each dictionary’s purpose; mixing unrelated types makes later comparisons and calculations harder to reason about.
Update, Replace, or Rename an Entry
Use assignment to replace an item when a key may already be present. This is useful for totals, latest values, or corrected data. For example, code processing repeated product rows can set the current total under a product key. If the key is absent, assignment creates it; if present, it replaces its item. By contrast, use Add when a duplicate key should be treated as a data problem rather than silently replacing a value.
To change a key rather than its item, use the dictionary’s Key property. The old key must exist, and the new key must not conflict with another key. A rename is not the same as setting Item: changing dict.Item("P17") changes the stored value, while changing the key alters how that entry is looked up. If renaming is unnecessary, keeping identifiers stable and updating only items is usually simpler.
For an object stored as an item, object assignment and retrieval require object-aware VBA syntax. In practical terms, use Set when assigning an object reference and when retrieving that reference into an object variable. For ordinary text and numeric values, do not use Set.
Loop Through Keys and Their Items
To inspect every pair, loop over the array returned by Keys and look up each item. Declare the loop variable as a Variant because the returned keys are held in a Variant array. This example prints each key and its associated item in the Immediate window in the Visual Basic Editor:
Sub ListDictionaryItems()
Dim dict As Object
Dim key As Variant
' Create example entries.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "P17", 11
dict.Add "P18", 3
' Print each key and its item.
For Each key In dict.Keys
Debug.Print key, dict.Item(key)
Next key
End Sub
Dictionary enumeration is useful for processing results, but do not rely on a particular ordering unless your code explicitly sorts the keys. A dictionary is designed for key-based lookup, not as a substitute for a sorted list. If output must appear in a specific order, collect the keys and sort them separately before writing results.
Key Matching and CompareMode
By default, string-key matching is case-sensitive: a key such as North and a key such as north can be treated as different keys. Set CompareMode to vbTextCompare before adding entries if case-insensitive text matching is appropriate. Set it to vbBinaryCompare for binary comparison. The default is binary comparison.
Set the comparison mode immediately after creating the dictionary and before adding any data. Changing it after entries exist can raise an error. Case-insensitive matching can be useful for user-entered names, but it is not automatically correct for every identifier. A part number or code may intentionally distinguish uppercase from lowercase, so decide based on the meaning of the key rather than convenience.
Also normalize keys consistently when data may contain accidental spaces or different formats. For example, a key with a trailing space is not necessarily the same as the otherwise identical key without it. Apply deliberate cleanup when loading keys, and use the same policy for lookups. Do not convert identifiers to numbers if leading zeros are significant: the codes 0012 and 12 may identify different records even though their numeric values match.
Common Errors and Troubleshooting
- Duplicate key error:
Addcannot add a key that already exists. CheckExistsfirst, or use item assignment if replacing the current item is the intended behavior. - Unexpected key appears: reading
dict(key)for a missing key can create an empty entry. CheckExistsbefore reading when a lookup may fail. - Wrong capitalization behavior: inspect
CompareModeand set it before inserting entries. Decide whether keys should be case-sensitive. - Object not available:
CreateObject("Scripting.Dictionary")relies on Windows COM support. It is not a portable Mac Excel solution. - Wrong value type: inspect what was placed in each item. A Variant holding text cannot always be used as a number without conversion and validation.
When code raises an error, also check whether the dictionary was initialized with Set, whether the key variable contains the expected value, and whether a prior lookup changed the dictionary. In the Visual Basic Editor, stepping through the macro and inspecting dict.Count, dict.Keys, and the lookup key can reveal where assumptions diverge from the actual data.
Windows, Mac, and Other Options
In Excel for Microsoft 365 on Windows, Scripting.Dictionary is a common VBA choice for fast lookups and unique keys. Late binding with CreateObject avoids a reference setting, while early binding offers type checking and completion in the editor. Both approaches still depend on the Windows Scripting Runtime component.
Excel for Mac supports VBA, but it does not provide Windows COM automation in the same way. As a result, code that creates a Scripting.Dictionary with CreateObject is not a dependable cross-platform choice. Excel for the web does not run VBA macros at all. If a workbook must work across Windows and Mac, consider a VBA Collection for simpler keyed storage, a custom class or array-based lookup for more control, or a worksheet table when the data should remain visible. Each alternative has different lookup, duplicate-key, and update behavior, so it may require more code than a Dictionary.
For ordinary Excel analysis, worksheet functions or Power Query may be better if the goal is to summarize or join data without writing a macro. A Dictionary is most useful when VBA logic needs repeated in-memory lookups, for example while consolidating many rows. It is not a worksheet cell function and cannot be entered into a cell formula.
Frequently Asked Questions
How do I get an item from a VBA Dictionary?
Use dict.Item(key) or its shorthand dict(key). If the key may be missing, test dict.Exists(key) first so a lookup does not create an unintended empty entry.
How do I change a Dictionary item in VBA?
Assign a value to the key, such as dict.Item("P17") = 11. This replaces the current item if the key exists and creates that key if it does not.
What is the difference between Add and Item assignment?Add expects a new key and reports a duplicate-key error. Assigning through Item can insert a missing key or replace the item for an existing key.
Does reading a missing Dictionary key cause an error?
With Scripting.Dictionary, reading a missing key can create that key with an empty item. Use Exists to check first when the key is not guaranteed to be present.
Can I use Scripting.Dictionary in Excel for Mac?
It is not a reliable cross-platform option because it depends on Windows COM support. For Mac-compatible VBA, consider a Collection, a custom lookup structure, or a worksheet-based method.
Related
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
- Power Query Split Cell Into Rows: Steps and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- 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…
- Refresh Pivot Table Using VBA: Code and TipsTo refresh a PivotTable with VBA in Excel for Microsoft 365 on Windows, call its RefreshTable method; to…
- VBA Range.Delete: Remove Cells, Rows, or ColumnsIn Excel VBA, use the Delete method to remove cells, rows, or columns: for example, Range("B2:C5").Delete Shift:=xlShiftUp deletes…
- Arrays in Excel VBA: How They Work and When to Use ThemAn array in Excel VBA is a variable that holds a group of related values under one name,…
- Excel VBA Zoom: Set and Manage Worksheet MagnificationTo change the zoom of the active Excel worksheet with VBA, set ActiveWindow.Zoom to a percentage from 10…
- VBA Copy and Paste Excel Cells Into the Email BodyYes. In Excel for Microsoft 365 on Windows, VBA can copy a cell range and paste it into…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.