A VBA Dictionary is an object that stores pairs of keys and values, letting an Excel macro look up a value by a unique key or quickly check whether that key exists. In Excel for Microsoft 365 on Windows, you can create one with CreateObject("Scripting.Dictionary") and use methods such as Add, Exists, and Remove.
What “VBA coding dictionary” means
The phrase can refer either to a dictionary object used in VBA code or to a reference that explains VBA terms and syntax. Most Excel questions about a “VBA dictionary” mean the object: a convenient in-memory structure for connecting a key—such as a product code—to a value—such as a description or count. This article focuses on that object and also points to places to look up VBA language details.
Think of a dictionary as a set of labeled drawers. A key identifies a drawer, and the item stored in it is the value. For example, the key "P-104" could refer to the value "Desk lamp". Unlike a numbered list, you request an item using its key rather than its position. Each key must be unique within a dictionary, although values can repeat.
This makes dictionaries useful when a macro needs to match records, count repeated entries, remove duplicates, or build a fast lookup table. They are especially handy when the same lookup would otherwise require repeatedly scanning a large range of worksheet cells.
How a Dictionary works
A Dictionary holds data in memory while the VBA procedure runs. It does not automatically create a worksheet table or save its contents into the workbook. Your code must read data into it and, if you want a lasting result, write the values back to a worksheet or another storage location.
Keys are unique. Adding a key that already exists with Add raises an error, so check with Exists first when duplicates are possible. An item can be a number, a string, or another VBA value. A dictionary can also hold object references, but those require object assignment and are a more advanced use.
By default, text keys are compared using binary comparison. In practical terms, "North" and "north" can be treated as different keys. If the lookup should ignore capitalization, set CompareMode to vbTextCompare before adding any keys. Changing the comparison mode after entries have been added can cause an error.
Creating one: early and late binding
There are two common ways to declare a Dictionary in VBA. Late binding uses a general Object variable and creates the object by its registered programmatic identifier. It avoids requiring each workbook user to set a VBA reference, so it is often convenient for distributing a macro on Windows.
Early binding uses a specific Dictionary type and can provide code completion in the Visual Basic Editor. To use it, open the editor, choose Tools > References, and select Microsoft Scripting Runtime if it is available. The exact reference list can vary by Windows installation. A workbook that relies on this reference may need it set on another computer too; a missing reference can produce a “User-defined type not defined” compile error.
Late binding avoids that particular reference dependency, but it does not make the Windows Scripting.Dictionary component available on every operating system. The choice is about how VBA connects to the object, not about making a Windows COM component universally portable.
Core properties and methods
These are the Dictionary members most Excel macros use:
Add key, iteminserts a new key and its value. It errors if that key is already present.Exists(key)returns True if the key is present, making it useful before reading or adding an entry.Item(key)retrieves or sets the value associated with a key. In VBA, assigning to an item for a key that is not present can add that key, but usingAddor an explicit existence check can make the intended behavior clearer.Countgives the number of keys currently stored.Keysreturns the keys as an array, andItemsreturns the corresponding values as an array.Remove(key)deletes one entry;RemoveAllclears the dictionary.
You can loop through the keys with a For Each loop. The key is the useful thing to iterate over when you need to retrieve its associated value. If you need to preserve a particular order, do not assume a Dictionary will provide the sorted order you want; sort the data separately or use a structure designed for ordered data.
Example: count repeated values in a column
This example reads names from column A on a worksheet named Data, starting in row 2. It counts each nonblank name, ignoring differences in capitalization, and prints the results in the Visual Basic Editor’s Immediate window. The Immediate window is available in the editor through View > Immediate Window, or with Ctrl+G on Windows. The code assumes the worksheet exists and that column A contains ordinary text or numbers rather than values requiring special error handling.
Before running any macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z. This example only reads worksheet data and prints results in the editor, but the reminder matters particularly for macros that write to or clear cells.
Option Explicit
Sub CountNamesWithDictionary()
Dim ws As Worksheet
Dim dict As Object
Dim lastRow As Long
Dim r As Long
Dim i As Long
Dim key As String
Dim keys As Variant
Dim counts As Variant
' Read the source list from the Data worksheet.
Set ws = ThisWorkbook.Worksheets("Data")
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
' Create a late-bound Dictionary and ignore letter case.
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
For r = 2 To lastRow
If Not IsError(ws.Cells(r, "A").Value) Then
key = Trim$(CStr(ws.Cells(r, "A").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
End If
Next r
' Print each unique name and its count in the Immediate window.
keys = dict.Keys
counts = dict.Items
For i = 0 To dict.Count - 1
Debug.Print CStr(keys(i)) & vbTab & CStr(counts(i))
Next i
End Sub
For example, if A2:A5 contains Alex, Sam, Alex, and Jo, the Immediate window shows three entries: Alex with 2, Sam with 1, and Jo with 1. Because the comparison mode is text-based, entries such as Alex and ALEX are counted together. Blank cells are skipped, and cells containing Excel errors are skipped rather than converted to text.
The key and count arrays returned by Keys and Items line up by position, which is why the loop can display each key with its matching count. If you want a reusable worksheet report instead, write those array values to a clearly chosen output range. Check that the destination is empty first, so the macro does not overwrite information you need.
Dictionary versus Collection and worksheet lookups
VBA also has a built-in Collection. A Collection can store items and can use keys, but it does not offer the same direct Exists method. Checking whether a keyed item exists in a Collection often requires error handling or a separate tracking structure. A Dictionary is usually simpler when the task depends on frequent membership checks or updating counts by key.
A worksheet lookup formula or Excel table may be a better fit when the result should remain visible, recalculate as data changes, or be understood by people who do not use VBA. A Dictionary is most useful for work performed inside a macro, particularly when processing many rows without repeatedly searching the sheet. It is not a replacement for a database or a sorted table when you need persistent records, a specified order, or shared access.
Common errors and how to avoid them
- Duplicate key error:
Addcannot add a key that is already present. Test withExists, then update the existing item or choose another key. - Wrong capitalization behavior: Set
CompareModebefore adding entries if letter case should not distinguish keys. Also consider trimming leading and trailing spaces; otherwise"Sam"and" Sam "are different keys. - Missing reference: Early-bound code can fail to compile if Microsoft Scripting Runtime is not selected on that computer. Late binding can avoid a reference setting, but still requires the component to be available.
- Type mismatch in a key: Normalize values before using them as keys. For example, decide whether numeric identifiers should be treated as numbers or text, and keep that choice consistent. A cell containing an Excel error also needs to be handled before converting its value with
CStr. - Unexpectedly empty results: Check the worksheet name, header row, and last-row calculation. In the example, data begins in row 2 and the last used row is detected using column A.
- Assuming the data is saved: A Dictionary is temporary in-memory data. It disappears when the procedure ends unless the macro writes its contents somewhere persistent.
Windows, Mac, and macro safety
The Scripting.Dictionary object is a Windows scripting component created through COM. Late binding does not remove that platform dependency, and Microsoft Scripting Runtime is not generally available as the same component in Excel for Mac. A workbook that uses it may therefore fail on a Mac even if the code compiles and runs on Windows. Excel for the web cannot run VBA macros.
If the workbook must work on both Windows and Mac, consider a VBA Collection for simpler keyed storage, a worksheet-based lookup table, or a custom class or routine that implements the needed behavior without relying on a Windows-only component. Test the chosen approach in the actual Excel versions and operating systems your users have. Also treat macros from untrusted sources cautiously: enable or run code only when you understand its source and purpose, and keep a backup before macros that edit workbook contents.
For a language reference rather than a data structure, Microsoft’s VBA documentation explains syntax and object-library members. In the Visual Basic Editor, the Object Browser can also help inspect available objects and members; select an item and use F2 on Windows to open it. The Object Browser describes what a member does, while a Dictionary itself is a runtime object used by your program.
Frequently Asked Questions
Is Dictionary built into VBA?
VBA code can use the Dictionary object through the Microsoft Scripting Runtime on Windows. It is not the same as a core VBA language type such as String, and its availability depends on the scripting component and how the project references or creates it.
Do Dictionary keys have to be unique?
Yes. A key identifies one entry, so adding the same key again with Add causes an error. Use Exists to check first, then update the existing item if that is the intended behavior.
How do I make Dictionary lookups case-insensitive?
Set CompareMode to vbTextCompare immediately after creating the Dictionary and before adding any keys. You can also normalize text yourself, but comparison mode is usually more direct when only capitalization should be ignored.
Can I use a VBA Dictionary on Mac?
Do not assume so. Scripting.Dictionary relies on a Windows COM component that is not generally available in Excel for Mac. For a workbook that must run on both platforms, use a cross-platform design such as a Collection or worksheet lookup and test it on Mac.
How do I get all the keys or values?
Use the Dictionary’s Keys or Items property, which returns an array. The two arrays correspond by position, so an item at a given index belongs to the key at that same index.
Related
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Microsoft Excel VBA For Loops: Syntax and ExamplesIn Microsoft Excel VBA, a For...Next loop repeats instructions a set number of times, while For Each...Next repeats…
- Round Up in Excel VBA: Methods, Rules, and ExamplesTo round numbers up in Excel VBA using Excel’s familiar “away from zero” rule, call Application.WorksheetFunction.RoundUp(number, num_digits). For…
- VBA Function That Returns an Array: Examples and TipsIn VBA, declare a function that returns an array as As Variant, then assign an array to the…
- Excel VBA ComboBox: Load a UserForm List From RangeTo fill an Excel VBA UserForm ComboBox from cells, set its RowSource property to a worksheet range, or…
- Excel UserForm TextBox: How to Wrap TextTo wrap text in an Excel UserForm TextBox, set its MultiLine property to True and its WordWrap property…
- How to Highlight Duplicates in Excel VBA with a MacroTo highlight duplicate values with VBA, select the cells you want to check, run a macro that counts…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.