VBA Dictionary Documentation: Setup, Methods, Properties & Examples

VBA & macros · Updated November 18, 2026

For VBA Dictionary documentation, start with Microsoft’s Dictionary object reference: it describes the Scripting.Dictionary object, which stores unique keys and their associated values. In Excel VBA on Windows, you can use it by adding the Microsoft Scripting Runtime reference or by creating the object with late binding; the example below shows a late-bound approach.

What a VBA Dictionary Is

A Dictionary is a key-and-value collection. Each key identifies one item, and the item can hold a value such as text, a number, a date, or an object reference. Unlike a VBA Collection, a dictionary provides a direct way to check whether a key exists and to retrieve its associated item. This makes it useful for looking up records, counting repeated values, removing duplicates, and grouping data.

Keys must be unique within a dictionary. For example, a customer ID can be the key and that customer’s name or total order count can be the item. If the same key appears again, you can either update its existing item or decide to skip it. A dictionary does not automatically decide how duplicates should be handled; your code defines that behavior.

Microsoft’s Dictionary object reference documents the object’s properties and methods. The reference is part of Microsoft’s language documentation for the scripting runtime, rather than a separate Excel worksheet-function guide. It is still the core object documentation VBA developers generally need.

How to Make the Dictionary Available

There are two common ways to create a Dictionary in Excel VBA. The main difference is whether the project connects to the library when you write and compile the code or waits until it runs.

  • Early binding: In the Visual Basic Editor, select Tools > References and check Microsoft Scripting Runtime. You can then declare a variable as a Scripting.Dictionary. This enables compile-time checking and editor assistance for its members. The reference must be available on any computer that runs the workbook.
  • Late binding: Declare the variable as Object and create it with CreateObject using the Scripting.Dictionary program identifier. This avoids setting a project reference and is often more portable between Windows computers. The object’s members are resolved at run time, so editor assistance and some compile-time checks are not available.

For a Windows-only workbook, either approach can be appropriate. Early binding is convenient while developing; late binding can reduce missing-reference problems when sharing a file across Windows installations. Late binding does not make the Windows scripting component available on platforms that do not support it.

Excel for Mac does not support Windows COM automation in the same way as Excel for Windows, so Scripting.Dictionary and CreateObject are not a dependable cross-platform choice. If a workbook must run on both Windows and Mac, test the exact target versions and consider a VBA Collection or an array-based approach instead. Excel for the web does not run VBA macros.

Core Properties and Methods

The Dictionary object is small, but its members cover the usual tasks. Microsoft’s reference is the authority for detailed syntax and behavior; these are the members most useful in everyday Excel VBA:

  • Add inserts a key and its item. It raises an error if the key already exists, so check first when duplicates are expected.
  • Exists returns True when a key is present and False otherwise. It is the safest way to test membership before retrieving an item.
  • Item reads or changes the value associated with a key. Assigning an item can create an entry when that key is not yet present, so use Add when you specifically want duplicate keys to be caught.
  • Keys returns the dictionary’s keys as an array, and Items returns the corresponding values as an array. The positions in those arrays correspond to each other.
  • Count reports how many key-item pairs are stored.
  • Remove deletes one key and its item; RemoveAll clears the dictionary.
  • CompareMode controls how string keys are compared. Set it before adding entries. Changing it after entries have been added raises an error.

The key comparison setting matters when keys are text. The default binary comparison distinguishes strings with different letter case, so “North” and “north” can be separate keys. Setting CompareMode to vbTextCompare makes string comparisons case-insensitive according to the runtime’s text comparison rules. Choose a mode deliberately and set it before populating the dictionary.

Example: Count Repeated Values in a Worksheet

This Windows Excel macro reads names in column A, beginning at row 2, counts each name without regard to letter case, and writes the count for each row into column B. Blank cells in column A are left blank in column B. It expects the input range not to contain Excel error values, because converting an error value to text requires separate handling.

Save a copy of the workbook before running any macro. VBA changes cannot be undone with Ctrl+Z, and this example writes to column B, replacing anything already there on the affected rows. On Windows, run it with Alt+F8 and choose CountNames; on Mac, the macro dialog shortcut is Option+F8, although this particular Scripting.Dictionary example is intended for Windows.

Option Explicit

Sub CountNames()
    Dim ws As Worksheet
    Dim dict As Object
    Dim lastRow As Long
    Dim rowNum As Long
    Dim nameKey As String

    ' Use the active sheet; column A holds names and column B receives counts.
    Set ws = ActiveSheet
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' First pass: count each nonblank name.
    For rowNum = 2 To lastRow
        nameKey = Trim$(CStr(ws.Cells(rowNum, "A").Value2))
        If Len(nameKey) > 0 Then
            If dict.Exists(nameKey) Then
                dict(nameKey) = CLng(dict(nameKey)) + 1
            Else
                dict.Add nameKey, 1&
            End If
        End If
    Next rowNum

    ' Second pass: write each row's name count to column B.
    ws.Range("B1").Value = "Count"
    For rowNum = 2 To lastRow
        nameKey = Trim$(CStr(ws.Cells(rowNum, "A").Value2))
        If Len(nameKey) > 0 Then
            ws.Cells(rowNum, "B").Value = dict(nameKey)
        Else
            ws.Cells(rowNum, "B").ClearContents
        End If
    Next rowNum
End Sub

For example, if A2:A5 contains “Mira”, “Lee”, “Mira”, and a blank, the output in B2:B5 is 2, 1, 2, and blank. The dictionary stores only two keys, while the second pass writes each key’s total beside every matching source row. To preserve existing contents in column B, change the destination column or write the results to a new worksheet.

Iteration, Keys, and Values

A common pattern is to loop through the dictionary’s keys and use each key to retrieve its value. You can also loop through the array returned by Keys or Items. Declare the loop variable as Variant when iterating over a returned array; this avoids type problems when keys are not all the same subtype. The Count property tells you how many entries to expect, but code should not assume that a particular key will be present.

Dictionary order should not be treated as a sorting feature. If the output needs to be alphabetized or ordered by value, collect the keys or items and sort them separately. The Keys and Items arrays are useful for transferring data, but they do not replace a deliberate sorting step. For worksheet output, it is often clearer to write the values to a range and then use Excel’s sorting tools.

Common Errors and Misconceptions

  • Duplicate key error: Add fails when the key already exists. Use Exists to choose whether to update, ignore, or report a duplicate.
  • Missing-reference error: Early-bound code may show “User-defined type not defined” or a missing library if Microsoft Scripting Runtime is not checked or unavailable. Fix the reference, or use late binding on supported Windows systems.
  • Unexpected new entry: Reading the Item property for a nonexistent key can create that key with an empty item. Test with Exists before reading if the lookup must not alter the dictionary.
  • CompareMode set too late: Set the comparison mode before adding keys. Once populated, changing it causes an error rather than converting existing entries.
  • Case-sensitive results: The default comparison can treat differently cased text as different keys. Set vbTextCompare first if case should not distinguish names.
  • Mac incompatibility: A Windows COM object is not made cross-platform by using late binding. Test on the actual Mac version or choose a different data structure.

Another misconception is that a Dictionary is automatically a worksheet range or a database table. It is an in-memory VBA object: its contents disappear when the macro ends unless the code writes them somewhere persistent, such as cells or a file. It can speed up repeated lookups compared with repeatedly scanning a range, but performance depends on the task, data size, and how much data is transferred to and from the worksheet.

Dictionary Compared with Collection and Worksheet Lookups

A VBA Collection also stores items and can retrieve an item by a key, so it may suit simple storage when you do not need the Dictionary’s Exists method or its key and item arrays. A Dictionary is usually more direct for counting, duplicate handling, or checking membership because those operations are built into its interface. A Collection and a Dictionary have different behavior and error handling; they are not interchangeable merely because both can hold multiple values.

Worksheet lookup formulas are a separate choice. In Microsoft 365 for Windows, XLOOKUP is useful when the source and result belong in worksheet formulas and should recalculate with the data. A VBA Dictionary is useful when a macro needs repeated lookups or grouping while it runs. For a small one-off lookup, either formulas or a straightforward loop may be easier to maintain than introducing an object. Choose based on where the logic belongs and whether results should update automatically when cells change.

Frequently Asked Questions

Where is the official VBA Dictionary documentation?
Use Microsoft Learn’s Dictionary object reference in the language reference. It documents the Scripting.Dictionary object and its methods and properties; it is not a worksheet function reference.

Do I need to add a reference to use a Dictionary?
Only with early binding. Check Microsoft Scripting Runtime under Tools > References in the Visual Basic Editor. With late binding through CreateObject, you do not set that project reference, but the Windows scripting object must still be available.

How do I avoid an error when adding a duplicate key?
Call Exists first. If it returns True, update the existing item or follow another explicit duplicate policy; otherwise, call Add.

Is a VBA Dictionary case-sensitive?
By default, string-key comparison is binary and distinguishes case. Set CompareMode to vbTextCompare before inserting keys when you want text keys compared without distinguishing letter case.

Can I use Scripting.Dictionary in Excel for Mac?
Do not assume a Windows Scripting.Dictionary COM object will work on Mac. Mac Excel does not support Windows COM automation in the same way; for cross-platform macros, test the target environment and consider a Collection or arrays.

Can a Dictionary sort its keys?
No built-in sorting method is provided. Retrieve the keys, sort them separately, and then use that sorted sequence to produce ordered output.

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.