In VBA, use Set to assign a Dictionary object to an object variable, for example Set dict = CreateObject("Scripting.Dictionary"). Add ordinary data with dict.Add key, value or assign it with dict(key) = value; Set is not needed for strings or numbers.
What Set means in a VBA Dictionary
Set is VBA’s assignment statement for object references. A Dictionary is itself an object, so its variable must be assigned with Set. Without it, VBA will generally report an object-related error when you try to assign the result of CreateObject or New.
A Dictionary stores pairs: a unique key and an associated item. For example, a customer ID can be the key and a name, number, or object can be the item. The syntax depends on what you are assigning:
Set dict = ...assigns a Dictionary object to the variabledict.dict.Add "A17", "Mina"adds a key and a string item.dict("A17") = "Mina Patel"assigns or replaces a simple item.Set savedObject = dict("A17")assigns an object item retrieved from the Dictionary.
This distinction is the source of many “VBA Dictionary Set” questions: use Set for object references, not for every operation involving a Dictionary.
Create a Dictionary with late binding
Late binding creates the Dictionary at run time, without requiring a reference to the Microsoft Scripting Runtime in the VBA project. It is a convenient choice for a small macro that needs to work across Windows computers where the reference might not be configured.
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z; on Mac, Command+Z does not undo macro changes either.
Option Explicit
Sub CreateDictionaryLateBound()
Dim dict As Object
' Create a Scripting.Dictionary at run time.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A17", "Mina"
Debug.Print dict("A17")
End Sub
The variable is declared As Object, and Set assigns the object returned by CreateObject. The final line prints the item associated with key A17 in the Immediate window. In the Visual Basic Editor, open that window from View > Immediate Window.
A Dictionary cannot contain the same key twice. If the key is already present, Add raises an error. You can test first with Exists, or assign a new scalar item using the Dictionary’s default Item property: dict("A17") = "Mina Patel".
Use early binding when you want type information
Early binding declares the variable as Scripting.Dictionary. The editor can then provide member names and type checking, which can make code easier to write and maintain. On Windows Excel, enable the reference in the Visual Basic Editor using Tools > References, then select Microsoft Scripting Runtime. The exact reference list can vary by Office installation.
Option Explicit
Sub CreateDictionaryEarlyBound()
Dim dict As Scripting.Dictionary
' Create a Dictionary using the selected reference.
Set dict = New Scripting.Dictionary
dict.Add "A17", "Mina"
Debug.Print dict.Count
End Sub
Here, Set is still required because dict is an object variable. The difference is that the type is known when the project is compiled rather than being resolved from the generic Object type at run time. If you share a workbook, remember that another Windows user may need the same reference enabled; a missing reference can cause a compile error.
Add, update, and retrieve ordinary items
For strings, dates, and numbers, the normal workflow is to add a key-value pair, check whether a key exists when that matters, and then read or update the item. This example uses late binding so it does not depend on the early-binding reference:
Option Explicit
Sub AddAndUpdateDictionaryItems()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 90
If dict.Exists("North") Then
dict("North") = 140
End If
Debug.Print dict("North")
End Sub
The output is 140. The assignment to dict("North") replaces the existing item; it does not add another copy of the key. If you instead call Add "North", 140 after the first addition, VBA raises a duplicate-key error. For code that might encounter existing keys, choose deliberately between updating and adding rather than relying on an error to decide.
Exists checks for a key and returns True or False. It is often clearer than trying to infer whether a missing item returned an empty value, because an empty string, zero, or an uninitialized item may also be a legitimate value in your data.
Store and retrieve object items
A Dictionary can hold object references as items as well as simple values. For example, you might associate a short label with a worksheet. You still use Set when assigning the retrieved worksheet to a worksheet variable.
Option Explicit
Sub StoreWorksheetInDictionary()
Dim dict As Object
Dim sourceSheet As Worksheet
Dim savedSheet As Worksheet
Set dict = CreateObject("Scripting.Dictionary")
Set sourceSheet = ThisWorkbook.Worksheets("Data")
' Add the worksheet object as the item.
dict.Add "source", sourceSheet
' Retrieve the object reference with Set.
Set savedSheet = dict("source")
Debug.Print savedSheet.Name
End Sub
This assumes that the workbook contains a worksheet named Data. The Dictionary holds a reference to that worksheet; it does not make a copy of the sheet. If you need to replace an object item, a straightforward approach is to remove the old key and add the new object. Keep the key-existence check in mind before calling Remove.
Choose key comparison rules before adding keys
By default, a Dictionary compares string keys using binary comparison, so keys such as "North" and "north" are treated as different. To make text keys case-insensitive, set CompareMode before adding any items:
Option Explicit
Sub SetDictionaryCompareMode()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict.Add "North", 125
Debug.Print dict.Exists("north")
End Sub
The printed result is True. If you try to change CompareMode after the Dictionary already contains keys, VBA raises an error. Decide whether your application needs case-sensitive or case-insensitive keys and set the mode immediately after creating the Dictionary.
Case-insensitive comparison also means keys that differ only by letter case cannot be used as separate entries. That can matter when importing identifiers from data sources whose rules are case-sensitive. Test the comparison behavior with representative keys before building a larger process around it.
Check keys and loop through entries
Count reports the number of key-item pairs. Use Keys to obtain the keys and loop over them when you need to process every entry. Declare the loop variable as a Variant, since the returned keys are provided as an array of Variants.
Option Explicit
Sub PrintDictionaryEntries()
Dim dict As Object
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 90
For Each key In dict.Keys
Debug.Print key, dict(key)
Next key
End Sub
This prints each key and its item in the Immediate window. If you need to remove every entry, use dict.RemoveAll. Avoid removing entries from a Dictionary while looping over its keys unless you have designed the loop for that change. A safer pattern is to first capture the keys, then remove selected entries in a separate pass, or build a new Dictionary containing the entries you want to keep.
Common errors and practical fixes
Several common problems come from mixing up object assignment and item assignment, or from assuming that a key is already present. Use these checks when troubleshooting:
- Object variable not set: Confirm that the Dictionary was initialized with
Set dict = CreateObject(...)orSet dict = New Scripting.Dictionarybefore you use it. - Duplicate key error: Check
dict.Exists(key)beforeAdd, or update the existing item instead. - Missing reference or compile error: If you use
Scripting.Dictionaryin a declaration, check the Microsoft Scripting Runtime reference. Alternatively, use late binding withAs Object. - Unexpected capitalization behavior: Set
CompareModebefore adding keys and confirm whether binary or text comparison is appropriate. - Wrong use of Set: Use
Setfor the Dictionary variable and for object variables receiving object items. Do not use it to assign a number or text item.
Also keep in mind that a missing key and a key whose item is blank are different situations. Call Exists before retrieving a key when absence is possible. That makes the intended behavior explicit and helps prevent errors from appearing only when the macro encounters an unusual row of data.
Windows, Mac, and alternatives
Scripting.Dictionary is a Windows COM component and is commonly used with Excel for Microsoft 365 on Windows. Excel for Mac does not provide the same Windows Scripting Runtime in the same way, so code that depends on CreateObject("Scripting.Dictionary") or the Microsoft Scripting Runtime reference may not be portable to Mac. Do not assume that changing from early binding to late binding makes a Windows-only component available on Mac.
If a workbook must run on both platforms, consider whether a built-in VBA Collection, a worksheet range, or parallel arrays are sufficient. A Collection stores keyed items but does not provide the Dictionary’s direct Exists method; you may need error handling or a separate lookup routine. Arrays can be fast for predictable data, but searching them requires your own logic. For cross-platform work, test the chosen design in the actual Excel versions and operating systems your readers use.
Frequently Asked Questions
Do I need Set when creating a VBA Dictionary?
Yes. Assign the Dictionary object to its variable with Set, such as Set dict = CreateObject("Scripting.Dictionary"). You do not use Set to assign ordinary string or numeric items.
How do I add an item to a VBA Dictionary?
Use dict.Add "key", value to add a new key and item. If the key might already exist, use Exists to check first, or assign a replacement item with dict("key") = value.
How do I change a Dictionary value?
For a scalar item, assign the new value with dict("key") = newValue. That creates the item if the key is absent or replaces its item if it is present, so check Exists if those cases need different handling.
How do I get an object stored in a Dictionary?
Assign the retrieved object to an appropriately typed object variable with Set, for example Set ws = dict("source"). The Dictionary stores a reference, not a copy of the object.
Why does Scripting.Dictionary fail on my Mac?
The Scripting Runtime is a Windows COM component and is not a dependable cross-platform choice for Excel VBA. For Mac-compatible workbooks, consider a Collection, arrays, or a platform-neutral lookup design, and test the macro on Mac Excel before distributing it.
Related
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Google Sheets Macros: How They Work and What to KnowYes. Google Sheets supports macros: you can record repeated actions and run them again, or edit the generated…
- VBA Hide UserForm: Methods, Code, and When to Use ThemTo hide an Excel VBA UserForm without unloading it, call Me.Hide from code inside the form. The form…
- Unload UserForm VBA: Close Forms and Reset State SafelyIn VBA, use Unload UserForm1 to close and unload a form, or use Unload Me from code inside…
- Excel VBA FindNext: How to Find the Next MatchIn Excel VBA, use Range.FindNext to move from one match to the next after starting a search with…
- Print a VBA Dictionary: Immediate Window and WorksheetTo print a VBA dictionary for debugging, loop through its keys and items and send each pair to…
- VBA Code to Disable Copy and Paste in ExcelTo block the usual copy-and-paste keyboard shortcuts in Excel for Microsoft 365 on Windows, use VBA’s Application.OnKey method…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.