VBA Dictionary: How to Create and Use One

VBA & macros · Updated November 18, 2026

A VBA Dictionary stores pairs of unique keys and associated values, letting you look up, count, or group data by a key. In Excel for Microsoft 365 on Windows, you can create one with CreateObject("Scripting.Dictionary"), then use methods such as Add and Exists to work with its contents.

What a VBA Dictionary Is

A dictionary is an in-memory data structure: it holds information while your macro runs, rather than writing it to a worksheet automatically. Each entry has a key and an item (its value). For example, a key might be a product code and its item the product name, or a customer name and the number of orders associated with that name.

Keys must be unique. If a macro tries to add a second entry with a key that already exists, the dictionary raises an error. That behavior is useful when you need one result per label, but it means you should check for an existing key before adding it if duplicates are possible.

Unlike looking up a value by scanning a worksheet range, a dictionary lets code ask whether a key exists and retrieve the associated item directly. This makes dictionaries especially useful for deduplicating lists, counting categories, matching two datasets, and keeping track of values already encountered.

Creating a Dictionary in Excel VBA

For a Windows macro that does not require a manually selected reference, use late binding with CreateObject. The variable is declared as an Object, and Windows creates the Dictionary at runtime:

Sub MakeDictionaryExample()
    Dim dict As Object

    ' Create a late-bound Scripting.Dictionary on Windows.
    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "A104", "Desk lamp"
    dict.Add "B208", "Notebook"

    If dict.Exists("A104") Then
        MsgBox dict("A104")
    End If
End Sub

This example displays “Desk lamp” because the key A104 has that item. The expression dict("A104") accesses the item’s value; it does not refer to a worksheet cell. A dictionary is separate from the workbook until your code reads from it or writes its contents to a range.

Late binding is convenient for sharing a Windows workbook because users do not have to enable a particular library reference. The trade-off is that the VBA editor cannot provide the same type-specific help and checking it can provide for an early-bound object.

Late Binding and Early Binding

With early binding, you add the Microsoft Scripting Runtime reference in the Visual Basic Editor using Tools > References, then declare the variable as Scripting.Dictionary. The library must be available and its reference must resolve on the computer running the macro.

Sub MakeEarlyBoundDictionary()
    Dim dict As Scripting.Dictionary

    ' Early binding requires the Microsoft Scripting Runtime reference.
    Set dict = New Scripting.Dictionary
    dict.Add "A104", "Desk lamp"
End Sub

Early binding gives the editor more information about the object’s members and can make it easier to discover methods while coding. Late binding avoids a project reference, which can reduce reference-related problems when a Windows workbook is moved between computers. Neither approach makes the Windows Scripting Runtime universally available on other platforms.

Excel for the web does not run VBA macros. On a Mac, VBA is available in desktop Excel, but Scripting.Dictionary relies on Windows COM automation and should not be treated as a portable Mac solution. If a workbook must run on both Windows and Mac, consider a VBA Collection, arrays, or a cross-platform design, and verify the chosen approach on the actual target versions.

Adding, Finding, and Updating Entries

The most-used Dictionary members are straightforward. Add creates a new key-item pair, Exists checks whether a key is present, and the default Item property reads or assigns the value for a key. Remove deletes one entry, while RemoveAll empties the dictionary.

  • dict.Add key, item adds a new pair and fails if the key already exists.
  • dict.Exists(key) returns True or False without requiring you to search through every entry.
  • dict(key) reads or sets the item associated with a key. Assigning through this property can create a missing key, so use Add when you want duplicate keys to be caught.
  • dict.Count reports the number of entries currently stored.
  • dict.Keys and dict.Items return arrays of the keys and items, respectively.

A common counting pattern checks for the key first. If the key exists, increase its stored number; otherwise, create it with an initial count of one. This avoids the duplicate-key error and turns a list of repeated labels into a set of counts.

Example: Count Repeated Labels in a Worksheet

The following macro assumes that row 1 contains a heading, labels are in column A of a worksheet named Sheet1, and the output area in columns D:E is available. It writes each distinct nonblank label and its count. It uses a case-insensitive comparison, so “East” and “east” are counted together.

Before running any macro, save a copy of the workbook. A VBA macro’s changes cannot be reversed with Ctrl+Z. The code below writes to columns D:E, so change the output columns or confirm that those cells can be overwritten before you run it.

Sub CountLabels()
    Dim ws As Worksheet
    Dim dict As Object
    Dim lastRow As Long
    Dim r As Long
    Dim outputRow As Long
    Dim label As String
    Dim key As Variant

    ' Use the worksheet that contains the source labels.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set dict = CreateObject("Scripting.Dictionary")

    ' Set comparison behavior before adding any keys.
    dict.CompareMode = vbTextCompare
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Count each nonblank, non-error label in column A.
    For r = 2 To lastRow
        If Not IsError(ws.Cells(r, "A").Value) Then
            label = CStr(ws.Cells(r, "A").Value)
            If Len(label) > 0 Then
                If dict.Exists(label) Then
                    dict(label) = dict(label) + 1
                Else
                    dict.Add label, 1
                End If
            End If
        End If
    Next r

    ' Write the keys and their counts to columns D and E.
    outputRow = 2
    For Each key In dict.Keys
        ws.Cells(outputRow, "D").Value = key
        ws.Cells(outputRow, "E").Value = dict(key)
        outputRow = outputRow + 1
    Next key
End Sub

If column A contains Apple, Pear, Apple, the output has Apple with a count of 2 and Pear with a count of 1. The code skips cells containing Excel errors and cells whose text length is zero. A cell containing spaces is not an empty string, however; trim the label with Trim$(CStr(...)) if leading or trailing spaces should be ignored, taking care to handle error values first as the example does.

Keys, Case Sensitivity, and Comparison Rules

By default, text keys are compared in a case-sensitive way. Set CompareMode to vbTextCompare before adding entries if “North” and “north” should be treated as the same key. Use vbBinaryCompare for binary comparison. Setting the comparison mode after adding keys can raise an error, so decide the rule immediately after creating the dictionary.

Keys can represent different kinds of data, but consistent key values matter. For example, the number 12 and the text string “12” may not behave as the same key in every context. If the source data mixes types, normalize it before using it as a key. Similarly, decide whether spaces, punctuation, and capitalization are significant; those choices determine whether two labels count as the same entry.

Dictionary iteration with For Each key In dict.Keys is useful for processing all entries. Do not rely on the order as a sorting rule. If results need to appear alphabetically or in a defined sequence, sort the keys separately or write them to a range and sort that range.

Common Problems and How to Fix Them

  • “ActiveX component can’t create object.” The Windows Scripting Runtime object could not be created. Check that the macro is running in desktop Excel on Windows and that the workbook is not being run in Excel for the web or in an environment without the required COM support.
  • “This key is already associated with an element.” The code called Add for a key already in the dictionary. Check with Exists first, or decide whether the existing item should be updated instead.
  • Results differ only by capitalization. Set CompareMode before adding keys if text comparison should ignore case. Review spaces and inconsistent data types as well.
  • Output appears in an unexpected order. A dictionary is for key-based storage, not guaranteed sorting. Apply a separate sort when order matters.
  • The macro reports a missing worksheet. The example expects a sheet named exactly Sheet1. Change that string to match the workbook tab name, or add error handling if sheet names vary.

For security, run macros only from workbooks and sources you trust. Do not enable all macros just to make one dictionary example work; use Excel’s macro security settings and follow your organization’s rules. A dictionary itself is only a data structure, but the VBA procedure using it can also change cells, files, or other resources.

Dictionary Versus Collection or Worksheet Lookup

A VBA Collection is a built-in alternative that can store items and optionally associate them with keys. It does not offer the same direct Exists method, so checking for a key often requires error handling or another structure. A Collection may be suitable when portability and simple ordered storage matter more than convenient key-existence checks.

For a modest dataset and a formula-based workbook, worksheet functions or a lookup table may be easier for other users to inspect and maintain. A Dictionary is more appropriate when a macro needs to build groups or counts in memory, repeatedly check whether labels have been seen, or match records as part of a larger VBA process. There is no need to use VBA just because a Dictionary exists; choose the approach that fits the workbook’s users and platform requirements.

Frequently Asked Questions

How do I create a dictionary in VBA?
On Windows, declare an object and use Set dict = CreateObject("Scripting.Dictionary"). Add entries with dict.Add key, item. This COM-based approach is not a portable choice for Mac or Excel for the web.

How do I check whether a VBA Dictionary contains a key?
Use If dict.Exists(key) Then. The method returns True when that key is present and False otherwise, making it useful before adding or updating an entry.

Can a VBA Dictionary have duplicate keys?
No. Each key identifies one entry. Use a different key, update the existing item’s value, or store multiple values inside one item if the design calls for several records under one key.

How do I make dictionary keys case-insensitive?
Set dict.CompareMode = vbTextCompare immediately after creating the dictionary and before adding keys. This makes text comparisons ignore letter case.

Does Scripting.Dictionary work in Excel for Mac?
It depends on Windows COM automation, so it is not a reliable cross-platform choice for Mac. Use a Collection or another design that is supported by the Excel versions where the workbook must run, and test it there.

Can I use a VBA Dictionary in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in a supported desktop version of Excel to run VBA, or use a non-VBA approach for browser-based workflows.

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.