Yes. A VBA Dictionary exists as the Scripting.Dictionary object: a key-value container that lets a macro store items under unique keys and look them up efficiently. In Excel for Microsoft 365 on Windows, you can create one with late binding, without adding a reference to the VBA project.
What a VBA Dictionary Is
A dictionary holds pairs: a key that identifies an entry and an item, or value, associated with that key. For example, a customer ID can be the key and the customer’s name or order total can be its item. A key can appear only once in a dictionary, but the same item value can be stored under multiple keys.
The object is part of the Windows Scripting Runtime, rather than a core VBA collection type. That distinction matters when you share a workbook: the macro needs access to the runtime, and support can differ between operating systems. The object is often called a VBA Dictionary because VBA code commonly creates and uses it, even though it is supplied by the scripting runtime.
A dictionary is useful when a macro needs to answer questions such as “Have I seen this code before?”, “What value belongs to this ID?”, or “How many times has this category appeared?” It can also prevent duplicate processing and help combine data from worksheets.
How to Create One in Excel for Windows
With late binding, declare the variable as an object and create it with CreateObject. This avoids selecting a library reference in the editor, and is often convenient when distributing a workbook to other Windows users. The following example counts nonblank statuses in column A of a sheet named Data and prints the results in the Immediate window.
Sub CountStatuses()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Dim status As String
Dim counts As Object
Dim key As Variant
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Create a late-bound Dictionary.
Set counts = CreateObject("Scripting.Dictionary")
counts.CompareMode = vbTextCompare
For r = 2 To lastRow
status = Trim$(CStr(ws.Cells(r, "A").Value))
If Len(status) > 0 Then
If counts.Exists(status) Then
counts(status) = CLng(counts(status)) + 1
Else
counts.Add status, 1&
End If
End If
Next r
For Each key In counts.Keys
Debug.Print CStr(key), counts(key)
Next key
End Sub
This example assumes row 1 contains a heading, the statuses are plain text or values that can be converted to text, and the cells do not contain Excel errors. It treats “Open” and “open” as the same status because CompareMode is set to vbTextCompare. The output appears in the VBA editor’s Immediate window, which you can show with Ctrl+G on Windows. Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.
Keys, Items, and the Main Methods
The key is the lookup identifier; the item is the stored value. Keys are unique within a dictionary, while items do not have to be unique. Common operations include:
Add key, itemcreates a new pair. Trying to add a key that already exists raises an error.Exists(key)returns True when the key is present, making it useful before reading or adding.dict(key)reads or sets the item for a key. Be careful: requesting an item for a missing key can create that key with an empty item, so check withExistswhen absence matters.Countreturns the number of pairs.KeysandItemsreturn arrays containing the current keys or items.Remove keydeletes one pair, andRemoveAllclears the dictionary.
For example, if counts is a dictionary, If counts.Exists("Open") Then safely checks for that entry. You can then read counts("Open"). If the key is absent, add it deliberately with counts.Add "Open", 1. This explicit pattern makes the macro’s behavior easier to understand than relying on an implicit default item.
Choosing and Comparing Keys
Keys should represent a stable identity. A product code, employee ID, normalized email address, or category label can work, depending on the task. If values that look alike should be treated as identical, decide how to normalize them. For text, that may mean trimming extra spaces, standardizing case, or removing unwanted characters before using the value as a key.
A dictionary’s comparison behavior is controlled by CompareMode. Its default is binary comparison, which distinguishes text by case. Set it to vbTextCompare when case should not distinguish keys. Set this property before adding entries; changing it after the dictionary contains data can raise an error. Text comparison does not automatically remove spaces or make different spellings equivalent, so normalize those separately if needed.
Choose keys that do not accidentally combine different records. For example, using a person’s first name alone is unsafe if multiple people can share it. If a unique identifier is unavailable, a combined key may help, but use a separator that cannot create ambiguous combinations. For instance, concatenating fields without a delimiter can make different pairs of values produce the same text.
When a Dictionary Is the Right Tool
Use a dictionary when you need fast membership checks, unique keys, or a direct mapping from one identifier to a value. It is particularly handy in loops: load or build the mapping once, then check each incoming record against it. Typical uses include counting categories, identifying duplicate IDs, mapping codes to descriptions, and collecting one result per customer.
For example, a macro could load product codes from a reference worksheet into a dictionary, then scan a transaction list and check whether each code exists. This is often clearer than repeatedly searching the reference range cell by cell. A dictionary can also store a running count as its item, as in the example above. It does not, by itself, sort entries or provide a worksheet-style table; those are separate tasks.
For a small list that needs to be visible, editable, and updated by users, a worksheet table may be a better source of truth. A dictionary is an in-memory object used while the macro runs. Unless the macro writes its contents somewhere, its data is not saved as a permanent workbook table.
Dictionary Compared with Collection and Worksheet Lookups
VBA’s built-in Collection is another way to group items and can use string keys. It is useful when you need a simple ordered collection, but it does not provide a direct Exists method. Checking whether a key is present commonly requires error handling or a separate tracking mechanism. A Dictionary provides Exists, Keys, and Items, which make lookup and iteration patterns more direct.
Worksheet formulas or Excel tables are often preferable when results should update automatically as cells change or when other workbook users need to inspect the logic. Functions such as XLOOKUP are available in Microsoft 365 and Excel 2021 or later, but they are worksheet formulas rather than VBA objects. A dictionary is a better fit when the work is procedural, temporary, or part of a macro that processes many records. The right choice depends on whether the result should live in cells, in a VBA collection, or only in memory during a procedure.
Common Problems and How to Avoid Them
- “ActiveX component can’t create object.” This commonly means the Scripting.Dictionary COM object is unavailable or cannot be created in that environment. Confirm you are running Excel on Windows and check the installation or security environment.
- Duplicate-key error. Use
Existsbefore callingAdd, or update the existing item when a repeated key is expected. - Case-insensitive matching is not working. Set
CompareMode = vbTextComparebefore adding any entries, and remember that spaces still differ unless you trim them. - A lookup seems to add an unexpected entry. Reading
dict(key)for a key that is not present can create an empty entry. Test withExistsbefore reading when a missing key should remain missing. - Results appear in an unexpected order. Do not rely on dictionary enumeration order for a sorted report. Sort the keys or write results to a range and sort that range separately.
Also consider the data being read. A cell containing an Excel error value may not convert cleanly with CStr. If the input range might contain errors, test for them with IsError before converting or using the cell as a key. Clear rules for blanks, errors, spaces, and case make dictionary-based macros more reliable.
Mac Support and Early Binding
The example uses the Windows Scripting Runtime COM object. It is not a dependable cross-platform choice for Excel for Mac, where that Windows component may be unavailable. If a workbook must run on both Windows and Mac, test the target Excel versions and consider a VBA Collection, arrays, or a cross-platform implementation instead. Excel for the web does not run VBA macros.
On Windows, early binding is another option: add the Microsoft Scripting Runtime reference under Tools > References in the VBA editor, then declare a variable as Scripting.Dictionary. Early binding can provide editor-assisted members and catch some type issues earlier, but the reference must be available when the project is compiled. Late binding with CreateObject avoids setting that reference manually, though the code still depends on the object being installed and creatable.
Frequently Asked Questions
Is Dictionary built into VBA?
No. VBA uses the Scripting Runtime’s Scripting.Dictionary object. It is commonly used from VBA, but it is not one of VBA’s intrinsic collection types.
Do I need to add a reference to use a VBA Dictionary?
Not when using late binding with CreateObject("Scripting.Dictionary") on a supported Windows installation. Early binding requires selecting the Microsoft Scripting Runtime reference in the VBA editor.
Can a dictionary have duplicate values?
Yes. Keys must be unique, but multiple keys can point to the same item value. If you need repeated keys, store a count or collection under each key instead of adding the same key again.
Is a VBA Dictionary available on Mac?
Do not assume the Windows Scripting Runtime object will be available in Excel for Mac. For a workbook that must run on Mac, test the intended setup and consider a cross-platform alternative such as a Collection or arrays.
What is the difference between a Dictionary and a Collection?
Both can hold groups of items, but a Dictionary has a built-in Exists check and exposes its keys and items. A Collection can be useful for simpler ordered storage, but checking for a key generally takes extra code.
Related
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
- Remove Duplicates in Power Query: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Multiple Items per Key: Practical GuideYes. A VBA Scripting.Dictionary can hold multiple items for one key if you store a container—most often a…
- 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…
- VBA Sort in Two Columns: Code, Steps, and Key OptionsYes. In Excel VBA, sort the complete data range and pass two keys to its Sort method: the…
- VBA Code to Refresh Pivot Tables Automatically in ExcelTo refresh every PivotTable in the workbook that contains your VBA, run a macro that loops through each…
- VBA Loop Only Through Visible Rows?Yes. In Excel VBA, loop through the rows in your data range and check whether each row is…
- VBA Macro Copy and Paste: Methods, Examples, and SafetyIn Excel VBA, copy and paste cells with the Range.Copy method: specify the source range and a destination,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.