VBA Dictionary Length: How to Count Items

VBA & macros · Updated November 18, 2026

In VBA, use a Dictionary’s Count property to find how many key-value pairs it contains: for example, Debug.Print dict.Count. A Dictionary has no Length property; its count changes as you add or remove entries.

What the Count Property Measures

A VBA Dictionary stores entries as pairs: each entry has a key and an associated value. The Count property reports the number of pairs currently stored. It does not report the number of characters in a key or value, the number of items in an associated array, or a maximum capacity.

For example, a dictionary containing the keys "A102", "B205", and "C310" has a count of 3, regardless of whether the values are short labels or long descriptions. The count is also not an index: a Dictionary is accessed by key, not by a numeric position like an array.

  • An empty Dictionary has a count of 0.
  • Adding one new key increases the count by one.
  • Changing the value for an existing key does not add another entry.
  • Removing a key decreases the count by one.

This is the direct answer to “VBA Dictionary length?”: check dict.Count for the number of stored entries.

A Basic VBA Example

The following example creates a late-bound Dictionary, adds three entries, and prints its count in the Immediate window. In the VBA editor, open the Immediate window with Ctrl+G on Windows, then run the macro. On Mac, the VBA editor’s menus and shortcuts can differ by version; use its View menu to open the Immediate window if the shortcut differs.

Sub ShowDictionaryCount()
    Dim dict As Object

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

    dict.Add "A102", "Pencil"
    dict.Add "B205", "Notebook"
    dict.Add "C310", "Ruler"

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

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is a practical safeguard even when a sample only prints a value.

In this example, dict is declared as Object, and CreateObject supplies the Dictionary at runtime. That is called late binding. The Count property is read from the object after the entries have been added; it is not a separate VBA function and does not require parentheses.

How Adding, Updating, and Removing Affect Count

It helps to distinguish adding a key from assigning a value to a key. The Add method creates a new pair and requires a key that is not already present. Assigning to an existing key’s Item value changes the stored value, but the number of pairs stays the same.

For instance, after adding "A102" once, setting dict.Item("A102") to another product name still leaves the count at 1. Adding a different key makes it 2. Trying to use Add again with a duplicate key raises an error rather than silently creating a second entry. Keys must be unique according to the Dictionary’s comparison rules.

Removal works in the opposite direction. Remove deletes one key and its value; RemoveAll clears every pair. Both operations affect Count immediately. This makes the property useful inside loops or before processing: a routine can check whether anything remains rather than assuming that the Dictionary still contains entries.

Sub CheckEmptyDictionary()
    Dim dict As Object

    ' Save a copy of the workbook before running any macro.
    Set dict = CreateObject("Scripting.Dictionary")

    If dict.Count = 0 Then
        Debug.Print "There are no entries."
    End If

    dict.Add "A102", "Pencil"
    dict.Remove "A102"
    Debug.Print dict.Count  ' Prints 0.
End Sub

The check is especially helpful before reading a key or passing keys to another procedure. A zero count tells you there are no stored pairs to process, but it does not tell you why the Dictionary is empty; that depends on the code that populated it.

Counting Keys, Values, and Unique Records

Because every Dictionary entry has one key and one value, dict.Count is both the number of keys and the number of key-value pairs. It is not necessarily the number of source rows that were read. If your code uses a field such as product ID as a key, repeated IDs cannot represent separate entries under the same key. The final count may therefore be lower than the number of rows scanned.

That behavior is often useful when the goal is to track unique items. Suppose a worksheet has 100 transaction rows but only 18 distinct customer IDs. If the code adds each customer ID as a Dictionary key and deals with duplicates appropriately, the Dictionary can contain 18 keys. Its count then describes unique IDs, not transactions.

Be clear about the intended meaning when reporting a count. A Dictionary can count distinct keys, while a loop counter can count rows examined. Those are different measures. If duplicate keys should contribute to a total, store or update a numeric value for the existing key instead of trying to add the same key again.

Dictionary comparison behavior can also affect what counts as a distinct key. The CompareMode property controls text comparison, including whether text keys are treated as case-sensitive. Set it before adding entries if you need a particular rule; changing it after the Dictionary contains data can cause an error. With a text-insensitive comparison mode, keys such as "North" and "north" are treated as equivalent for lookup and uniqueness, so they do not form two separate entries.

Late Binding and Early Binding

Late binding, used in the first example, avoids adding a reference in the VBA editor. The variable is declared as Object, and VBA resolves Dictionary members while the code runs. This can make a workbook easier to move between Windows computers that have the required scripting component, but misspelled member names are less likely to be caught during compile-time checks.

With early binding, you select the Microsoft Scripting Runtime reference in the VBA editor and declare the variable as Scripting.Dictionary. On Windows, the reference is normally added through Tools > References by selecting Microsoft Scripting Runtime. The variable declaration then identifies the object type explicitly, and the editor can offer member names as you type. A workbook using this reference may need that reference available on another computer too.

Both approaches use the same property for the number of entries: dict.Count. The choice affects setup and portability, not the meaning of the count. If you share a workbook across computers, test the chosen binding method in the target environment rather than assuming that a reference or automation component is installed everywhere.

Excel for Mac supports VBA, but Windows COM automation and the Microsoft Scripting Runtime setup should not be assumed to work identically on every Mac version. Availability can depend on the Excel build and environment. If Mac compatibility is essential, verify the Dictionary approach on the actual target version; a VBA Collection or a custom class may be a more portable design, though neither is a drop-in replacement with precisely the same interface.

Common Count Mistakes

  • Using Length: A Dictionary does not expose a Length property. Use Count; array bounds and string lengths use different approaches.
  • Counting before creating the object: A variable declared as an object must be assigned with Set before accessing Count. Otherwise, VBA raises an object-variable error.
  • Confusing rows with unique keys: Duplicate keys do not create additional pairs. Count the source rows separately if that is the number you need.
  • Assuming a value update adds an entry: Replacing the value for a key changes its data, not the Dictionary’s count.
  • Using count as an item position: A Dictionary is keyed, not position-indexed. Use a known key to retrieve an item, or use its Keys collection when you need to enumerate keys.
  • Ignoring errors on duplicate Add calls: Check Exists before adding when duplicates are possible, or write logic that updates the existing item.

A simple defensive pattern is to test that the object has been set and that its count is greater than zero before processing. For a late-bound object, the test for whether the variable is set must come first: trying to read dict.Count while dict is Nothing is not a valid empty-Dictionary check.

Dictionary Count Versus Arrays and Collections

The right way to measure a container depends on its type. For a Dictionary, use Count. For a VBA array, use UBound and, where relevant, LBound to determine its bounds; an array’s number of elements is not always simply its upper bound because its lower bound may not be zero. For a string, use the Len function to get the number of characters.

A VBA Collection also has a Count property, but it differs from a Scripting.Dictionary. A Collection can be accessed by position and can assign optional keys, while a Dictionary is designed around key-based lookup and offers methods such as Exists. Choose based on how the data will be retrieved, not on the word “length.” If you only need the number of stored members, the relevant property for either object is Count.

For a Dictionary whose values are arrays or strings, the outer Count still reports the number of key-value pairs. It does not measure the contents inside each value. For example, a Dictionary with two keys has a count of 2 even if one value is a 500-character string or an array containing dozens of elements. Measure that nested value separately with the method appropriate to its type.

Frequently Asked Questions

What is the VBA Dictionary length property?
There is no Length property for a Scripting.Dictionary. Use dict.Count to return the number of key-value pairs currently stored.

What does Count return for an empty Dictionary?
It returns 0. First make sure the Dictionary object has been created and assigned; a variable that is still Nothing is not an empty Dictionary you can query.

Does changing a Dictionary value increase Count?
No. Replacing the value associated with an existing key leaves the count unchanged. Adding a new, distinct key increases it by one.

How do I count unique values in a VBA Dictionary?
A Dictionary directly tracks unique keys, not unique values. To count distinct values, use each value as a key in a second Dictionary, with comparison rules suited to the data, or use another method that explicitly removes duplicates.

Can I use Scripting.Dictionary in Excel for Mac?
Do not assume the Windows Scripting Runtime or COM-based creation method is available in every Mac Excel environment. Check the specific Mac version and deployment setup; consider a Collection or a custom VBA class if portability is required.

Practical Rule

When “length” means how many entries a VBA Dictionary currently holds, write dict.Count. Create the object first, account for duplicate keys and removals, and remember that the result counts unique key-value pairs rather than source rows or characters. For other data types, use the measurement designed for that type: arrays have bounds, strings use Len, and Dictionary entries use Count.

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.