VBA Dictionary Functions and How to Use Them

VBA & macros · Updated November 18, 2026

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, item inserts a new key-value pair. It raises an error if that key already exists.
  • Exists(key) returns True if the key is present and False otherwise. It is the usual check before adding a key with Add.
  • Item(key) gets or sets the value for a key. It can also be written with parentheses after the dictionary name, such as dict("East").
  • Keys returns an array containing the dictionary’s keys; Items returns an array of corresponding values.
  • Remove key deletes one key and its value. RemoveAll clears every pair but leaves the dictionary object available for reuse.
  • Count reports how many pairs are currently stored.
  • CompareMode controls how string keys are compared. Set it before adding entries. vbTextCompare treats letter case as insignificant; the default binary comparison distinguishes case.
  • Key(oldKey) = newKey changes 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 Add for an existing key raises an error. Check with Exists first, or update the existing item instead.
  • Unexpected case matching: Choose CompareMode deliberately 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 Item can create it. Test with Exists before retrieving when absence has meaning.
  • Object values require object assignment: When storing an object rather than a number or string, use VBA’s Set statement 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.