VBA Dictionary Example: Create, Find, and Count Items

VBA & macros · Updated November 18, 2026

A VBA dictionary stores unique keys with associated values, making it useful for counting repeated entries or looking up information by name. In Excel for Microsoft 365 on Windows, create one with CreateObject("Scripting.Dictionary"); the example below counts each nonblank name in column A and writes the totals to a Results worksheet.

A VBA Dictionary Example

Set up a worksheet named Data with names in cells A2 downward, and a worksheet named Results for the output. The macro treats names that differ only by letter case as the same, trims leading and trailing spaces, and skips blank cells. It writes each distinct name and its count in columns A and B of Results.

Sub CountNamesWithDictionary()
    ' Count nonblank names in Data column A and write totals to Results.
    Dim dict As Object
    Dim ws As Worksheet
    Dim resultWs As Worksheet
    Dim cell As Range
    Dim item As Variant
    Dim key As String
    Dim lastRow As Long
    Dim outputRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    Set resultWs = ThisWorkbook.Worksheets("Results")
    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
    If lastRow < 2 Then
        MsgBox "No names were found in Data column A."
        Exit Sub
    End If

    For Each cell In ws.Range("A2:A" & lastRow).Cells
        If Not IsError(cell.Value) Then
            key = Trim$(CStr(cell.Value2))
            If Len(key) > 0 Then
                If dict.Exists(key) Then
                    dict(key) = dict(key) + 1
                Else
                    dict.Add key, 1
                End If
            End If
        End If
    Next cell

    resultWs.Range("A1").Value = "Name"
    resultWs.Range("B1").Value = "Count"
    outputRow = 2

    For Each item In dict.Keys
        resultWs.Cells(outputRow, 1).Value = item
        resultWs.Cells(outputRow, 2).Value = dict(item)
        outputRow = outputRow + 1
    Next item
End Sub

Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. To run this example in Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, then run the procedure with F5 while the cursor is inside it. On a Mac, Option+F11 (or Fn+Option+F11, depending on the keyboard) opens the editor; Mac keyboard layouts and settings can affect the shortcut. The code’s Scripting.Dictionary dependency is intended for Windows, not Mac.

What a Dictionary Stores

A dictionary is a collection of key-and-value pairs. A key identifies an entry, and its associated item holds the information you want to store. In the example, a person’s name is the key and the number of times that name appears is the item. Each distinct key occurs once, even if the source contains that name many times.

This structure is different from a plain list, where the same value can appear repeatedly without being combined. It is also different from a worksheet lookup formula: a dictionary exists in VBA memory while the macro runs, unless your code writes its contents to cells or saves them elsewhere. That makes it useful for building a quick in-memory index, grouping records, or checking whether a value has already appeared.

Dictionaries are often used when a macro must repeatedly search a large set of values. Rather than scan all earlier rows each time it encounters a name, code can check whether a key exists and retrieve its item. The practical benefit depends on the size and shape of the task, but this key-based approach is generally a natural fit for repeated lookups and counting.

How the Example Works

CreateObject("Scripting.Dictionary") creates a dictionary object at runtime. The variable is declared As Object, so VBA does not need a compile-time reference to the Scripting Runtime library. This is called late binding. The worksheet variables point to the sheets named Data and Results in the workbook containing the macro, and the last-row calculation finds the last used cell in column A.

The loop examines each cell from A2 to that last row. It ignores Excel error values, converts other cell values to text, and trims spaces around the text. The blank check keeps an empty string from becoming a key. When the key is already present, the macro adds one to its stored count; otherwise, it creates the key with an initial count of one.

Finally, the code writes headers and loops through the dictionary’s keys, placing each key and its associated count into the Results sheet. Dictionary key order should not be treated as a sorting guarantee. If alphabetical output matters, sort the resulting worksheet range after the macro runs or add explicit sorting logic.

Useful Dictionary Properties and Methods

The example uses a small set of members that cover many common tasks:

  • Add key, item creates a new pair. It raises an error if the key already exists.
  • Exists(key) returns True when that key is present, which is why the counting loop checks before adding.
  • dict(key) retrieves or assigns the item for a key. In the example, assigning a larger number updates the count.
  • Count returns the number of distinct keys, not the total number of source rows.
  • Keys and Items return arrays of keys and their associated values, useful for looping or transferring data.
  • Remove key deletes one entry, while RemoveAll empties the dictionary.

For example, after the sample macro processes rows containing Ana, Ben, Ana, and a blank, the dictionary has two keys: Ana with a count of 2 and Ben with a count of 1. Its Count is 2, while the sum of the stored counts is 3. Keeping those two meanings separate prevents a common reporting mistake.

Choosing How to Compare Keys

The CompareMode property controls how string keys are compared. The sample sets it to vbTextCompare, so Ana and ANA are treated as the same key. With vbBinaryCompare, the comparison is case-sensitive, and those spellings can be separate keys. The default behavior is binary comparison unless the code changes it.

Set CompareMode before adding any entries. Changing it after the dictionary contains data can cause a runtime error. Case-insensitive comparison does not remove spaces or standardize punctuation, which is why the sample separately uses Trim$. If values may contain inconsistent internal spaces, punctuation, or other formatting, define a normalization rule that suits the data before using each value as a key.

Normalization changes what counts as a match, so apply it deliberately. For instance, trimming outer spaces can be sensible for names copied from a worksheet, but removing punctuation could incorrectly combine distinct product codes. If codes can include leading zeroes, keep them as text in the worksheet and in VBA; converting them to numbers can change their identity.

Late Binding and Early Binding

Late binding, as used above, creates the object with CreateObject and declares it as a generic Object. It avoids requiring each user to enable a library reference in the VBA editor, which can reduce missing-reference problems when a workbook is shared among Windows users. The trade-off is that the editor provides less specific autocomplete and compile-time checking for dictionary members.

With early binding on Windows, open the VBA editor and choose Tools > References, then select Microsoft Scripting Runtime if it is available. The code can then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can make the object’s members easier to discover while writing code, but a workbook may fail to compile on a machine where that reference is unavailable or broken. For a shared workbook, consider the recipients’ environment before choosing.

Common Problems and Safe Use

  • “ActiveX component can’t create object”: The Scripting.Dictionary object is a Windows COM component. Check that the macro is running in desktop Excel on Windows, rather than Excel for the web or an unsupported environment.
  • Duplicate-key error: Calling Add for a key that already exists raises an error. Check with Exists first, or assign an item deliberately when updating or creating a key.
  • Unexpected separate entries: Case, spaces, punctuation, and data types can affect comparisons. Choose a comparison mode and normalize input consistently.
  • Missing worksheet error: The example expects sheets named exactly Data and Results in the workbook that contains the macro. Rename the sheets or update the worksheet names in the code.
  • Old results remain below new output: The code overwrites the current output rows but does not erase unused rows left by a previous, longer result. Use a dedicated Results sheet and clear only the previous output area if that behavior is desired.

Macros do not run in Excel for the web, and the Scripting.Dictionary approach is not a cross-platform solution for Mac Excel. If the workbook must run on a Mac, avoid assuming that a Windows COM object is available; consider a VBA Collection or a platform-independent approach that fits the required operations. Do not enable macros in workbooks from untrusted sources, and save a separate copy before running code that changes workbook contents.

Dictionary Compared With Collection and Worksheet Tools

A VBA Collection can store items and can use keys, so it is a built-in alternative for some tasks. However, dictionaries provide convenient operations such as Exists, Keys, and Items, and are especially straightforward for checking membership and maintaining counts. A Collection may suit simple ordered storage, but the choice depends on whether the code needs dictionary-specific lookup and comparison behavior.

Worksheet features may be a better choice when the goal is visible, refreshable analysis rather than VBA automation. A PivotTable can summarize repeated categories, and Excel formulas can produce counts in a worksheet without a macro. A dictionary is useful when the result is an intermediate step in a larger procedure, such as matching records, validating entries, or creating a custom report. It is not automatically better than a formula or PivotTable; choose the tool that makes the result easiest to maintain.

Frequently Asked Questions

How do I check whether a key exists in a VBA dictionary?
Call dict.Exists(key). It returns True if the key is present and False otherwise. Use that check before calling Add when duplicate keys are possible.

How do I get the number of entries in a dictionary?
Use dict.Count. It counts distinct keys. If each item stores a frequency, add the item values to get the total number of counted records.

Is a VBA dictionary case-sensitive?
By default, string comparison is binary and case-sensitive. Set dict.CompareMode = vbTextCompare before adding keys if capitalization should not distinguish entries.

Can I use Scripting.Dictionary in Excel for Mac?
The Windows Scripting.Dictionary COM object is not a dependable cross-platform choice for Mac Excel. For a workbook that must run on both platforms, consider a Collection or another approach and verify that it supports the operations your code needs.

Why does the macro say it cannot create the object?
CreateObject("Scripting.Dictionary") depends on the Windows Scripting Runtime COM component. The error can occur in Excel for Mac, Excel for the web, or another environment that does not provide that component.

Can a dictionary sort its keys?
Do not rely on dictionary key order for sorted output. Write the keys and items to a worksheet and sort that range, or implement explicit sorting in VBA when the output order is important.

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.