VBA Dictionary Key Count: How to Use the Count Property

To count the keys in a VBA dictionary, read its Count property: dict.Count. It returns the number of key-item pairs currently stored, so an empty dictionary returns 0 and a dictionary with five distinct keys returns 5.

What the Count Property Measures

A Scripting.Dictionary stores entries as key-item pairs. A key identifies an entry, and its associated item holds the value. The Count property reports how many entries are in the dictionary at that moment. Because each entry has one key, this is also the number of keys.

For example, suppose a dictionary contains the entries North → 12, South → 8, and West → 15. Its count is 3. The values do not affect the count: changing the item for North from 12 to 20 still leaves three entries. Likewise, the count is not the number of characters in the keys, the number of values in a source worksheet, or the number of times a key was encountered while processing data.

The property is useful after building a lookup table, collecting distinct labels, or checking whether any entries were added. It is a read-only property: use dictionary methods such as Add or Remove to change the entries, then read Count again.

Basic VBA Example

This Windows Excel macro creates a dictionary, adds three distinct keys, and prints the count in the Immediate window. In the VBA editor, press Ctrl+G on Windows to show that window; on a Mac, use the editor’s View menu to open the Immediate window if available.

Sub CountDictionaryKeys()
    Dim dict As Object

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

    dict.Add "North", 12
    dict.Add "South", 8
    dict.Add "West", 15

    Debug.Print dict.Count  ' Prints 3.
End Sub

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even when they change worksheet data. This example only creates an object and prints a result, but backing up is a sensible habit whenever you run VBA code.

In the sample, the answer is 3 because exactly three entries were added. The count does not include a separate total for the items; every key-item pair contributes one. To assign the result to a variable, declare it as a Long and use keyCount = dict.Count. A Long is the usual VBA integer type for counts because it accommodates far more entries than the older 16-bit Integer type.

How to Create the Dictionary

The example uses late binding: CreateObject("Scripting.Dictionary") creates the object at run time, and the variable is declared As Object. This avoids requiring a reference to the Scripting Runtime library in the VBA project. It is convenient when a workbook will be opened on multiple Windows computers with different VBA reference settings.

On Windows, you can instead use early binding. In the VBA editor, open Tools > References and select Microsoft Scripting Runtime, if it is listed. Then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding provides access to the library’s type information while editing, including clearer member suggestions, but a missing or broken reference can prevent a project from compiling.

The count expression is the same either way: read dict.Count. Do not add parentheses to the property as if it were a method. For a dictionary variable that has not been initialized, however, trying to read Count causes an object-variable error. Create the dictionary with Set first, then check its count.

Counting Unique Worksheet Values

A common use is counting distinct labels in a column. The following macro assumes the labels are text in A2:A100 on the active worksheet. It ignores blank cells and treats upper- and lowercase spellings as the same key. It reports the number of distinct nonblank labels in a message box.

Sub CountUniqueLabels()
    Dim dict As Object
    Dim cell As Range
    Dim key As String

    ' Save a copy of the workbook before running a macro.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    For Each cell In ActiveSheet.Range("A2:A100")
        If Len(cell.Value2) > 0 Then
            key = CStr(cell.Value2)
            If Not dict.Exists(key) Then
                dict.Add key, True
            End If
        End If
    Next cell

    MsgBox dict.Count
End Sub

For instance, if the cells contain North, South, North, West, and a blank, the macro displays 3. The repeated North value is already present, so it does not add another entry. The stored item True is just a placeholder; this task needs the keys, not meaningful values.

This example expects ordinary text values. If the range may contain Excel error values, convert or handle those separately before calling CStr, since converting an error value to a string can raise a VBA error. If you want numeric identifiers, decide deliberately whether values such as numeric 12 and text “12” should count as the same key; converting every value to text makes those representations share a key.

Duplicates, Case, and the Count

A dictionary cannot hold two separate entries with identical keys. If a routine calls Add using a key that already exists, VBA raises an error rather than increasing the count. That is why the worksheet example calls Exists first. The count then represents distinct keys accepted by the dictionary, not the number of source rows processed.

Key comparison also matters. By default, string keys are compared using binary comparison, which generally distinguishes uppercase and lowercase text. Set CompareMode to vbTextCompare before adding entries if you want text comparison that treats case variations such as “North” and “north” as equivalent. Set this mode before populating the dictionary; changing it after entries have been added can raise an error.

Comparison mode is not the same as trimming or otherwise cleaning text. “North” and “North ” have different characters, so they can remain different keys even with text comparison. If extra spaces should not matter, normalize the key before testing it, for example by applying VBA’s Trim$ function to a text value. Consider other normalization rules, such as punctuation or leading zeros, based on what the identifiers mean in the data.

Count Versus Keys, Items, and Worksheet Counts

The Keys method returns an array of the dictionary’s keys, while Items returns an array of its items. For a populated dictionary, the number of keys and number of items match the dictionary’s count because each entry has one of each. Usually, dict.Count is the simplest way to get the number without retrieving an array.

Be careful when using the array returned by Keys. An empty dictionary has no keys to enumerate, so code that relies on array bounds can fail or need special handling. Checking dict.Count first is a straightforward way to distinguish an empty dictionary from a populated one.

A dictionary count is also different from worksheet counting functions. COUNTA counts nonempty cells, including repeated values, while a dictionary can be used to count distinct keys after a VBA routine has processed values. A dictionary is useful when the macro needs fast key-based lookup or needs to associate each unique label with an item. If the only goal is a worksheet summary, a PivotTable or a suitable worksheet formula may be simpler than introducing VBA.

Common Errors and Troubleshooting

Object variable not set: Ensure the dictionary has been created with Set dict = CreateObject(...) or, with early binding, Set dict = New Scripting.Dictionary before reading dict.Count.
Duplicate-key error: Check dict.Exists(key) before calling Add when repeated input is expected. Alternatively, decide whether an existing item should be updated rather than added again.
Compile error on the dictionary type: If using Scripting.Dictionary as a declared type, confirm the Microsoft Scripting Runtime reference is enabled. Late binding with an Object variable avoids that compile-time reference, but still relies on the Windows scripting component being available.
Unexpectedly high count: Look for differences in whitespace, punctuation, or number-versus-text formatting. Those differences can create distinct keys even when worksheet cells look alike.
Unexpectedly low count: Check whether case-insensitive comparison or text conversion is merging values that you intended to keep separate. Confirm that blank cells are being handled as intended.

To inspect the result while debugging, put Debug.Print dict.Count after the loop and open the Immediate window. You can also inspect the variable in the Locals window while the code is paused. These checks help separate a counting issue from a problem in the loop that creates the dictionary.

Windows, Mac, and Excel for the Web

This article’s Scripting.Dictionary examples target desktop Excel for Microsoft 365 on Windows. The object is based on Windows scripting and COM technology. Excel for Mac does not provide the same Windows COM automation environment, so CreateObject("Scripting.Dictionary") and the Microsoft Scripting Runtime reference should not be assumed to work there. Excel for the web does not run VBA macros.

If a workbook must support Mac users, choose an approach designed for the platforms involved. A VBA Collection can store entries, though it does not provide the same built-in dictionary interface, such as an Exists method. Another option is to use worksheet tools or a platform-independent design suited to the workbook. Test the chosen approach on every target Excel platform rather than relying on Windows-only automation.

Frequently Asked Questions

What VBA code counts dictionary keys?
Read the property with dict.Count. For example, Debug.Print dict.Count prints the number of entries in the Immediate window.

Does Dictionary.Count count unique keys?
It counts the entries currently stored, and dictionary keys are unique within the dictionary. If you use an Exists check before adding repeated source values, the count becomes the number of distinct keys accepted under the dictionary’s comparison rules.

What does Count return for an empty dictionary?
It returns 0. The dictionary object must still be initialized before you can read that property; an uninitialized object variable is not an empty dictionary.

Can I count keys without a loop?
Yes, once the dictionary has been populated, use dict.Count directly. A loop is needed only when you are building the dictionary from another source, such as a worksheet range.

Does Dictionary.Count work in Excel for Mac?
The property belongs to the dictionary object, but the Windows Scripting.Dictionary COM object is not a portable Mac VBA feature. For Mac support, use a compatible alternative and verify it in the target version of Excel.

Choosing the Right Method

Use dict.Count whenever the dictionary already contains the entries you want to count. For distinct worksheet values, first define what counts as the same value: decide how to handle case, spaces, blanks, and numbers stored as text. Then build the dictionary consistently and read its count. This keeps the answer simple while making the result meaningful and reproducible.

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.