To declare a VBA Dictionary in Excel for Microsoft 365 on Windows, use Dim dict As Object followed by Set dict = CreateObject("Scripting.Dictionary"); this late-bound form does not require adding a reference. A Dictionary stores unique keys with associated values, making it useful for fast lookups, counting, and removing duplicate entries.
What a VBA Dictionary Is
A Dictionary is a key-and-value collection provided by the Microsoft Scripting Runtime on Windows. Each entry pairs a key—such as a product code, customer name, or worksheet row identifier—with a value, such as a description, total, or object. You retrieve a value by its key rather than by its position in a numbered list.
For example, a dictionary could map each department name to its sales total. When a department appears again, code can find the existing key and update its total instead of searching through a separate list. Dictionary keys are unique: adding the same key a second time raises an error unless the code checks for it or updates the existing item.
VBA does not have a dedicated Dictionary keyword or built-in declaration type in the way it has types such as Collection. On Windows, the commonly used Dictionary comes from the Scripting Runtime. The declaration choice determines whether VBA checks that library at compile time or finds it at run time.
Choose Late Binding or Early Binding
There are two common ways to declare and create a Dictionary in Windows VBA:
- Late binding: Declare the variable as
Objectand create it withCreateObject. This avoids setting a project reference and is usually easier to share between Windows computers. - Early binding: Declare it as
Scripting.Dictionaryand create it withNew. This gives the VBA editor access to the Dictionary type and its member names while writing code, but requires a reference to the library.
For a workbook that will run only in a managed Windows environment, either approach can work. Late binding is a practical default for portable Windows workbooks because a missing reference is less likely to cause a compile error on another computer. Early binding is convenient during development, especially when you want editor assistance and compile-time checks. Neither approach makes the Windows Scripting Runtime available to Excel for Mac.
Late-Bound Declaration and Creation
This complete example declares a late-bound Dictionary, adds two entries, and reads one value. Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.
Sub CreateLateBoundDictionary()
Dim dict As Object
Dim city As String
' Create a Windows Scripting.Dictionary without a project reference.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "Oslo", 710000
dict.Add "Lima", 9750000
city = "Oslo"
If dict.Exists(city) Then
MsgBox dict(city)
End If
End Sub
Set is required when assigning an object to an object variable. The variable itself is declared with Dim; CreateObject creates the Dictionary at run time. In this example, the key is a city name and the value is a number. The displayed value for Oslo is 710000.
Early-Bound Declaration and Reference Setup
For early binding, first enable the library in the VBA editor on Windows. Press Alt+F11 to open the editor, then choose Tools > References and select Microsoft Scripting Runtime. The exact wording can vary slightly by installed Office or Windows components. After selecting it, declare and create the Dictionary like this:
Sub CreateEarlyBoundDictionary()
Dim dict As Scripting.Dictionary
' Requires the Microsoft Scripting Runtime reference.
Set dict = New Scripting.Dictionary
dict.Add "A100", "Keyboard"
MsgBox dict("A100")
End Sub
Here the message displays Keyboard. If the reference is not available or is marked as missing on another computer, the project can fail to compile. To change to late binding, declare the variable as Object and use CreateObject("Scripting.Dictionary").
Add, Find, Update, and Remove Entries
Use Add to create a new key-value pair and Exists to check whether a key is already present. The Item property—often written with parentheses after the dictionary variable—returns or sets the value for a key. You can also use dict(key) as shorthand for dict.Item(key).
This pattern safely counts repeated labels. The example uses text keys and stores a numeric count. Save a copy of the workbook before running the macro because VBA changes cannot be reversed with Ctrl+Z.
Sub CountLabels()
Dim dict As Object
Dim labels As Variant
Dim label As Variant
Set dict = CreateObject("Scripting.Dictionary")
labels = Array("Red", "Blue", "Red", "Green", "Blue", "Red")
For Each label In labels
If dict.Exists(CStr(label)) Then
dict(CStr(label)) = dict(CStr(label)) + 1
Else
dict.Add CStr(label), 1
End If
Next label
MsgBox dict("Red")
End Sub
The result is 3, because Red occurs three times. Calling Add for a key that already exists would instead raise an error, which is why the code checks Exists first. To change an existing value, assign to that key as shown. Use dict.Remove key to delete one entry, or dict.RemoveAll to clear every entry while keeping the Dictionary object.
Keys, Comparison Rules, and Case Sensitivity
By default, text keys are compared using binary comparison, so capitalization matters: "North" and "north" can be distinct keys. To make text-key matching case-insensitive, set CompareMode to vbTextCompare immediately after creating the Dictionary and before adding any entries. Changing the comparison mode after entries have been added can raise an error.
Sub CaseInsensitiveKeys()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
' Set comparison behavior before adding keys.
dict.CompareMode = vbTextCompare
dict.Add "North", "Region 1"
MsgBox dict.Exists("north")
End Sub
The message returns True. This setting changes how text keys are compared; it does not change the spelling or capitalization stored in the original key. Choose the comparison mode deliberately when keys come from user input or worksheet cells, since unexpected spaces are still different characters. For example, "North" and "North " are not the same key unless the input is cleaned first.
Loop Through a Dictionary
The Keys and Items properties return arrays of keys and values. A straightforward way to visit every entry is to loop through the keys and look up each corresponding item. Count reports the number of entries. Do not rely on a Dictionary to represent a sorted list; sort the keys separately if order matters.
Sub PrintDictionaryEntries()
Dim dict As Object
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A100", "Keyboard"
dict.Add "B205", "Mouse"
For Each key In dict.Keys
Debug.Print CStr(key), dict(key)
Next key
End Sub
Open the Immediate window in the VBA editor with Ctrl+G to see the printed entries. On a Mac keyboard, the shortcut and editor availability can depend on the keyboard and Excel version; use the editor’s View menu to open the Immediate window if needed. A key variable declared as Variant is useful for this loop because the key array is returned as a Variant array.
Common Errors and Practical Safeguards
- Duplicate key error: Check
Existsbefore callingAdd, or assign a new value to an existing key when updating is intended. - Missing reference: An early-bound declaration can fail if Microsoft Scripting Runtime is not selected or is missing. Use late binding for Windows workbooks that need to avoid that project reference.
- Object variable not set: Create the Dictionary with
Setbefore using it. A declared object variable is initiallyNothing. - Wrong comparison behavior: Set
CompareModebefore adding keys, and decide whether capitalization should distinguish keys. - Unexpected key mismatch: Check for leading or trailing spaces and inconsistent data types. Converting worksheet values to a consistent string format can make matching more predictable.
A Dictionary can store objects as values as well as ordinary values, but object assignments need object-reference handling with Set. For basic lookups and counters, storing strings, numbers, dates, or other simple values is easier to maintain. Also, a Dictionary is held in memory: changing it does not write anything back to worksheet cells unless the macro explicitly outputs the data.
Dictionary Compared With Collection and Arrays
A Dictionary is a good fit when code needs to look up an item by a unique key or test whether a key exists. A VBA Collection can also hold items and can use keys, but it does not provide the same built-in Exists method or the same convenient key-and-item arrays. Arrays are useful for ordered data and can be efficient for processing a fixed block of worksheet values, but finding a particular value may require searching through the array.
Choose based on the job rather than assuming one container is always fastest or best. Use a Dictionary for unique-key lookups and counts; use an array for ordered batches of data; and consider a Collection for a simple group of items where its behavior is sufficient. A Dictionary does not automatically sort entries or remove duplicates from a worksheet range; your code must decide how to handle order and output.
Excel for Mac and Other Alternatives
The Scripting Dictionary examples above target Excel for Microsoft 365 on Windows. Excel for Mac does not provide the Windows COM-based Scripting Runtime in the same way, so CreateObject("Scripting.Dictionary") and a reference to Microsoft Scripting Runtime are not reliable cross-platform choices. A macro that depends on them should not be assumed to run unchanged on Mac.
For code that must run on both Windows and Mac, consider a VBA Collection, arrays, or a custom class and lookup routine, depending on the required behavior. These options may need more code to check for existing keys or manage updates. A third-party VBA Dictionary implementation is another possibility, but it adds a dependency that must be included and maintained. Test cross-platform macros in the actual Excel versions and operating systems where the workbook will be used.
Frequently Asked Questions
How do I declare a Dictionary in VBA?
For late binding on Windows, write Dim dict As Object, then initialize it with Set dict = CreateObject("Scripting.Dictionary"). For early binding, use Dim dict As Scripting.Dictionary and enable the Microsoft Scripting Runtime reference.
Do I need to add a reference for Scripting.Dictionary?
Only for early binding. Late binding uses Object and CreateObject, so you do not select the reference in Tools > References, although the Windows Scripting Runtime still needs to be available at run time.
How do I check whether a Dictionary contains a key?
Call dict.Exists(key). It returns True when the key exists and False when it does not. This is the normal check to make before adding a key that may already be present.
Can a Dictionary contain duplicate keys?
No. Keys must be unique under the Dictionary’s comparison rules. Use a different key, update the existing item’s value, or store multiple values inside a collection or array associated with that key.
Does Scripting.Dictionary work in Excel for Mac?
Do not rely on the Windows Scripting Runtime Dictionary for Mac VBA. For a workbook that must run on Mac, use a platform-compatible design such as a Collection, arrays, or custom VBA code, and verify it in the target Excel environment.
Related
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Loop Array: Examples, Bounds, and Best PracticesA VBA array loop visits array elements one at a time, usually with a For loop using LBound…
- VBA Array Search: Find Values with Loops or MatchTo search a VBA array, loop from LBound to UBound, compare each element with the value you want,…
- Excel VBA: Delete a PivotTable SafelyTo delete a PivotTable with VBA, clear its full report range: use pt.TableRange2.Clear after setting pt to the…
- VBA UserForm Design: Build a Practical Excel FormTo design a VBA UserForm in Excel, open the Visual Basic Editor, choose Insert > UserForm, add controls…
- VBA Dictionary: How It Works, Examples, and Common UsesA VBA Dictionary is a key–value collection: you store a value under a unique key, then retrieve it…
- VBA PivotTable Range: Source, Output, and ExamplesIn VBA, a PivotTable’s source range is the worksheet data used to build or refresh it, while its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.