Excel VBA Dictionary Count: What .Count Returns

VBA & macros · Updated November 21, 2026

In Excel VBA, get the number of entries in a Dictionary with its .Count property: use dict.Count. It returns the number of key–item pairs currently stored—not the sum of the items, and not the number of rows in your worksheet unless you have added one dictionary entry for each row.

What Dictionary.Count actually counts

A VBA Dictionary stores values by key. Each entry consists of one key and its associated item, such as a product name and its quantity. The Count property reports how many such entries exist at the moment you read it. If there are keys for East, West, and Central, dict.Count is 3, regardless of whether their stored items are 2, 500, or text.

This makes Count useful for checking whether a dictionary is empty, reporting how many distinct keys were collected, or confirming that a routine added the expected number of entries. It is a property, not a method, so write dict.Count without parentheses. The property is read-only: change the entries with methods such as Add, Remove, or RemoveAll, and the count changes accordingly.

A minimal example

The following Windows VBA example creates a late-bound Dictionary, adds three entries, and prints the count in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows; on Mac, use the editor’s View menu to show it.

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

Sub ShowDictionaryCount()
    Dim dict As Object

    ' Create a Scripting.Dictionary on Windows.
    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "East", 12
    dict.Add "West", 7
    dict.Add "Central", 19

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

The stored values total 38, but the count is 3 because there are three keys. If you remove one key with dict.Remove "West", the count becomes 2. If you call dict.RemoveAll, it becomes 0. Reading dict.Count after the change is the straightforward way to verify the current number of entries.

Count is not the same as frequency

A common point of confusion is the difference between counting dictionary entries and counting how many times a value appeared in source data. A dictionary with one entry per distinct product might contain the key “Pencils” and the item 14. Its Count is 1; the item 14 is the frequency stored for that product. With 20 distinct product keys, the dictionary count is 20, even if the quantities add up to thousands.

To count repeated labels, use each label as a key and store its running frequency as the item. In the example below, capitalization is treated as equivalent because the comparison mode is set before any keys are added.

Sub CountLabelFrequencies()
    Dim dict As Object
    Dim labels As Variant
    Dim label As Variant

    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare  ' Set before adding keys.

    labels = Array("Pencils", "Folders", "pencils", "Pens")

    For Each label In labels
        If dict.Exists(CStr(label)) Then
            dict(CStr(label)) = dict(CStr(label)) + 1
        Else
            dict.Add CStr(label), 1
        End If
    Next label

    Debug.Print dict.Count              ' Prints 3 distinct labels.
    Debug.Print dict("Pencils")        ' Prints 2 occurrences.
End Sub

Here the dictionary contains Pencils, Folders, and Pens, so its count is 3. The item for Pencils is 2 because that label appeared twice, once with a lowercase initial. Use dict.Count for the number of distinct keys and dict(key) for the stored frequency of a particular key.

How to add entries without duplicate-key errors

The Add method expects a key that is not already present. Calling dict.Add again with an existing key raises an error rather than creating a second copy. That behavior is intentional: a Dictionary has one item per key. Before adding uncertain input, use Exists to decide whether to add a new entry or update the existing item.

  • Use dict.Exists(key) to test whether a key is present.
  • For a new key, call dict.Add key, item.
  • For an existing key, update its item if that is the desired behavior.
  • Read dict.Count after the operation if you need the new total.

Updating an existing key does not create an additional entry, so it does not increase the count. Removing a key does decrease it. If the goal is to count duplicates in the input, update the existing key’s numeric item instead of trying to add that key again.

Key comparison and capitalization

Dictionary key comparison affects what counts as a distinct key. The CompareMode property controls text comparison. The default binary comparison generally treats strings with different letter case as different keys, so “Maple” and “maple” can be separate entries. Set dict.CompareMode = vbTextCompare before adding any entries if you want case-insensitive text keys.

Do not change CompareMode after keys have been added. Setting it after the dictionary contains data can cause a run-time error. Choose the comparison rule when you create the dictionary, and apply it consistently. Also consider whether trimming spaces is appropriate for your data: “Maple” and “Maple ” may be different keys even when the visible distinction is easy to miss. If whitespace should not distinguish entries, clean the key text before checking or adding it.

Empty dictionaries and changing the count

A newly created Dictionary has a count of 0. You can use that fact to guard later code, for example before trying to process keys or report results. After adding one new key, the count becomes 1. Adding another key increases it again; changing the item associated with an existing key leaves it unchanged. Removing an existing key lowers it by one, and removing all entries resets it to zero.

For example, a data-import macro might add each previously unseen customer ID to a Dictionary. At the end, dict.Count gives the number of distinct IDs encountered, provided the macro uses the ID as the key and checks for existing keys. It does not tell you how many source rows were read: repeated IDs can appear on many rows while occupying a single dictionary entry.

When diagnosing a surprising result, inspect the keys as well as the count. A low count may mean the source contains repeated keys that the code correctly treats as one entry, or that key cleanup and comparison rules are combining values you expected to remain separate.

Late binding, references, and platform support

In the examples, Dim dict As Object and CreateObject("Scripting.Dictionary") use late binding. This avoids selecting a VBA reference at compile time, but the object still depends on the Windows Scripting runtime being available. The Dictionary is a COM scripting object, not a built-in worksheet function.

On Windows desktop Excel, an alternative is early binding: in the Visual Basic Editor choose Tools > References, select Microsoft Scripting Runtime, then declare the variable as Dim dict As Scripting.Dictionary and create it with Set dict = New Scripting.Dictionary. Early binding gives the editor access to the object’s type information and can provide better code completion. A workbook that uses this reference may show a missing-reference warning on a computer where the library is unavailable, so late binding is sometimes chosen for portability among Windows computers.

Excel for the web does not run VBA macros. On Mac, the Windows Scripting Runtime and its CreateObject approach are not a dependable cross-platform choice; availability differs from Windows and should not be assumed. For Mac-compatible VBA, consider a VBA Collection or a custom dictionary implementation, while recognizing that a Collection does not provide the same built-in key lookup and Exists method. Test any chosen solution in the Excel versions and operating systems where the workbook will be used.

Common problems when Count looks wrong

  • Counting rows instead of keys: repeated source values share one key, so the dictionary count reflects unique keys, not input rows.
  • Adding an existing key: Add raises an error for a duplicate. Check with Exists and update the item when appropriate.
  • Unexpected capitalization: binary comparison can distinguish differently capitalized strings. Set CompareMode before adding keys if text should be compared without regard to case.
  • Whitespace or inconsistent input: leading and trailing spaces, differing punctuation, or different data types can produce keys that look similar but are not identical. Normalize keys deliberately where appropriate.
  • Wrong object or missing runtime: confirm that the variable was set to a Dictionary and that the required Windows object can be created. A worksheet cell, VBA Collection, and Dictionary do not expose identical members.

For troubleshooting, check the code path that creates the object, the exact key being passed, and whether the operation adds a key or merely updates an item. Printing dict.Count after each stage can isolate when the total changes. Avoid assuming a count is wrong just because it differs from the number of rows in the input range; first establish whether the intended measure is rows, distinct values, or occurrences.

Dictionary versus Collection for counting

Both a Dictionary and a VBA Collection can hold multiple values, but a Dictionary is often the more direct choice when each entry needs a key and the code needs to test whether that key already exists. Its Count property reports its number of key–item pairs, while Exists supports a simple duplicate check. A Collection also has a Count property, but it does not offer the Dictionary’s built-in Exists method, so keyed counting generally needs extra error handling or another lookup strategy.

Choose based on the operation, not just the shared property name. For a simple ordered set of values, a Collection may be sufficient. For distinct-key totals or frequency tables, a Dictionary is convenient on supported Windows desktop installations. If the workbook must work in Excel for the web, VBA is not an option; use worksheet formulas or Office Scripts where suitable instead.

Frequently Asked Questions

Does Dictionary.Count include the items’ numeric values?
No. It counts key–item pairs. If three keys store 4, 10, and 20, the count is 3, while the sum of the items is 34.

How do I check whether a VBA Dictionary is empty?
Test whether dict.Count = 0. Make sure the Dictionary has been created with Set before reading its property; an uninitialized object variable causes an error.

Does changing a value increase Dictionary.Count?
No, changing the item for a key that already exists does not add another key. The count increases when a new key is added and decreases when an existing key is removed.

Can a Dictionary contain duplicate keys?
No. Each key identifies one entry. Use distinct keys, or store a frequency as the item for a repeated value and update that item when the key is already present.

Does VBA Dictionary work in Excel for the web or on Mac?
Excel for the web does not run VBA. The Scripting.Dictionary method shown here targets Windows desktop Excel; do not assume the Windows Scripting Runtime is available on Mac. Use a compatible alternative and verify it on the target platform.

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.