VBA Dictionary Item Count: Use the Count Property

VBA & macros · Updated November 18, 2026

To get the number of entries in a VBA Scripting.Dictionary, read its Count property: dict.Count. It returns the number of key-item pairs currently in the dictionary, so an empty dictionary has a count of 0.

What the Count Property Measures

A dictionary stores values under keys. Each key can appear only once, and Count reports how many keys—and therefore how many key-item pairs—the dictionary contains. It does not add up the values, count characters, or count the rows from which you might have loaded the data.

For example, if a dictionary contains the pairs North → 18, South → 24, and West → 18, its count is 3. The repeated item value of 18 does not make the count 2: the keys are distinct. If you replace the item associated with North, the count remains 3 because you have changed a value, not added a key.

The property belongs to the dictionary object supplied by the Microsoft Scripting Runtime. In ordinary VBA code, you write dict.Count, not dict.Count(). It takes no argument and is read-only: use methods such as Add, Remove, or RemoveAll to change the dictionary, then read Count again.

Read Count in VBA

In the example below, dict is declared as an Object and created with late binding. The printed results show that changing an existing item leaves the count alone, while removing a key decreases it.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z.

Sub ShowDictionaryCount()
    Dim dict As Object

    ' Create a Scripting.Dictionary using late binding.
    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "North", 18
    dict.Add "South", 24
    Debug.Print dict.Count   ' 2

    ' Update an existing key; this does not add another entry.
    dict("North") = 20
    Debug.Print dict.Count   ' 2

    dict.Remove "South"
    Debug.Print dict.Count   ' 1
End Sub

To run it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor, insert a standard module, paste the procedure, and run ShowDictionaryCount. The results appear in the Immediate window, which you can display in the editor with Ctrl+G. On Mac, the Visual Basic Editor has its own menus and keyboard shortcuts can differ; more importantly, the Windows Scripting Dictionary component is not a dependable cross-platform choice, as discussed below.

How Add, Update, and Remove Affect the Count

The count changes when the set of keys changes. Adding a new key increases it by one; removing an existing key decreases it by one. Calling RemoveAll clears every entry, leaving the count at zero. Updating the item for a key that is already present does not change it.

  • dict.Add key, item adds a new key-item pair. If the key already exists, Add raises an error rather than silently creating a duplicate.
  • dict(key) = item assigns an item to a key. For a key that already exists, this replaces its item and leaves the count unchanged. Assignment to a missing key can create an entry, so use Add when you want duplicate keys to be caught clearly.
  • dict.Remove key removes one entry. Check dict.Exists(key) first if the key may not be present; attempting to remove a missing key raises an error.
  • dict.RemoveAll removes all entries. The dictionary object itself still exists and can be filled again.

These rules make Count useful for checking a process as it runs. For instance, you can inspect it after loading data to confirm how many distinct keys were accepted, or test whether there is anything to process before starting a loop.

Count Versus Keys, Items, and Worksheet Rows

Count is often the simplest way to answer “How many entries are in this dictionary?” You do not need to retrieve all the keys or items just to find their number. The Keys and Items properties are useful when you need the actual contents, but they serve a different purpose from the count.

A dictionary count is also not necessarily the number of source rows. If a worksheet has 100 records but you use a customer ID as the key, repeated IDs do not create new keys. The final count could be lower than 100. Conversely, if your code constructs different keys for records that represent the same customer, the count may be higher than the number of real customers. The result depends on how you define and normalize keys.

There is no need to use UBound(dict.Keys) as a substitute for Count. An array returned by Keys has array-bound rules, and handling an empty result can make that approach needlessly awkward. dict.Count directly expresses the number of dictionary entries and gives 0 when there are none.

Example: Count Unique Values from a Range

A common use is to count distinct, nonblank values in a worksheet column. In this example, the values are in A2:A100 on a sheet named Sheet1. Each different text value becomes a key; the item stored for each key is just a placeholder. The final Count is the number of unique nonblank values.

The code uses text comparison, so values such as Blue and blue are treated as the same key. It skips blank cells and error-valued cells. Change the range and sheet name to match your workbook.

Save a copy of the workbook before running this macro; macro actions cannot be reversed with Ctrl+Z.

Sub CountUniqueValues()
    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("Sheet1")

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

    Debug.Print "Unique nonblank values: " & dict.Count
End Sub

Setting CompareMode before adding keys matters. With vbTextCompare, the dictionary treats text keys as equal without regard to letter case. If you want case-sensitive keys, use the default binary comparison instead. The example does not trim spaces, so Blue and Blue remain different keys; add deliberate cleanup if surrounding spaces should not matter.

Common Count Problems and Their Fixes

If dict.Count causes an error, first check that the object was created successfully and that the variable refers to a dictionary. A declaration such as Dim dict As Object only declares a variable; it does not create the object. You also need a statement such as Set dict = CreateObject("Scripting.Dictionary") on supported Windows installations.

  • Object variable not set: The variable is still Nothing. Create the dictionary with Set before reading its properties.
  • Duplicate key error: Add was called for a key that already exists. Test with Exists first, or update the existing item if that is the intended behavior.
  • Count did not increase: The code may have updated an existing key instead of adding a new one, or a comparison setting may treat two differently spelled keys as equal.
  • Unexpectedly high count: Keys that look alike may contain extra spaces, different data types, or inconsistent formatting. Normalize the key values consistently before adding them.
  • Error changing CompareMode: Set CompareMode before adding the first entry. Changing the comparison mode after the dictionary has keys can raise an error.

When debugging, print the count at meaningful points, such as immediately after loading the source data and after filtering. That helps separate a dictionary issue from a data-cleaning issue. Remember that Count tells you how many keys exist; it cannot tell you whether those keys represent the entities you intended to count.

Windows and Mac Compatibility

On Windows, Scripting.Dictionary is commonly used from Excel VBA. The late-bound creation shown above avoids adding a compile-time reference, which can make a workbook easier to move between Windows computers with different Office setups. It still relies on the Scripting Runtime being available.

For early binding, set a reference in the Visual Basic Editor through Tools > References and select Microsoft Scripting Runtime. Then you can declare a variable as Scripting.Dictionary, and the editor can provide its members while you write code. A workbook using that reference may require the library to be available on another computer; a missing reference can cause compile problems.

Do not assume that CreateObject("Scripting.Dictionary") will work on Mac. It is a Windows COM component, and availability differs outside Windows. If a macro must run on both platforms, consider a VBA Collection or a dictionary implementation designed for cross-platform VBA, and confirm that its behavior meets your needs. Collections do not expose the same built-in Count and key-management pattern as a Scripting Dictionary, so adapting code may require different logic.

Frequently Asked Questions

How do I get the item count of a VBA Dictionary?
Read the property with dict.Count. For example, if the dictionary variable is named customers, use customers.Count.

Does Dictionary.Count count duplicate values?
It counts distinct keys, not distinct item values. Two keys can store the same value and still contribute two entries to the count.

Is Count a method or a property?
Count is a property. Write dict.Count without parentheses or arguments.

What does Count return for an empty dictionary?
It returns 0. After RemoveAll, the dictionary is empty, so its count is also 0.

Why does the count stay the same after I assign a value?
If you assign a new item to a key that already exists, you replace that key’s item rather than adding another key. The count changes only when the set of keys changes.

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.