VBA Dictionary Count: What It Returns and How to Use It

VBA & macros · Updated November 18, 2026

In VBA, read a dictionary’s item count with its Count property: dict.Count. It returns the number of key–item pairs currently stored, so a new or emptied dictionary has a count of 0, and adding a previously unused key increases the count by 1.

What the VBA Dictionary Count Measures

A VBA dictionary is a key-and-item collection provided by the Microsoft Scripting Runtime. Each entry has a key that identifies it and an item that holds its associated value. The Count property counts entries, not the number of characters in their values, the number of worksheet rows, or the number of distinct item values.

For example, a dictionary with the keys "North", "South", and "West" has a count of 3, even if two entries happen to contain the same item. Likewise, an entry still counts if its item is an empty string or another empty value. The key–item pair is what matters.

Count is a property, not a method that needs arguments. Use dict.Count, without parentheses, in ordinary VBA code. The property is read-only: change the number by adding or removing entries rather than assigning a value to Count.

Create a Dictionary and Read Its Count

On Windows, a common approach is late binding: declare the variable as an object and create it with CreateObject. This avoids setting a project reference to the Microsoft Scripting Runtime, which can make a workbook easier to move between Windows computers. The following procedure creates three distinct entries and displays the resulting count:

Sub ShowDictionaryCount()
    Dim dict As Object

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

    ' Add three key-item pairs.
    dict.Add "North", 12
    dict.Add "South", 18
    dict.Add "West", 9

    ' Display the number of entries: 3.
    MsgBox dict.Count
End Sub

Before running a macro in a workbook, save a copy. VBA macros cannot be undone with Ctrl+Z, and a procedure that changes worksheet data or dictionary contents may not have a built-in reversal step. This example only displays a message, but the backup habit is useful for macros generally.

With early binding, you can instead set a reference to Microsoft Scripting Runtime in the VBA editor and declare a variable as Scripting.Dictionary. That provides compile-time type information and editor assistance, but the reference must be available on the computer that runs the project. For a small routine, late binding is often simpler on Windows.

How Adding, Updating, and Removing Keys Changes Count

Adding a new key with Add increases the count by one. A dictionary does not allow two entries with the same key under its current comparison rules. Attempting to add a duplicate key raises an error; it does not silently create another entry. To change the item associated with an existing key, assign a new item to that key. Updating an existing entry leaves the count unchanged.

For example, after adding the key "A102", changing its item from a name to a revised name still leaves one entry for that key. Assigning an item through a key that does not yet exist can create an entry, so use Exists when the distinction between updating and adding matters.

Removing one existing key reduces the count by one. RemoveAll removes every entry, making the count 0 while leaving the dictionary object available for reuse. Check that a key exists before calling Remove if the key may be absent; trying to remove a missing key raises an error.

  • New key added: count increases by 1.
  • Existing key’s item updated: count stays the same.
  • Existing key removed: count decreases by 1.
  • RemoveAll called: count becomes 0.

Count Unique Keys from Worksheet Data

A frequent use for Count is counting unique values while processing worksheet rows. The dictionary stores each value as a key. Before adding a value, test it with Exists; only values not already present are added. The final count is then the number of distinct keys according to the dictionary’s comparison rules.

This example assumes the values to inspect are in cells A2 through A6 on the active worksheet. It skips blank cells, adds each other value once, and displays the number of unique nonblank values. For example, if the cells contain Apple, Pear, Apple, Pear, and Plum, the result is 3.

Sub CountUniqueValues()
    Dim dict As Object
    Dim cell As Range
    Dim value As String

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

    ' Read values from A2:A6 on the active sheet.
    For Each cell In ActiveSheet.Range("A2:A6")
        value = CStr(cell.Value)

        ' Ignore blank cells and add each value once.
        If Len(value) > 0 Then
            If Not dict.Exists(value) Then
                dict.Add value, True
            End If
        End If
    Next cell

    ' Show the number of unique nonblank values.
    MsgBox dict.Count
End Sub

The example converts cell values to strings to make the key type consistent, but that choice affects what counts as the same value. If numbers, dates, or other data types need their original types preserved, design the key conversion deliberately. Also decide whether cells containing spaces should count as blank: Len(value) > 0 treats a space as a value. A trimmed-string check can be used if leading and trailing spaces should be ignored.

Case Sensitivity and What Counts as a Duplicate

Whether two text keys are considered identical depends on the dictionary’s CompareMode. By default, comparison is binary, so text keys differing only in letter case are treated as different keys. For instance, "Lime" and "lime" can be separate entries under the default mode.

If you want text keys to compare without regard to case, set CompareMode to vbTextCompare before adding any data. Once the dictionary contains entries, changing the comparison mode causes an error. Choose the rule at the start, because it directly affects both duplicate detection and the final count.

Sub CountNamesWithoutCaseDifferences()
    Dim dict As Object

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

    ' Set comparison behavior before adding keys.
    dict.CompareMode = vbTextCompare

    ' These two spellings represent one key.
    dict.Add "Lime", 1
    If Not dict.Exists("lime") Then dict.Add "lime", 2

    ' Display 1 because the keys compare without case.
    MsgBox dict.Count
End Sub

Text comparison is not the same as cleaning data. It does not automatically remove extra spaces or standardize punctuation. If worksheet values may contain those differences, normalize them before using them as keys, and make that normalization match the intended meaning of “unique.”

Dictionary Count Compared with Other Ways to Count

A dictionary count answers “How many entries are in this object?” A worksheet function answers a different question about cells. For example, COUNTA counts nonempty cells in a range, including repeated values; it does not count distinct values by itself. If a list has five nonblank rows but only three unique names, COUNTA may return 5 while a dictionary built with an Exists check returns 3.

VBA’s Collection can store items, but it does not provide the same straightforward key lookup and Exists method as a Scripting.Dictionary. A dictionary is useful when code needs to test membership quickly, associate a value with each key, or count unique keys as it processes data. If the task is simply counting worksheet records, a worksheet formula or a PivotTable may be easier to inspect and maintain.

Also distinguish a dictionary’s count from the number of keys returned by its Keys method or items returned by its Items method. Those methods provide arrays of the stored keys or items; the Count property gives the number of entries. For a valid dictionary, the number of keys and items corresponds to that same entry count.

Common Count Problems and Practical Checks

If dict.Count causes an error, the variable may not refer to an initialized dictionary object. Confirm that the creation line ran successfully and that the object variable was declared and assigned with Set. With late binding on Windows, a missing Scripting Runtime component or an unsupported platform can also prevent object creation.

If the count is higher than expected, check for differences that make keys distinct: letter case under binary comparison, leading or trailing spaces, number-versus-text values, or inconsistent formatting. If it is lower than expected, check whether CompareMode is set to text comparison, whether the code skips blanks, and whether values are being converted or normalized before insertion.

A useful debugging check is to inspect the count after each operation or set a breakpoint and view the dictionary in the VBA editor’s Locals window. Remember that a dictionary only tracks entries in that particular object. Creating a new dictionary inside a procedure starts with a count of 0; it does not automatically reflect values elsewhere in the workbook.

Windows, Mac, and Alternative Approaches

The Scripting.Dictionary object is commonly used from Excel VBA on Windows. The late-binding example uses Windows COM automation and should not be assumed to work in Excel for Mac. The Microsoft Scripting Runtime reference and ActiveX-based automation are not a dependable cross-platform foundation for a Mac workbook; changing from early binding to late binding does not make an unavailable component portable.

If a workbook must run on both Windows and Mac, consider a VBA Collection for simple storage, or use arrays and explicit search logic when the data size and performance needs make that practical. These options are not exact drop-in replacements: a Collection does not offer the dictionary’s built-in Exists method, and arrays require code to manage searching and resizing. Test the chosen design in every Excel environment you need to support.

For a Windows-only macro, a dictionary is a convenient way to maintain a running set of unique keys. For cross-platform Excel work, worksheet formulas and PivotTables may be more portable, depending on whether the result needs to update on the sheet or be used internally by VBA.

Frequently Asked Questions

What does dict.Count return in VBA?
It returns the number of key–item pairs currently stored in the dictionary. A new or cleared dictionary returns 0.

Does changing a dictionary item increase its count?
No. Replacing the item for an existing key does not add another entry, so the count stays the same. Adding a new key increases the count by 1.

How do I count unique values with a VBA dictionary?
Use each candidate value as a key, check dict.Exists(value), and add it only when it is not already present. Then read dict.Count. Set CompareMode first if you need case-insensitive text matching.

Can a dictionary count be zero?
Yes. A newly created dictionary has no entries, and calling RemoveAll clears its entries, so in either case its count is 0.

Does Scripting.Dictionary work in Excel for Mac?
Do not assume it does. The common Scripting.Dictionary approach relies on Windows COM support. For a workbook that must run on Mac, use and test a more portable design, such as a Collection or arrays, or consider a worksheet-based solution.

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.