A VBA data dictionary is usually the Scripting.Dictionary object: a key-and-value store that lets a macro look up information by a unique key. In Excel for Microsoft 365 on Windows, create one with CreateObject("Scripting.Dictionary"), then use methods such as Add and Exists to store and retrieve values.
What a VBA data dictionary is
A dictionary organizes data as pairs. Each pair has a key, which identifies an entry, and an item, which is the value associated with that key. For example, a product code could be a key and its product name or stock count could be the item. A dictionary can also keep track of which customer names have already appeared in a worksheet or count how often each category occurs.
The word “dictionary” here does not mean a special worksheet feature or a built-in VBA language type. Scripting.Dictionary is an object supplied by the Windows Scripting Runtime. VBA code can use it to perform lookups without repeatedly searching a worksheet or scanning a list. Keys must be unique: adding the same key twice with Add causes an error. The item associated with an existing key can, however, be changed.
This makes a dictionary useful when a macro needs to connect records, remove duplicates, count categories, or quickly check whether a value has been encountered. It is held in memory while the code runs; it does not automatically save its contents to the workbook or to a file.
How to create one in Excel for Windows
The simplest approach is called late binding. It creates the object by name at run time and does not require adding a library reference in the VBA editor. Declare the variable as an Object, then assign it with CreateObject. This is convenient for sharing a workbook among Windows computers with different Office setups.
Early binding is the other common approach. In the Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime. Then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can provide editor assistance and compile-time checking for the object’s members. The reference must be available on the computer running the workbook, so a missing reference can prevent the code from compiling there.
For most macros intended to run on different Windows installations, late binding is a practical default. The object is still the same dictionary; the difference is how VBA resolves its type.
Core methods and properties
These are the members most often used in everyday dictionary code:
Add key, iteminserts a new key and its associated item. The key must not already exist.Exists(key)returns True if the key is present. Check this before adding a key that might be repeated.dict(key)reads or assigns the item for a key. If code assigns to an existing key, it replaces that key’s item.Countreports the number of key-item pairs currently stored.KeysandItemsreturn arrays containing the keys and their corresponding items.Remove keydeletes one pair, whileRemoveAllclears the dictionary.
A subtle behavior matters when reading or assigning through the item property: requesting an item for a key that is not present can create that key with an empty item. Therefore, use Exists when the distinction between “not found” and “found with an empty value” matters. For intentional insertion, Add makes the operation explicit and exposes accidental duplicate keys rather than silently treating them as new records.
Example: count repeated names in a column
The following macro reads names from column A of a worksheet named Data, starting in row 2. It ignores blank cells, treats names differing only by letter case as the same key, and stores each name’s frequency as its item. It then reports the number of distinct nonblank names. The example assumes the cells being read do not contain Excel error values.
Sub CountNamesWithDictionary()
' Count nonblank names in column A on the Data worksheet.
Dim ws As Worksheet
Dim dict As Object
Dim lastRow As Long
Dim cell As Range
Dim key As String
Set ws = ThisWorkbook.Worksheets("Data")
Set dict = CreateObject("Scripting.Dictionary")
' Set comparison behavior before adding any keys.
dict.CompareMode = vbTextCompare
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "There are no names to count."
Exit Sub
End If
For Each cell In ws.Range("A2:A" & lastRow).Cells
key = Trim$(CStr(cell.Value2))
If Len(key) > 0 Then
If dict.Exists(key) Then
dict(key) = CLng(dict(key)) + 1
Else
dict.Add key, 1
End If
End If
Next cell
MsgBox dict.Count & " distinct nonblank names found."
End Sub
For instance, if the input contains Alex, Sam, Alex, and a blank cell, the dictionary contains two keys: Alex with item 2 and Sam with item 1. Its Count is 2 because Count measures unique keys, not the total number of input rows. The macro does not write a summary table; it illustrates the storage and counting pattern. A separate output step can use the dictionary’s Keys and Items arrays to place names and counts in worksheet cells.
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a change made by a macro may not be recoverable through Excel’s normal undo history.
Key comparison and duplicate handling
By default, a dictionary compares string keys using binary comparison, which treats uppercase and lowercase letters as different. Setting CompareMode to vbTextCompare makes string comparisons case-insensitive, so “Alex” and “ALEX” refer to the same key. Set this property before adding any keys; changing it after the dictionary contains data can raise an error.
Comparison settings do not automatically clean or standardize the text. In the example, Trim$ removes leading and trailing spaces, so “Alex ” is counted with “Alex.” It does not remove spaces within a name, standardize punctuation, or decide whether two different spellings refer to the same person. Those are data-cleaning decisions the macro must make deliberately.
When a duplicate key is expected, check Exists and decide what should happen: increment a count, replace the previous item, keep the first value, or report a conflict. If duplicates indicate bad input, using Add without a check can be useful because VBA raises an error instead of hiding the duplicate.
Loop through keys and use the stored values
A dictionary is most useful when the macro can both build it and use it. For example, code can first read a worksheet table into a dictionary keyed by an ID, then check another table’s IDs with Exists. That turns a repeated search into a direct membership check, which can be helpful when the same set of records is consulted many times.
To process every entry, use a Variant variable in a For Each loop over dict.Keys, then retrieve the matching item with dict(key). The keys and items arrays can also be assigned to Variant variables and written to a worksheet. Keep the relationship between array positions intact: the item at a given position corresponds to the key at that position. Do not assume the dictionary sorts entries alphabetically; sort the output separately if an ordered report is required.
For a simple existence check, the core logic is conceptually: ask Exists whether the key is present, then read its item only when the answer is True. This avoids accidentally creating a missing key by looking up its item. A dictionary can store different kinds of items, but using a consistent item type—such as a number for counts or a text value for labels—makes later processing easier to reason about.
Common problems and limitations
- Object creation fails:
CreateObject("Scripting.Dictionary")relies on the Windows Scripting Runtime. It is intended for Windows desktop VBA and is not a portable solution for Excel for the web or Mac Excel. - Duplicate-key error:
Addfails if the key already exists. UseExistsfirst when repeats are normal, or handle the error when duplicates should be treated as invalid data. - Unexpected extra key: reading or assigning
dict(key)for a missing key may create an empty entry. CheckExistsbefore reading when a missing key must remain missing. - Unexpected duplicates: case, spaces, punctuation, and inconsistent formats can affect whether two values count as the same key. Normalize input only according to the rules appropriate for the data.
- Reference error: early-bound code may show “MISSING” for its library reference on another computer. Late binding avoids that specific compile-time reference dependency, but still relies on the Windows object being available.
A dictionary also uses memory, so it is not automatically the right choice for every dataset. For very large data, consider the workbook’s available memory and whether processing the data in arrays or with Excel’s built-in data tools is more appropriate. If input cells can contain errors, handle those values before converting them with CStr; otherwise, conversion can fail.
Dictionary, Collection, or worksheet lookup?
VBA’s built-in Collection can also store items and retrieve them by a key. It is useful for some tasks, but it does not offer the same direct Exists method for checking whether a key is present. A dictionary is often the clearer choice when uniqueness, membership checks, or changing an item by key are central to the task. A Collection may suit code that mainly appends items and processes them in sequence.
For smaller, one-off lookups, worksheet formulas or Excel features may be simpler than a macro. In Microsoft 365, functions such as XLOOKUP can retrieve matching data directly in cells, while Remove Duplicates or a PivotTable can address common worksheet tasks without VBA. A dictionary is especially useful when a macro needs to perform many lookups, combine data from multiple sources, or apply custom rules that would be cumbersome in formulas.
On Mac, the Windows Scripting Runtime is not a dependable cross-platform choice. A VBA Collection, arrays, worksheet formulas, or a custom VBA class may be alternatives, depending on whether the task needs keyed lookups, ordering, or only a small number of values. Excel for the web does not run VBA macros; use a supported alternative such as Office Scripts where appropriate.
Frequently Asked Questions
Is Scripting.Dictionary built into VBA?
It is an object used by VBA, not a native VBA collection type. On Windows, VBA can create it late-bound with CreateObject("Scripting.Dictionary"), or use an early-bound reference to Microsoft Scripting Runtime.
How do I check whether a dictionary contains a key?
Call dict.Exists(key). It returns True when the key is present and False when it is not. This is safer than reading the item property when a missing key should not be created.
How do I make dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare before adding entries. The default comparison is case-sensitive for text keys. Normalize other differences, such as extra spaces, separately if the data requires it.
Can I use Scripting.Dictionary in Excel for Mac?
Do not assume the Windows Scripting Runtime is available in Mac Excel. For code that needs to run on both platforms, consider a Collection, arrays, worksheet functions, or another cross-platform design.
Why does dict.Count not equal the number of rows?
Count reports the number of unique keys currently stored, not how many times a key was encountered. In a frequency-counting macro, repeated rows increase an item’s count while the dictionary’s Count stays the same for that key.
Related
- How to Open a Text File in Excel Without Losing Data
- How to Open an AIS JSON File in Excel
- How to Open and Repair an Excel File Without Losing Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA If Then Examples for ExcelVBA’s If...Then statement runs code when a condition is true, and can choose an alternative with Else or…
- VBA Error Handling Best Practices for Reliable MacrosVBA error handling is most reliable when each procedure has a clear failure path: use On Error GoTo…
- Close an Excel Workbook with VBA Without Losing WorkTo close an Excel workbook with VBA, call its Close method and decide whether to save changes: choose…
- VBA If Not IsError: Meaning and ExamplesIn VBA, If Not IsError(value) Then means “run this code if value is not an error value.” Use…
- Copy and Paste Dynamically Using VBA in ExcelTo copy a range that changes size as your data grows, use VBA to find its last row…
- Excel Goal Seek VBA Code for a Whole ColumnTo run Goal Seek down a column, loop through the populated rows and call Range.GoalSeek once per row:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.