VBA Dictionary Count: How to Get the Number of Keys

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

The direct answer: use the Count property

A dictionary stores data as pairs: each key identifies an associated item (often called a value). In VBA, the Count property tells you how many such entries the dictionary contains. You do not need to loop through the keys or calculate the size of an array to get this number.

For example, if a dictionary contains the entries "North", "South", and "West", then dict.Count returns 3. The number describes entries in that particular dictionary at the moment you read the property; it is not the number of times your code has attempted to add a key, nor the number of items in a worksheet range.

Here is a complete late-bound example for Excel VBA on Windows:

Sub ShowDictionaryCount()
    Dim dict As Object

    ' Create a Scripting.Dictionary without setting a reference.
    Set dict = CreateObject("Scripting.Dictionary")

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

    Debug.Print dict.Count
End Sub

Run the procedure and check the Immediate Window in the Visual Basic Editor; it prints 3. On Windows, the Immediate Window can be opened from View > Immediate Window in the editor. Save a copy of your workbook before running a macro: macro actions cannot be undone with Ctrl+Z.

What the count includes

The count is the number of distinct keys present, with one associated item for each key. A dictionary cannot hold two entries with the same key under its current comparison rules. Consequently, adding another value for an existing key does not create a second entry if you assign through the key; it changes the item associated with that key.

For instance, after the following statements, the count is 1, not 2:

Sub ReplaceDictionaryItem()
    Dim dict As Object

    ' Create a dictionary and set two items for the same key.
    Set dict = CreateObject("Scripting.Dictionary")
    dict("apples") = 4
    dict("apples") = 9

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

In this example, assigning to dict("apples") the first time creates the entry; assigning to that key again replaces its item. By contrast, calling dict.Add "apples", 9 when the key already exists raises an error rather than increasing the count. That distinction matters when you are counting unique values from a list: decide whether duplicates should be ignored, replaced, or reported as errors.

Creating a dictionary in Excel VBA

On Windows, the commonly used dictionary is Scripting.Dictionary. Late binding with CreateObject("Scripting.Dictionary"), as shown above, avoids choosing a reference in the VBA editor. It is convenient when sharing a workbook across Windows computers because the code does not need a compile-time reference to the scripting library, although the component must still be available to create the object at run time.

With early binding, you add the Microsoft Scripting Runtime reference through Tools > References in the Visual Basic Editor, then declare a variable as Scripting.Dictionary and instantiate it with New Scripting.Dictionary. Early binding gives the editor more information about the object, including autocomplete and compile-time checks. Late binding uses an Object variable, so those conveniences are reduced.

Whichever style you choose, the count is accessed the same way: dict.Count. A typical test before using a dictionary can prevent code from making assumptions about it having entries:

Sub ReportIfDictionaryHasEntries()
    Dim dict As Object

    ' Create an empty dictionary.
    Set dict = CreateObject("Scripting.Dictionary")

    If dict.Count = 0 Then
        Debug.Print "No entries are stored."
    Else
        Debug.Print dict.Count & " entries are stored."
    End If
End Sub

How the count changes

Count reflects the dictionary’s current contents. Adding a previously absent key increases it by one. Removing an existing key decreases it by one. Removing a key that is not present does not remove an entry. Calling RemoveAll clears the dictionary, after which Count is 0. Replacing the item for an existing key leaves the count unchanged.

This makes Count useful in loops and conditional logic, but it is important to understand what the loop changes. For example, a loop that removes dictionary entries changes the count while it runs. Avoid assuming the original count remains fixed after each removal. If you need to process every original key while deleting entries, first capture the keys in an array, then work through that saved list carefully.

The property is read directly; it is not a method call. Write dict.Count, not dict.Count(). You can store the result in a variable if you need a snapshot of the count at a particular point:

Sub SaveCurrentCount()
    Dim dict As Object
    Dim entryCount As Long

    ' Create a dictionary and record its current size.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A", 100
    dict.Add "B", 200
    entryCount = dict.Count

    Debug.Print entryCount  ' Prints 2.
End Sub

That variable will not update automatically if the dictionary changes later. Read dict.Count again whenever you need the current number.

Counting keys versus counting matches

A frequent source of confusion is the difference between counting all keys and counting keys that meet a condition. dict.Count gives the total number of entries. It does not count only keys with a particular prefix, items above a threshold, or values that meet a test. For a conditional count, inspect the entries and maintain a separate counter.

For example, this code counts items whose numeric values are at least 10. The dictionary’s overall count and the qualifying count can differ:

Sub CountValuesAtLeastTen()
    Dim dict As Object
    Dim key As Variant
    Dim qualifyingCount As Long

    ' Add sample entries, then count values meeting the condition.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "A", 12
    dict.Add "B", 7
    dict.Add "C", 10

    For Each key In dict.Keys
        If dict(key) >= 10 Then qualifyingCount = qualifyingCount + 1
    Next key

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

The loop variable is declared as Variant because the array returned by Keys contains variants. The conditional counter is a Long, which is appropriate for ordinary VBA collection counts. If you only want the total number of dictionary entries, skip the loop and use dict.Count.

Key comparison and duplicate values

Dictionary keys are unique according to the dictionary’s comparison mode. The default comparison is binary, so text keys that differ in letter case can be treated as different keys. If you want text keys to compare without regard to case, set CompareMode to vbTextCompare before adding entries.

Sub CountCaseInsensitiveKeys()
    Dim dict As Object

    ' Set comparison behavior before adding any keys.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    dict.Add "Excel", 1

    Debug.Print dict.Exists("EXCEL")  ' True.
    Debug.Print dict.Count             ' 1.
End Sub

Set the comparison mode before populating the dictionary. Changing it after keys have been added can cause an error. Under text comparison, a later attempt to add a key differing only by capitalization may be treated as a duplicate. This is why the count represents unique keys under the chosen comparison rule, not necessarily distinct character strings as they appeared in the source data.

Common mistakes and checks

Using the wrong variable: Confirm that dict is the dictionary you intend to measure, especially if a procedure uses multiple dictionaries.
Reading the count before adding entries: A newly created dictionary starts empty, so its count is 0. Read the property after the code that populates it if you want the final total.
Expecting duplicate additions to raise the count: A key can occur only once under the dictionary’s comparison mode. Use a different key or update the existing item, depending on the intended result.
Confusing total entries with qualifying entries: The property does not apply a filter. Loop through the keys and count matches when a condition is involved.
Assuming a dictionary is initialized: If an object variable has not been set, trying to read its property causes an object-variable error. Create the object with Set first.

You may see code that uses UBound(dict.Keys) + 1 to estimate the number of keys. It is usually unnecessary and less clear than dict.Count. It also needs special handling when the dictionary is empty because the returned key array has no usable upper bound to add one to. Use Count for the total, and use Keys when you actually need to retrieve or loop through the key values.

Windows, Mac, and alternatives

The examples use the Windows Scripting.Dictionary COM component. Excel for Microsoft 365 on Windows supports this common VBA approach. Excel for Mac does not provide Windows COM automation in the same way, so code that calls CreateObject("Scripting.Dictionary") should not be assumed to work on Mac. If a workbook must run on both platforms, test the required VBA features on each target version and consider a platform-neutral design.

A VBA Collection is one built-in alternative and also has a Count property. It is not a drop-in replacement: its key handling and lookup behavior differ from a dictionary, and it does not provide the same Exists method. Arrays can also store data, but you need to manage their size and search logic yourself. Choose based on whether you need unique keys, fast key lookup, duplicate handling, and cross-platform compatibility, rather than replacing the object solely to obtain a count.

If your code only needs the number of entries and already has a dictionary, no alternative is needed. dict.Count is direct and readable. If your actual requirement is counting distinct worksheet values rather than VBA dictionary entries, that is a separate Excel task and may be better handled with worksheet formulas or a PivotTable, depending on the data and Excel version.

Frequently Asked Questions

Does Dictionary.Count return the number of keys or values?
It returns the number of entries, meaning key–value pairs. Each entry has one key and one associated item, so the key count and item count are the same.

What does Count return for an empty dictionary?
It returns 0. A dictionary created with CreateObject has no entries until your code adds them.

Can I count the keys without a loop?
Yes. Use dict.Count. A loop is needed only when you are counting a subset, such as keys that match a condition.

Does adding a duplicate key increase Count?
No. A dictionary does not store two entries with the same key under its comparison rules. The Add method raises an error for an existing key; assigning an item through an existing key replaces its value without increasing the count.

Why does CreateObject fail on my Mac?
Scripting.Dictionary relies on Windows COM automation and is not a portable assumption for Mac Excel. Use an alternative data structure or a design tested for both platforms if the workbook must run on Mac.

Is Count a method that needs parentheses?
No. It is a property, so write dict.Count without parentheses.

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.