In VBA, a Dictionary is an object for storing unique keys alongside values, and its main functions are the Add, Exists, Item, Keys, Items, Remove, and RemoveAll methods or properties. It is not a worksheet formula: you use it in a macro to look up, count, or organize values efficiently.
What a VBA Dictionary Is
A Dictionary keeps pairs such as a product code and its description, or a name and a running total. Each key must be unique within the dictionary; the value associated with it can be a number, text, a date, or an object. This key-based structure makes a dictionary useful when a macro repeatedly needs to ask, “Have I seen this value?” or retrieve information associated with it.
VBA’s Dictionary object is supplied by the Microsoft Scripting Runtime. In common Excel VBA code on Windows, it is created with CreateObject("Scripting.Dictionary"). You can then call its methods and read or set its properties. The names sometimes get called “dictionary functions,” but technically they are object methods and properties, not built-in VBA functions like Len or InStr.
For instance, a macro can use customer IDs as keys and order totals as values. When another row contains an ID already in the dictionary, the macro can add to that ID’s existing total instead of searching all earlier rows again.
Main Methods and Properties
Add key, iteminserts a new key-value pair. It raises an error if that key already exists.Exists(key)returnsTrueif the key is present andFalseotherwise. It is the usual check before adding a key withAdd.Item(key)gets or sets the value for a key. It can also be written with parentheses after the dictionary name, such asdict("East").Keysreturns an array containing the dictionary’s keys;Itemsreturns an array of corresponding values.Remove keydeletes one key and its value.RemoveAllclears every pair but leaves the dictionary object available for reuse.Countreports how many pairs are currently stored.CompareModecontrols how string keys are compared. Set it before adding entries.vbTextComparetreats letter case as insignificant; the default binary comparison distinguishes case.Key(oldKey) = newKeychanges a key while retaining its associated value. The replacement must not conflict with another existing key.
Keys are unique, but values do not have to be: several keys can point to the same value. A key can be a string or another supported non-array value. In everyday Excel automation, strings and numbers are the most straightforward choices.
Create and Use a Dictionary
This Windows example uses late binding, so it does not require selecting a reference in the VBA editor. The dictionary is created at run time, and the text comparison setting is applied before any keys are added.
Save a copy of the workbook before running a macro. VBA actions cannot be reversed with Ctrl+Z.
Sub DictionaryBasics()
Dim dict As Object
' Create a late-bound Dictionary.
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict.Add "North", 120
If dict.Exists("north") Then
Debug.Print dict.Item("NORTH")
End If
End Sub
Because text comparison is enabled, the lookup succeeds even though the capitalization differs. The example prints 120 in the Immediate window, which you can open in the VBA editor with Ctrl+G on Windows. On a Mac, use the equivalent VBA editor command or menu for the Immediate window; the Windows ActiveX-based creation method shown here is not a portable Mac solution.
Check Before Adding or Updating
There are two common ways to put data into a dictionary. Use Add when a duplicate should be treated as a problem, and check Exists first when duplicates are expected. To update a value, assign to the item for the key.
A subtle behavior matters here: reading or assigning an Item for a key that is not present can create that key with an empty value before the assignment. Therefore, use Exists when you need to distinguish a missing key from a key whose value is empty or zero. Do not assume that simply asking for an item is a harmless lookup.
The following pattern counts nonblank entries in column A. It uses the cell text converted to a string as the key and stores a count as the value.
Save a copy of the workbook before running this macro; its actions cannot be undone with Ctrl+Z.
Sub CountValuesInColumnA()
Dim ws As Worksheet
Dim dict As Object
Dim cell As Range
Dim lastRow As Long
Dim key As String
' Count nonblank values in the active sheet's column A.
Set ws = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow >= 2 Then
For Each cell In ws.Range("A2:A" & lastRow).Cells
If Len(cell.Value2) > 0 Then
key = CStr(cell.Value2)
If dict.Exists(key) Then
dict.Item(key) = dict.Item(key) + 1
Else
dict.Add key, 1
End If
End If
Next cell
End If
For Each cell In ws.Range("A2:A" & lastRow).Cells
If Len(cell.Value2) > 0 Then
key = CStr(cell.Value2)
If dict.Exists(key) Then Debug.Print key, dict.Item(key)
End If
Next cell
End Sub
The Immediate window may show the same key more than once because the final loop visits the source cells, not the dictionary keys. To print each key once, loop through dict.Keys instead. If the column has no data below the header, the first loop is skipped; in a production macro, you can also guard the output loop with the same lastRow >= 2 condition.
Loop Through Keys and Values
Use For Each to visit the keys, then retrieve each matching item. The key variable should be declared as a Variant, since Keys returns an array and keys are not necessarily strings. In this example, assume the dictionary has already been populated.
Sub PrintDictionaryPairs()
Dim dict As Object
Dim key As Variant
' Build a small example dictionary.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "A12", "Pencils"
dict.Add "B05", "Folders"
For Each key In dict.Keys
Debug.Print key, dict.Item(key)
Next key
End Sub
The order in which a dictionary returns its keys should not be used as a sorting rule. If output must be alphabetical or follow a specific business order, copy the keys into a range or array and sort them explicitly. The Keys and Items arrays correspond by position, but they are snapshots; changing the dictionary later does not turn an earlier returned array into a live view.
Dictionary Compared with Collection and Worksheet Lookups
A VBA Collection also stores items and can use keys, but it does not provide a built-in Exists method or a direct Keys array property. Code often has to handle a missing collection key through error handling. A Dictionary is usually more convenient when the macro needs frequent membership tests, updates, or access to all keys.
For a small, fixed list, an array or a Collection may be enough. For data already laid out in a worksheet, a worksheet lookup formula or Excel’s built-in tools may be simpler, especially if users need to see and maintain the results. A Dictionary is particularly useful for in-memory processing: for example, building a unique list, tallying repeated values, or joining two tables by an ID within a macro. It does not replace a worksheet table or provide spreadsheet recalculation by itself.
Common Errors and Practical Limits
- Duplicate key error: Calling
Addfor an existing key raises an error. Check withExistsfirst, or update the existing item instead. - Unexpected case matching: Choose
CompareModedeliberately and set it before inserting data. Changing it after keys have been added can raise an error. - Missing key appears to exist later: Accessing a missing
Itemcan create it. Test withExistsbefore retrieving when absence has meaning. - Object values require object assignment: When storing an object rather than a number or string, use VBA’s
Setstatement for the item assignment. A normal value assignment is for non-object values. - Unexpected key formatting: Values such as numeric IDs can be converted to strings inconsistently if the source contains mixed types or formatting. Normalize keys intentionally, for example with
CStr, and decide how blanks should be handled.
For a large macro, also consider where data is read and written. Reading cells one at a time can be slower than loading a range into a Variant array, processing values in memory with a Dictionary, and writing results back in a batch. The Dictionary can speed up repeated lookups, but it does not make worksheet cell access itself instantaneous.
Windows, Mac, and Binding Choices
On Windows, late binding with CreateObject("Scripting.Dictionary") avoids a project reference and is convenient when sharing a workbook among Windows users. Early binding is another Windows option: in the VBA editor, select Tools > References, enable Microsoft Scripting Runtime, and declare a variable as Scripting.Dictionary. Early binding can provide editor autocomplete and compile-time type information, but another user’s project must be able to resolve the reference.
The Scripting Runtime is a Windows COM component, so code depending on it should not be assumed to run unchanged in Excel for Mac or Excel for the web. Excel for the web cannot run VBA macros. For a workbook that must work across platforms, test on the target Excel versions and consider a platform-neutral implementation using arrays and custom lookup logic, or use Office Scripts where that fits the task. Those alternatives do not provide the same Dictionary API automatically.
Frequently Asked Questions
Is Dictionary a built-in VBA function?
No. A Dictionary is an object provided by the Microsoft Scripting Runtime. Its operations, such as Add and Exists, are methods or properties of that object, rather than standalone VBA functions.
How do I check whether a Dictionary key exists?
Call dict.Exists(key). It returns True or False and does not require retrieving the item. This is safer than reading dict.Item(key) when a missing key must remain missing.
How do I get all Dictionary keys?
Use dict.Keys, which returns an array of keys. You can assign that result to a Variant variable or loop through it with a Variant control variable. Use dict.Items to get the associated values.
Can Dictionary keys ignore uppercase and lowercase?
Yes. Set CompareMode to vbTextCompare before adding keys. With that setting, keys such as "CodeA" and "codea" are treated as the same key; the default binary comparison treats their letter case as different.
Why does CreateObject fail?
On Windows, a failure can indicate that the Scripting Runtime component is unavailable or cannot be created in that environment. The approach is also not a general Mac or web solution: Excel for the web does not run VBA, and Windows COM dependencies should not be presumed to work on Mac.
Related
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA: Clear an AutoFilter If It Exists SafelyYes. In Excel VBA, check whether the worksheet is currently filtered with Worksheet.FilterMode, then call ShowAllData only when…
- Close MsgBox VBA: Reliable Ways to Dismiss a DialogTo close a VBA MsgBox, click one of its buttons or use a keyboard action the dialog supports;…
- VBA Function Arguments: Types, Defaults, and ExamplesVBA function arguments are the values or variables supplied when a function is called; the function’s parameter list…
- VBA Convert String to Date: Safe Parsing in Excel MacrosIn VBA, use CDate to convert a string that VBA recognizes as a date, or DateValue when you…
- Access VBA InputBox: Ask for User Input in a MacroYes. In Access VBA, call the built-in InputBox function to display a small dialog and collect a text…
- Access VBA: Find a Character in a StringIn Access VBA, use InStr to find a character or substring: it returns the character’s 1-based position, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.