VBA Dictionary Methods: Add, Exists, Keys and More

A VBA Dictionary stores pairs of unique keys and associated values, and its core methods are Add, Exists, Keys, Items, Remove, and RemoveAll. Its Item and Count members are properties, not methods; together, these let you add, find, retrieve, count, and delete entries efficiently.

What a VBA Dictionary Is

The Dictionary object is provided by the Microsoft Scripting Runtime. Think of it as a lookup table: each entry has a key that identifies it and an item that stores its associated value. For example, a key might be a product code and its item the product name, or a customer name and its item the number of orders. A key must be unique within a dictionary, but different keys can have the same item.

Unlike a worksheet range, a dictionary lives in memory while the VBA procedure runs. It is useful when code needs to check whether a value has appeared before, associate records with identifiers, or build a summary without repeatedly searching cells. A dictionary does not automatically keep its contents on a worksheet or save them with the workbook; your code must write results to a range or another destination if you want to retain them.

Core Methods and What They Do

These are the principal methods available on a Scripting.Dictionary object:

Add key, item inserts a new key-item pair. It raises an error if that key already exists.
Exists(key) returns True if the key is present and False if it is not. Check this before reading or adding when you are unsure whether an entry exists.
Keys returns an array containing the dictionary’s keys.
Items returns an array containing the items, in the corresponding order to the keys array.
Remove key deletes one entry. It raises an error if the key is not found, so use Exists first when absence is possible.
RemoveAll deletes every entry but leaves the dictionary object available for reuse.

These names are often all called “dictionary methods” informally, but VBA distinguishes methods from properties. Count is a read-only property that gives the number of entries. Item(key) gets or sets the value for a key. The object also has a Key property that can rename an existing key, subject to the new key being unique.

Creating a Dictionary in Excel VBA

On Windows, you can use late binding, which does not require setting a reference in the VBA editor. Create the object with CreateObject("Scripting.Dictionary") and declare it as Object. This is convenient for sharing a workbook with users whose reference settings may differ. The trade-off is that VBA’s editor cannot offer the same compile-time member suggestions or type checking as it can with early binding.

For early binding, open the Visual Basic Editor, choose Tools > References, and select Microsoft Scripting Runtime. Then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding can make code easier to develop, but the reference must be available and enabled on the computer running the workbook.

The following example counts how often each nonblank value appears in column A of a sheet named Data. It uses late binding and treats text keys without regard to letter case.

Sub CountValues()
    Dim dict As Object
    Dim ws As Worksheet
    Dim cell As Range
    Dim key As String

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Count each nonblank value in the specified range.
    For Each cell In ws.Range("A2:A100").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

    Debug.Print dict.Count
End Sub

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. This example assumes the cells contain ordinary values rather than error values; code that may encounter worksheet errors should test for them before converting a cell value to text.

Adding, Looking Up, and Updating Entries

Add is best when a duplicate key should be treated as a problem. For example, a list expected to contain unique invoice numbers can use dict.Add invoiceNumber, amount; a repeated invoice number then produces an error that can reveal bad input. If duplicates are expected, check with Exists first and choose what they should mean: ignore them, replace the prior item, or combine the values.

To update a value for an existing key, use dict.Item(key) = newValue. The shorter form dict(key) = newValue also accesses the Item property. A notable behavior is that reading or assigning an Item with a key that is not present can create that key automatically, with an initially empty item when it is only read. This differs from Add, which explicitly adds a new pair and rejects a duplicate. Use Exists when you need to distinguish a missing key from a key whose stored value is empty.

For example, to total amounts by department, test whether the department exists. If it does, add the new amount to its stored item; otherwise, add the department with the first amount. This pattern avoids the duplicate-key error and keeps one running total per department. Decide how to handle blank department names, spaces, and inconsistent spelling before counting, since each can affect which records are considered the same key.

Getting Keys, Items, and the Count

dict.Count returns the number of entries, not the number of characters or the highest numeric key. When the dictionary is empty, its count is zero. Use this property to check whether there is anything to process before relying on a keys or items array.

dict.Keys and dict.Items each return an array. Their elements correspond by position: the item at a given position belongs to the key at that position. You can assign an array to a Variant variable, then loop through its indexes. Dictionary keys are not automatically sorted, so do not treat the returned order as alphabetical or numeric. If presentation order matters, sort the keys separately or write the results to a worksheet and sort that range.

For a simple pass through all entries, VBA’s For Each loop can iterate over the keys. Inside the loop, use the current key to retrieve its item. This is often clearer than creating separate arrays, especially when the code needs to act on each pair. Avoid changing the dictionary’s contents while enumerating it unless you have designed and checked that pattern; removing or adding entries during iteration can complicate which entries are visited.

CompareMode and Key Matching

The CompareMode property controls how string keys are compared. Set it before adding any entries. vbBinaryCompare compares text with case sensitivity, so “North” and “north” can be different keys. vbTextCompare compares text without case sensitivity, so those spellings are treated as the same key. Changing CompareMode after the dictionary contains data raises an error; create a fresh dictionary or clear it before changing the setting.

CompareMode does not clean or standardize your data. For example, it does not remove leading or trailing spaces, so “North” and “North ” may still be different keys. If that distinction is unwanted, normalize values before using them as keys, such as by trimming spaces. Also decide whether to convert numeric-looking identifiers to text: values such as 0012 and 12 may not represent the same identifier in your source system, even if Excel stores both as numbers.

Common Errors and Misunderstandings

Duplicate key error: Add cannot insert a key already in the dictionary. Use Exists and explicitly choose whether to update, skip, or combine.
Missing-key error: Remove on an absent key raises an error. Check with Exists when absence is possible.
Unexpected empty entries: reading an absent key through Item can create it. Test existence before retrieval if the dictionary must not gain entries during lookup.
CompareMode error: set the comparison rule before the first key is added, not after building the dictionary.
Object creation failure: CreateObject("Scripting.Dictionary") relies on Windows COM support and is not a portable solution for every Excel platform.

Another common misconception is that a dictionary is a sorted collection or a worksheet lookup formula. It is an in-memory object optimized for key-based access, not a persistent table, and its enumeration order should not be used as a sorting guarantee. If the result must appear in a particular order, handle sorting separately.

Windows, Mac, and Alternatives

Excel for Microsoft 365 on Windows can create the Scripting.Dictionary object through the Windows scripting runtime. Excel for Mac does not provide the same Windows COM automation support, so code that depends on CreateObject("Scripting.Dictionary") is not a reliable cross-platform choice. Setting an early-bound Microsoft Scripting Runtime reference does not make a Windows COM component available on Mac.

For code that must run on both platforms, consider a VBA Collection, arrays, or worksheet-based data structures. A Collection can store values under keys, but it does not offer the same built-in Exists method, so checking for a key usually needs additional error-handling code or a separate lookup routine. Arrays can be efficient for small, fixed data sets, but searching them generally requires a loop. Choose based on the workbook’s platform requirements, data size, and how often the code needs to find or update entries.

Frequently Asked Questions

What are the main methods of a VBA Dictionary?

The main methods are Add, Exists, Keys, Items, Remove, and RemoveAll. Count and Item are properties, although they are often discussed alongside the methods because they are central to using the object.

How do I check whether a key exists in VBA?

Call dict.Exists(key). It returns True or False. This is especially useful before calling Add, reading an item that must already exist, or removing a key that might be absent.

How do I change a value in a dictionary?

For an existing entry, assign the new value to its Item property, for example dict.Item(key) = newValue. Check Exists first if you do not want a missing key to be created implicitly.

Can a VBA Dictionary have duplicate keys?

No. Each key must be unique according to the dictionary’s comparison rules. With text comparison enabled, keys that differ only by letter case are treated as duplicates. Multiple different keys can still store identical items.

Does a VBA Dictionary work in Excel for Mac?

The Windows Scripting.Dictionary COM object is not a dependable cross-platform choice for Mac Excel. If a macro must work on both Windows and Mac, use a compatible alternative such as a Collection or arrays, and account for the extra lookup logic those options may require.

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.