A VBA Scripting.Dictionary is an object that stores pairs of unique keys and associated values, letting a macro look up, count, or total information by key. It is especially useful when processing repeated values, such as finding each product’s total sales, because it can check whether a key exists and retrieve its value without repeatedly searching a worksheet range.
What the Scripting.Dictionary Object Is
Scripting.Dictionary is a Windows COM object provided by the Microsoft Scripting Runtime. It is not a built-in VBA collection, although it is commonly used in VBA because its key-based lookup methods are convenient. A dictionary has a collection of keys, and every key points to an item, or value. For example, the key "North" could point to the number 125, a customer name could point to a customer record, or a word could point to the number of times it occurs.
Each key must be unique under the dictionary’s comparison rules. Values do not have to be unique: two different keys can point to the same value. Items can hold many kinds of values, including numbers, strings, dates, arrays, and object references. That flexibility makes a dictionary useful for both simple tallies and more structured VBA work.
It is not a worksheet feature or formula. You create and use it in VBA code, typically in the Visual Basic Editor. Excel for the web does not run VBA macros, so a workbook that depends on this object needs a desktop version of Excel to run its VBA.
Creating a Dictionary: Early and Late Binding
There are two common ways to create the object. With late binding, VBA treats it as a general object and creates it by its COM programmatic identifier:
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Late binding does not require a reference to the Microsoft Scripting Runtime in the workbook’s VBA project. This makes it convenient for sharing a macro with Windows users whose project references may differ. The trade-off is that the editor cannot provide the same level of type-specific autocomplete, and many dictionary constants may need to be written as their numeric values if they are not available through a reference.
With early binding, first open the Visual Basic Editor, choose Tools > References, and select Microsoft Scripting Runtime. Then declare the variable using the dictionary type:
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary
Early binding gives clearer declarations and editor assistance, and it can make code easier to read. It also means the project depends on that reference being available. If a reference is marked “MISSING” on another computer, the project may fail to compile until the reference is repaired. Both approaches use the same object; the choice is mainly about development convenience and portability within the environments you need to support.
How Keys, Items, and Lookups Work
The most-used members are Add, Exists, the default Item property, and Count. A small example illustrates their roles:
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
If dict.Exists("North") Then
Debug.Print dict("North") ' Prints 125
End If
Debug.Print dict.Count ' Prints 1
Add creates a key-item pair and raises an error if that key already exists. Exists checks for a key and returns True or False. Writing dict(key) reads the item for that key. Assigning an item through that property can also create a missing key, which can be handy for tallying but is a source of accidental new entries if the key is misspelled.
Use Keys and Items when you need the contents as arrays, and use Remove to delete one key or RemoveAll to clear the dictionary. The Key property can change an existing key. If you need to loop over entries, a common pattern is For Each key In dict.Keys; declare the loop variable as a Variant. A dictionary is not a two-column worksheet range, so its enumeration order should not be treated as a sorting or reporting rule. Sort the keys separately if a particular order matters.
Practical Example: Totalling Rows by Category
Suppose a worksheet named Data has category names in column A and numeric amounts in column B, with headers in row 1. The following macro adds amounts for matching categories and prints each total in the Immediate window. It assumes category cells and amount cells contain usable values, with no worksheet error values in those columns.
Sub SummarizeByCategory()
Dim ws As Worksheet
Dim totals As Object
Dim lastRow As Long
Dim r As Long
Dim category As String
Dim amount As Double
Dim key As Variant
Set ws = ThisWorkbook.Worksheets("Data")
Set totals = CreateObject("Scripting.Dictionary")
totals.CompareMode = vbTextCompare
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
category = Trim$(CStr(ws.Cells(r, "A").Value2))
If Len(category) > 0 And IsNumeric(ws.Cells(r, "B").Value2) Then
amount = CDbl(ws.Cells(r, "B").Value2)
If totals.Exists(category) Then
totals(category) = CDbl(totals(category)) + amount
Else
totals.Add category, amount
End If
End If
Next r
For Each key In totals.Keys
Debug.Print key, totals(key)
Next key
End Sub
For instance, if the rows contain North/40, South/25, and North/10, the printed totals are North/50 and South/25. The dictionary performs the grouping in memory rather than requiring a separate worksheet search for every row. To display the results on a sheet instead, write the keys and items to cells, taking care to choose an output location that will not overwrite the source data.
Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even when the code’s purpose is only to automate a repetitive task. This example prints results rather than editing cells, but a copy is still a sensible precaution when adapting code.
Case Sensitivity and CompareMode
By default, a dictionary compares string keys using binary comparison. In practical terms, "North" and "north" are treated as different keys. Set CompareMode to vbTextCompare when capitalization should not distinguish keys. Do this immediately after creating the dictionary and before adding any entries:
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
Changing CompareMode after the dictionary contains data raises an error. The setting affects string-key comparisons; it does not trim spaces or normalize spelling. For example, "North" and " North " remain different keys unless the code trims the text first. This is why the example uses Trim$ as well as text comparison. Choose the comparison rule based on the data: a product code may need exact matching, while names entered with inconsistent capitalization may be better handled case-insensitively.
Common Errors and How to Avoid Them
Several mistakes account for many dictionary problems. Check these points when a macro behaves unexpectedly:
- Adding a duplicate key:
.Addraises an error when the key already exists. Use.Existsfirst if duplicates should update an existing total rather than stop the macro. - Using a missing key without checking: Reading a missing item can produce an empty value, while assigning to that key can create a new entry. Validate keys when a typo would be consequential.
- Setting comparison mode too late: Assign
CompareModebefore the first key is added; changing it afterward is not allowed. - Confusing a key with its item:
dict.Keysreturns keys;dict.Itemsreturns values. They are different arrays, even though their positions correspond. - Ignoring blanks or errors from cells: Decide how to handle blank keys, worksheet error values, and nonnumeric amounts before converting cell contents with
CStrorCDbl. Validate inputs when the source data is not controlled. - Assuming an order: Do not rely on dictionary enumeration to produce alphabetical, numerical, or worksheet order. Sort keys explicitly when presentation order matters.
When an error occurs, inspect the key being processed and the dictionary’s Count. A breakpoint in the Visual Basic Editor and the Locals or Watch window can help identify whether a key was added twice, a value has an unexpected type, or the code reached a missing reference.
When to Use It Instead of a Collection or Worksheet Formula
A VBA Collection can store values and lets you retrieve an item by a string key, but it does not provide the dictionary’s direct Exists method or the same set of key-management features. A collection can be a reasonable built-in alternative for some tasks, especially when you need a simple collection of objects. A dictionary is often clearer when the code repeatedly asks whether a particular key has been seen or updates a total associated with that key.
For calculations that should remain visible and update when worksheet data changes, formulas or a PivotTable may be a better choice than a macro. A dictionary is most useful when VBA is already processing data, when a macro needs fast repeated lookups, or when intermediate groupings do not need to appear on the sheet. It is not automatically the best choice for every workbook: a simple formula or PivotTable may be easier for other users to inspect and maintain.
For large data sets, a common VBA pattern is to read a worksheet range into a two-dimensional Variant array, process the array with a dictionary, and write results back in a batch. Repeatedly reading individual cells can be slow because each worksheet interaction adds overhead. The dictionary helps with in-memory lookups; it does not remove the cost of thousands of cell-by-cell operations.
Windows, Mac, and Portability
The Microsoft Scripting Runtime is a Windows COM component. Late-bound creation with CreateObject("Scripting.Dictionary") is therefore a Windows-oriented solution and should not be assumed to work in Excel for Mac. Mac versions of Excel support VBA, but they do not provide general Windows COM automation in the same way. If a workbook must run on both Windows and Mac, test on both platforms and consider a VBA Collection, arrays, or a custom class as a more portable design. Each alternative has different capabilities, so the code may need to change rather than simply substituting one object name.
Excel for the web cannot run VBA macros. A workbook can be stored or viewed there, but its VBA code must be run in desktop Excel. If users need browser-based automation, that calls for a different approach, such as Office Scripts, which is separate from VBA and does not use the Scripting.Dictionary object in the same way.
Frequently Asked Questions
Do I need to add a reference to use Scripting.Dictionary?
No. Late binding with CreateObject("Scripting.Dictionary") does not require adding the Microsoft Scripting Runtime reference. Early binding does require selecting that reference in Tools > References in the Visual Basic Editor. The object itself is a Windows component, so avoiding a reference does not make it universally portable to Mac or the web.
Is Scripting.Dictionary available in Excel for Mac?
Do not rely on it for cross-platform Mac VBA. The object is supplied as a Windows COM component, and Windows COM automation is not generally available in Mac Excel. Use and test a suitable alternative, such as a Collection or arrays, if Mac support is required.
Can a dictionary contain duplicate keys?
No. Each key is unique according to its comparison mode. Calling .Add with an existing key raises an error. To update its value, check .Exists and assign a new item; to keep multiple records, use a distinct key or store a collection or array as the item.
What is the difference between Keys and Items?
Keys returns an array of the dictionary’s keys, while Items returns an array of their associated values. Corresponding elements occupy matching positions, but neither array is guaranteed to be sorted for reporting purposes.
Can Scripting.Dictionary store objects?
Yes. A dictionary item can hold an object reference as well as a basic value. When retrieving an object into an object variable, use VBA’s Set statement. Keep the object’s lifetime and reference handling in mind, especially if multiple objects refer to one another.
Related
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- 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: How to Find an Accredited VA PractitionerIf by VBA you mean the U.S. Department of Veterans Affairs’ Veterans Benefits Administration, use VA’s official accreditation…
- Excel VBA Copy and Paste a Column: Examples and TipsTo copy a column with Excel VBA, use sourceWs.Columns("B").Copy Destination:=targetWs.Columns("D"), replacing the worksheet variables and column letters with…
- Excel VBA Long Strings: Limits and Practical FixesIn Excel VBA, use a variable-length String to hold long text; it can theoretically contain about 2 billion…
- Excel VBA UserForm Label Font Color: Set and ChangeTo change an Excel VBA UserForm label’s text color, set its ForeColor property. For example, in the UserForm’s…
- VBA Dictionary Methods: Add, Exists, Keys and MoreA VBA Dictionary stores pairs of unique keys and associated values, and its core methods are Add, Exists,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.