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, itemadds a new key-item pair. If the key already exists,Addraises an error rather than silently creating a duplicate.dict(key) = itemassigns 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 useAddwhen you want duplicate keys to be caught clearly.dict.Remove keyremoves one entry. Checkdict.Exists(key)first if the key may not be present; attempting to remove a missing key raises an error.dict.RemoveAllremoves 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 withSetbefore reading its properties. - Duplicate key error:
Addwas called for a key that already exists. Test withExistsfirst, 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
CompareModebefore 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
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Method to Sort Data in ExcelThe VBA method to sort data in Excel is the Sort object, configured with a sort key, a…
- Excel Macro to Get Row Count: Choose the Right VBA MethodTo count data rows in an Excel macro, count a column that has one nonblank entry for every…
- VBA AutoFilter Syntax: Criteria, Fields, and ExamplesIn Excel VBA, the basic AutoFilter syntax is Range.AutoFilter Field:=columnNumber, Criteria1:=value. The range should include the list’s header…
- Excel Macro Protect Sheet Options: VBA, Limits, and TipsIn Excel for Microsoft 365 on Windows, a VBA macro protects a worksheet with the Worksheet.Protect method: choose…
- VBA Range Is Empty? How to Check Cells CorrectlyTo check whether a VBA range contains no entered values or formulas, use Application.CountA(target) = 0. First make…
- VBA Sort String Array: Methods, Code, and ExamplesVBA has no built-in general-purpose routine for sorting a string array, but you can sort one with a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.