A VBA Dictionary object stores unique keys alongside associated values, letting a macro retrieve, update, and count information by key. In Excel for Microsoft 365 on Windows, you can create one with late binding using the Scripting.Dictionary component, then use methods such as Exists, Add, and Remove to work with its contents.
What the VBA Dictionary Object Is
A dictionary is a key-and-value collection. Each key identifies one entry, and its value holds the information associated with that key. For example, a product code can be the key and its stock count the value. You can then ask whether a code is present or retrieve its count without searching a worksheet row by row yourself.
Keys must be unique within a dictionary, but values do not have to be. Two different keys can hold the same value. Keys and values are stored in memory while the VBA procedure runs; a dictionary does not automatically write data back to a worksheet or save itself in the workbook.
This structure is useful for tasks such as counting repeated entries, checking whether a value has already appeared, matching identifiers, or associating labels with calculated results. It is not a worksheet function: you create and use it in VBA code.
Availability in Excel and VBA
The Scripting.Dictionary is part of the Microsoft Scripting Runtime. In Excel for Microsoft 365 on Windows, VBA can usually create it through COM automation. This is why examples often use the name Scripting.Dictionary and why some VBA projects need a reference to the Microsoft Scripting Runtime library.
Excel for the web cannot run VBA macros. Mac support is also an important distinction: the Windows COM-based Scripting.Dictionary approach is not a dependable cross-platform choice for Excel for Mac. If a workbook must run on both Windows and Mac, consider a VBA Collection, arrays, or worksheet-based methods, and verify any chosen library against the Excel versions you need to support.
There are two common ways to declare a dictionary on Windows:
- Late binding: Create the object by its programmatic name at runtime. This avoids requiring users to set a VBA reference, but the variable is declared as Object, so the editor offers less help with its members.
- Early binding: Add the Microsoft Scripting Runtime reference in the Visual Basic Editor under Tools > References, then declare a Scripting.Dictionary variable. This offers type information and editor autocomplete, but the reference must be available and correctly resolved on other computers.
Late binding is often more convenient for a workbook distributed among Windows users with different Office setups. Early binding can be useful in a controlled Windows environment where compile-time type information is valuable.
How Keys, Values, and Comparison Work
The dictionary exposes several members that cover the usual operations. Count reports the number of entries; Keys and Items return arrays of the keys and values; Exists checks for a key; Add inserts a new key and value; Remove deletes one entry; and RemoveAll empties the dictionary. The Item property retrieves or assigns the value associated with a key.
By default, string keys are compared using binary comparison, so capitalization can distinguish keys: for example, “North” and “north” may be treated as different entries. To compare text without regard to case, set CompareMode to vbTextCompare before adding entries. Changing CompareMode after the dictionary already contains data raises an error, so choose the comparison rule immediately after creating the object.
Use Exists when you need to test for a key. Add is intended to create a new entry and raises an error if that key already exists. Assigning a value through the Item property has different behavior: it creates the key if it is absent and replaces the stored value if it is present. That makes assignment convenient for updating a running total, while Add is useful when duplicate keys should be treated as a problem.
Creating a Dictionary and Counting Entries
The following example reads labels from cells A2 downward on a worksheet named Data, counts each nonblank label without regard to capitalization, and writes the results in columns D and E. Reserve those output columns for the macro. It uses late binding, so it does not require adding a reference in Tools > References.
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z. In the Visual Basic Editor, insert a standard module, paste the procedure, and run it with Data as the worksheet name and the source labels in column A.
Sub CountLabels()
' Count labels in Data column A and write results in columns D:E.
Dim ws As Worksheet
Dim dict As Object
Dim lastRow As Long
Dim cell As Range
Dim labelText As String
Dim keys As Variant
Dim items As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
For Each cell In ws.Range("A2:A" & lastRow)
labelText = Trim$(CStr(cell.Value2))
If Len(labelText) > 0 Then
If dict.Exists(labelText) Then
dict(labelText) = CLng(dict(labelText)) + 1
Else
dict.Add labelText, 1
End If
End If
Next cell
ws.Range("D1").Value = "Label"
ws.Range("E1").Value = "Count"
If dict.Count > 0 Then
keys = dict.Keys
items = dict.Items
For i = 0 To dict.Count - 1
ws.Cells(i + 2, "D").Value = keys(i)
ws.Cells(i + 2, "E").Value = items(i)
Next i
End If
End Sub
For example, if A2:A5 contains Maple, Oak, maple, and Pine, the macro treats Maple and maple as the same key and produces counts of 2, 1, and 1. The order of dictionary entries should not be used as a sorting rule; sort the output range separately if a particular order is required.
Choosing Between a Dictionary and Other Options
A Dictionary is a good fit when code repeatedly needs to find a value by a unique identifier, or when it needs to test membership efficiently without repeatedly scanning a list. It is especially handy when the macro builds a lookup structure once and consults it many times. For a small, one-off task, a direct worksheet formula or a simple loop may be easier to understand.
A VBA Collection also holds items and can use keys, but its interface differs and it does not provide the same straightforward Exists method. Collections are built into VBA and can be useful where platform portability matters, though checking for a missing key often needs error handling or a separate tracking method.
Arrays are suitable when data is naturally a sequence and the code processes it in order. They can be compact and fast for bulk operations, but finding a particular item generally requires searching unless the data is structured or sorted for that purpose. Worksheet functions and Excel tables may be clearer when the result should remain visible and update with spreadsheet data rather than exist only during a macro.
- Choose a Dictionary for unique-key lookup, de-duplication, or frequency counts.
- Choose an array for ordered data that is processed as a batch.
- Choose a Collection when its simpler built-in object model suits the task or portability is important.
- Choose formulas or a table when users need transparent, worksheet-based results.
Common Errors and Practical Safeguards
A duplicate-key error is a common surprise when Add is called more than once for the same key. Decide whether a duplicate should be rejected, ignored, or used to update the existing value. For counting, check Exists first and increment the current value; for a simple overwrite, assign through the Item property deliberately.
Another frequent issue is setting CompareMode too late. Set it directly after creating the dictionary and before inserting the first key. Also consider how the source data is normalized. Trimming spaces, converting values consistently, and deciding how to handle blank cells can prevent values that look identical to a person from becoming separate keys.
Be cautious when converting worksheet values to strings. A cell may contain an Excel error value, a date, or a number; CStr is not a universal substitute for deciding what those values mean. If the source can contain errors, check for them with IsError before converting. If numeric identifiers can have leading zeros, preserve them as text in the source because converting a number to text cannot restore zeros Excel has already discarded.
Finally, a dictionary lives in memory, so a macro that ends does not preserve its contents for the next run. Write results to cells, a file, or another persistent store if they must remain available.
Frequently Asked Questions
How do I create a dictionary in Excel VBA?
On Windows, declare an Object variable and create it with CreateObject using the Scripting.Dictionary programmatic name. Set CompareMode before adding keys. The example above shows a complete late-bound macro.
Do I need to add a reference for Scripting.Dictionary?
Not when using late binding with CreateObject. Early binding requires selecting Microsoft Scripting Runtime under Tools > References in the Visual Basic Editor, and the reference must be available on the computer running the workbook.
How do I check whether a key exists?
Call the dictionary’s Exists method with the key and use its True or False result in an If statement. This avoids relying on an error to detect an absent key.
Can a dictionary have duplicate keys?
No. A key identifies one entry, so Add raises an error if that key already exists. You can update the existing value, or check Exists and choose another action.
Does Scripting.Dictionary work in Excel for Mac?
The COM-based Scripting.Dictionary method shown here is intended for Windows and is not a reliable Mac-compatible solution. For cross-platform VBA, investigate alternatives such as Collection or arrays and test the workbook on the target Excel versions.
When a Dictionary Is the Right Tool
Think of a Dictionary as an in-memory lookup table: each distinct key points to one value, and your macro controls how entries are added, compared, and saved. It can simplify counting and matching work, but it is not automatically the best choice for every list. Check platform requirements, define how duplicate and blank values should behave, and choose a worksheet-based alternative when users need results that persist and remain easy to inspect.
Related
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- UserForm VBA Properties: What They Do and How to Set ThemUserForm VBA properties control a form’s identity, appearance, size, position, and behavior in an Excel desktop workbook. You…
- Outlook Excel VBA: Automate Email and Calendar TasksYes—Excel VBA can automate classic Outlook for Windows, including creating email drafts from spreadsheet rows and working with…
- Excel VBA Loop Through Columns: Practical ExamplesTo loop through columns in Excel VBA, use a For loop with a column number, or a For…
- Excel VBA If String Equals: Exact and Case-InsensitiveIn Excel VBA, test whether two strings are equal with If firstText = secondText Then. That comparison is…
- VBA Go to Next Item in a For LoopVBA does not have a Continue For statement. To skip the rest of the current pass and move…
- Excel VBA Find Sheet by Name: Safe Code and ExamplesTo find a worksheet by its tab name in Excel VBA, use ThisWorkbook.Worksheets("Summary") and handle the error that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.