Yes, Excel VBA can use a Dictionary on Windows, but VBA does not include a native Dictionary type. The commonly used option is the Scripting.Dictionary object, which stores unique keys alongside values and lets you look up, count, or update entries efficiently.
What a Dictionary Is in VBA
A Dictionary is a key-and-item collection: each key identifies one associated value. For example, a key might be a product code and its item might be a product name, or a person’s name might be the key and the number of times it appears might be the item. Unlike a worksheet range, a Dictionary is held in memory while your macro runs.
Each key can appear only once. That makes a Dictionary useful when a macro needs to test whether a value has already been seen, retrieve an associated value, or build a unique list. It is not the same as a worksheet formula, and it does not appear as a normal worksheet function. You create and use it in VBA code.
The object usually meant by “VBA Dictionary” is provided by the Microsoft Scripting Runtime. On Windows, you can use it with early binding, which gives VBA access to its declared type and members, or late binding, which creates the object at run time. Both approaches use the same basic Dictionary behavior.
How to Create a Dictionary
Late binding is often the simplest way to begin on Windows because it does not require you to set a project reference. Declare the variable as an object, then create the Dictionary with CreateObject:
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
For early binding, open the Visual Basic Editor, choose Tools > References, and select Microsoft Scripting Runtime. Then declare and create the object using its type:
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary
Early binding can provide member suggestions and catch some type mistakes while you edit. The trade-off is that another computer needs the relevant reference available and correctly set. Late binding avoids that explicit reference, but VBA treats the variable as a general object, so it offers less help while writing the code. For small macros shared across Windows installations, late binding is a common practical choice.
After creating the object, set its comparison behavior before adding keys if you need case-insensitive text matching. For example, dict.CompareMode = vbTextCompare treats “North” and “north” as the same key. The default comparison is binary, so differences in letter case are significant. Changing CompareMode after keys have been added can cause an error.
Adding, Finding, and Updating Items
The main operations are straightforward. Add creates a new key and item, Exists checks for a key, and the Item property reads or changes the value associated with a key. The Count property reports how many keys are stored.
dict.Add key, itemadds a new pair. It raises an error if that key already exists.dict.Exists(key)returns True or False, making it useful before adding or updating.dict(key)reads or assigns the item associated with a key.dict.Keysanddict.Itemsreturn arrays of keys and values, respectively.dict.Remove keyremoves one pair, whiledict.RemoveAllempties the Dictionary.
A particularly useful detail is that assigning through the Item property can add a missing key as well as update an existing one. However, reading an absent key through that property also creates the key with an empty item. Therefore, use Exists when you mean to test membership; do not assume that simply reading dict(key) is a harmless lookup.
Example: Count Repeated Names in a Column
This Windows macro reads names from column A of a worksheet named Data, counts each nonblank name, and writes the unique names and counts into columns C and D. It uses late binding and ignores capitalization differences. The output area should be clear or checked beforehand, because the macro writes results but does not remove older results below them.
Option Explicit
Sub CountNames()
Dim ws As Worksheet
Dim counts As Object
Dim lastRow As Long
Dim r As Long
Dim itemText As String
Dim keys As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Data")
Set counts = CreateObject("Scripting.Dictionary")
counts.CompareMode = vbTextCompare
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
itemText = Trim$(CStr(ws.Cells(r, "A").Value2))
If Len(itemText) > 0 Then
If counts.Exists(itemText) Then
counts(itemText) = CLng(counts(itemText)) + 1
Else
counts.Add itemText, 1
End If
End If
Next r
If counts.Count > 0 Then
keys = counts.Keys
For i = LBound(keys) To UBound(keys)
ws.Cells(i + 2, "C").Value = keys(i)
ws.Cells(i + 2, "D").Value = counts(keys(i))
Next i
End If
End Sub
For example, if A2:A5 contains “Mira,” “Owen,” “Mira,” and “MIRA,” the case-insensitive comparison produces two keys: Mira with a count of 3, and Owen with a count of 1. The order of keys is not a sorting guarantee, so sort the output separately if a particular order matters.
Save a copy of the workbook before running a macro, especially while adapting code to real data. VBA macros cannot be undone with Ctrl+Z. On Windows, run code from the Visual Basic Editor or assign the macro to a control; Excel for the web cannot run VBA macros.
When a Dictionary Is a Good Choice
A Dictionary is most helpful when the macro performs many membership checks or needs to associate a value with each unique key. Common examples include counting repeated entries, matching IDs to descriptions, removing duplicates in memory, and building a quick lookup from one range before processing another.
For instance, a macro can read a two-column table of employee IDs and department names into a Dictionary, then look up each ID from another list. This avoids searching the entire source range anew for every row. If the task is simply to return a value in a worksheet, a worksheet lookup formula or a normal range-based approach may be easier to understand and maintain.
Dictionary keys are unique, but items do not have to be. An item can be a number, text, or another value suitable for the operation. Choose a consistent key format: an ID stored as text is not necessarily equivalent to the same-looking number stored numerically. Normalize imported values when necessary, and be deliberate about trimming spaces, handling blank cells, and converting values to text.
Dictionary Compared with Collection and Arrays
VBA’s built-in Collection is an alternative for storing items and retrieving them by position or, in some cases, by a key. It is available without creating the Scripting Runtime object, but it does not provide the same convenient Exists method. A Dictionary is generally more direct when the code must ask whether a key is already present or update a value tied to that key.
Arrays are a better fit for fixed or sequential data, especially when the code mostly processes values in order. They can be efficient and straightforward, but they do not automatically enforce unique keys or provide a key-based lookup interface. A worksheet range is often best when users need to inspect, filter, sort, or edit the data directly. The right choice depends on how the data will be accessed, not on a blanket rule that one structure is always fastest.
- Choose a Dictionary for key-based lookups, uniqueness checks, and counts.
- Choose a Collection for a simple ordered set of items when its interface is sufficient.
- Choose an array for sequential processing or a known data layout.
- Keep data in a worksheet range when visibility and user editing are central to the task.
Common Errors and Key Details
A duplicate-key error is one of the most common issues. Calling Add twice with the same key raises a run-time error, so check with Exists first or use the Item property deliberately when updating. Conversely, reading a missing key with the Item property can silently create an entry, which may make the final Count larger than expected.
Case and formatting can also create unexpected results. With the default binary comparison, “ABC” and “abc” are separate keys. Leading or trailing spaces are different characters too, so “ABC” and “ABC ” do not match. Set CompareMode before inserting text keys, and normalize data consistently if it comes from different sources. Avoid converting identifiers to numbers if leading zeros are meaningful; “0042” may be a distinct code from “42.”
Another frequent issue is an object-creation error on a computer where the Scripting Runtime is unavailable or inaccessible. Confirm that the code is running in desktop Excel for Windows and that the object name is spelled correctly. If you use early binding, check the project reference in Tools > References. For loops over Keys, account for an empty Dictionary before calling LBound or UBound, as the example does.
Windows, Mac, and Macro Safety
Scripting.Dictionary is a Windows scripting component, not a Dictionary type built into VBA itself. Late binding with CreateObject("Scripting.Dictionary") is commonly used in desktop Excel for Windows. On Mac, Windows COM automation components such as this are not generally available in the same way, so code that depends on this object should not be assumed to work there. A Collection, arrays, or a Mac-compatible custom solution may be needed; test the chosen approach in the target Excel version.
Excel for the web does not execute VBA, even if a workbook contains macros. A workbook can be opened there, but VBA code must be run in desktop Excel. When distributing a macro-enabled workbook, use a trusted source, inspect code before enabling macros, and follow your organization’s macro policy. A Dictionary itself does not grant special access to files or data; the permissions and actions of the surrounding macro determine what it can do.
Frequently Asked Questions
Does Excel VBA have a built-in Dictionary?
No. VBA does not provide a native Dictionary type. On Windows, the commonly used Scripting.Dictionary object supplies dictionary behavior through the Microsoft Scripting Runtime.
Do I need to enable Microsoft Scripting Runtime?
Not if you use late binding with CreateObject. Early binding requires selecting Microsoft Scripting Runtime under Tools > References in the Visual Basic Editor.
How do I check whether a Dictionary key exists?
Call dict.Exists(key). It returns True when the key is present and False otherwise, without relying on an Item-property read that could create a missing key.
Can a VBA Dictionary contain duplicate keys?
No. Each key must be unique. Use one key with an updated item, or choose a different data structure if the same key must hold multiple separate records.
Will Scripting.Dictionary work in Excel for Mac or Excel for the web?
Do not assume it will. The Windows scripting component is not generally available on Mac, and Excel for the web does not run VBA macros. Use a tested alternative or run the macro in desktop Excel for Windows.
Related
- Excel Split Cell Into Multiple Cells: Methods That Work
- Excel Split Names in One Cell Into Two Cells
- Excel Split One Cell Into Multiple Rows: How It Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Copy Dictionary: Methods, Code, and Deep CopiesTo copy a VBA Scripting.Dictionary, create a second dictionary and add each key and item from the first;…
- Excel VBA Get Current Workbook Name: Code and TipsTo get the name of the workbook currently active in Excel VBA, use ActiveWorkbook.Name. For example, assign Set…
- Excel Sheet Open VBA: Open and Activate a WorksheetTo open a worksheet that is already in an Excel workbook, use VBA to activate it: ThisWorkbook.Worksheets("Summary").Activate. If…
- Excel VBA Get Month From Date: Code, Formats, and TipsIn Excel VBA, use the built-in Month function to get the month number from a date: Month(dateValue) returns…
- VBA Loop Format: Syntax, Examples, and When to Use EachA VBA loop repeats a block of code while counting through values, visiting items in a collection, or…
- Excel VBA Sort Order: Ascending, Descending, and CustomIn Excel VBA, set a sort field’s Order to xlAscending or xlDescending to choose its direction. For a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.