Excel VBA Dictionary Length: Count Entries and Keys

VBA & macros · Updated November 21, 2026

In Excel VBA, use a Dictionary’s Count property to find how many key-item pairs it contains. If by “length” you mean the number of characters in a key or text value, use VBA’s Len function instead; a Dictionary does not have a string-style length.

What “length” means for a VBA Dictionary

A Dictionary is a collection of unique keys, each associated with an item. Its Count property reports the number of pairs currently stored. For example, a dictionary containing the keys “North” and “South” has a count of 2, regardless of the lengths of those words or the size of the associated items.

The distinction matters because “length” can refer to several different things in VBA:

  • Number of entries: read dict.Count.
  • Characters in a string key or value: use Len on that string.
  • Number of elements in an array stored as an item: use the array’s bounds to calculate its element count.
  • Storage size: Count does not measure memory use or the size of the stored data.

So if a question asks for the “length of a dictionary” without further detail, the usual answer is its entry count. In code, that is a property rather than a method: write dict.Count, not dict.Count().

How to read Count

For a late-bound Dictionary on Windows, first create the object and then inspect Count. The property is read-only: it reflects the current contents but is not something to set directly. An empty Dictionary reports 0. Adding a new key increases the count by one, and removing a key decreases it by one.

The count is about distinct keys, not how many times your code has assigned values. A key can be associated with an item, and that item can later be changed without creating another entry. This makes Count useful for checking whether a lookup table is empty, reporting how many unique values were collected, or deciding whether a loop over the dictionary has anything to process.

For instance, a dictionary used to map employee IDs to department names has one entry per distinct ID. If it contains 350 IDs, Count returns 350, even if some departments appear many times among the items.

A complete VBA example

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so keep a recoverable version if the code changes workbook data. This example creates two entries, prints the count, updates one item, and checks the character length of each key.

Option Explicit

Sub ShowDictionaryCount()
    Dim dict As Object
    Dim key As Variant

    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "A-101", "North"
    dict.Add "B-204", "South"
    Debug.Print dict.Count  ' Prints 2

    dict("A-101") = "West" ' Changes the item; count stays 2
    Debug.Print dict.Count  ' Still prints 2

    If dict.Exists("B-204") Then
        Debug.Print dict("B-204")
    End If

    For Each key In dict.Keys
        Debug.Print CStr(key), Len(CStr(key))
    Next key
End Sub

In the Immediate window, the first count is 2 and the second count is also 2. The second assignment replaces the item for the existing key; it does not add a third pair. The loop separately prints each key and its character count. In the VBA editor, open the Immediate window with Ctrl+G on Windows.

What changes the count—and what does not

Adding a new key normally increases Count by one. Removing an existing key reduces it by one, and calling RemoveAll clears the entries so that the count becomes zero. Changing an existing item does not change the count. These simple rules help explain most unexpected results.

Keys must be unique under the Dictionary’s comparison rules. By default, string keys use binary comparison, which is case-sensitive: “ID7” and “id7” can be separate keys. If you want case-insensitive string matching, set CompareMode to TextCompare before adding entries. Changing the comparison mode after entries have been added can raise an error, so choose it when setting up the object.

Adding a key that already exists with Add raises an error rather than increasing the count. If the goal is to update an item, assign to the existing key; if the code must handle both cases, test with Exists before adding. Also remember that a dictionary’s item can be an object or another kind of value, so the count does not tell you how many characters, cells, or records are inside that item.

Dictionary entry count versus key or value length

Use Len when you need the number of characters in a text key or value. For example, Len("A-101") returns 5. If a dictionary stores a value under a known key, Len(CStr(dict("A-101"))) measures the string representation of that value. Converting with CStr is appropriate when the item is a value that can be converted to text; it is not a way to measure an object’s memory footprint.

To find the total number of characters across all keys, loop through the keys and add each key’s Len to a running total. That is a different calculation from Count: a dictionary with three keys of lengths 2, 5, and 8 has a count of 3 and a total key-character count of 15.

Arrays also require a separate calculation. A Dictionary can store an array as an item, but Count still counts the key-item pair, not the elements in that array. For a one-dimensional array with a nonempty range, its number of elements is UBound(arr) - LBound(arr) + 1. Be aware that an uninitialized or empty array may not have usable bounds, so code should handle that case rather than assuming every array has elements.

Is there a maximum Dictionary length?

There is no small, practical fixed entry limit that you should treat as the Dictionary’s “length.” The number of entries that can be stored depends on available memory, the size and type of keys and items, and the runtime environment. The Count property is exposed as a VBA Long, but that does not mean a workbook can realistically store anything close to the theoretical range of that data type; memory and performance constraints will matter first in ordinary use.

For large collections, consider what each entry stores and whether the Dictionary is the right structure. Thousands or tens of thousands of entries are routine in many VBA tasks, but performance depends on the work performed around the lookups as well as the number of pairs. A slow macro may be caused by repeatedly reading worksheet cells inside a loop, not by Count itself. Reading a range into an array, processing it in memory, and writing results back in a batch can reduce worksheet interaction.

Do not use Count as a memory estimate or as proof that every entry is valid. It reports the current number of pairs only; it does not check whether values are blank, duplicated in some other sense, or suitable for the next operation.

Creating a Dictionary in Excel VBA

On Windows, late binding is a common way to create a Dictionary without setting a reference in the VBA project. Declaring it as Object and using CreateObject("Scripting.Dictionary") lets the code run without choosing the Microsoft Scripting Runtime reference in advance. The trade-off is that VBA cannot provide the same compile-time type checking and member suggestions that an explicitly typed object can.

For early binding, open the VBA editor, choose Tools > References, and select Microsoft Scripting Runtime if it is available. Then declare a variable as Scripting.Dictionary. This can make code easier to explore in the editor, but a workbook that depends on that reference may need the reference available on another computer too.

Excel for the web does not run VBA macros. On Excel for Mac, availability and behavior of the Windows Scripting Runtime can differ by Excel version and setup, so do not assume a Windows CreateObject example will work unchanged. Test the object on the target Mac; if it is unavailable, use a compatible alternative such as a VBA Collection or a custom implementation. The VBA editor shortcut is Alt+F11 on Windows; on Mac it is typically Option+F11, sometimes with Fn as well, depending on the keyboard.

Common counting mistakes

The most frequent mistake is asking a Dictionary for a property named Length. The Scripting Dictionary exposes Count, not a string-style Length property. Another is treating the count as the number of attempted additions. Failed additions of duplicate keys do not create entries, and updating an existing item does not increase the count.

It is also easy to confuse the Dictionary’s default item access with a test for whether a key exists. Use Exists when you need to test for a key. Accessing an item for a key that was not previously added can create that key in some usage patterns, which may alter the count and hide a missing-key bug. When correctness depends on the key already being present, test first.

Finally, do not infer case behavior from how a key looks on a worksheet. The Dictionary’s CompareMode setting determines how string keys are compared; it is separate from worksheet sorting or cell formatting. If “abc” and “ABC” should be treated as the same key, set text comparison before inserting either one and test the behavior with representative data.

When to use a Collection or another approach

A VBA Collection also has a Count property, so it can report how many items it contains. It may be an option when working across environments where Scripting Dictionary support is uncertain. However, a Collection does not offer the same built-in Exists test, and its key-handling behavior differs. If fast key-based lookups and explicit existence checks are central to the task, a Dictionary is often more convenient on a supported platform.

For worksheet data that should remain visible and editable, a table with Excel filters, formulas, or built-in duplicate-removal tools may be more suitable than keeping everything in a VBA object. Choose the structure based on what you need to do: use a Dictionary for in-memory key-to-item lookup, and use worksheet features when the data needs to be inspected or maintained by workbook users.

Frequently Asked Questions

What is the VBA Dictionary Count property?
Count returns the number of key-item pairs currently in the Dictionary. An empty Dictionary has a count of 0.

Does updating a Dictionary value increase Count?
No. Assigning a new item to an existing key replaces its item and leaves the number of pairs unchanged.

How do I get the length of a Dictionary key?
Use Len on the key converted to text when needed, such as Len(CStr(key)). This returns character count, not the number of dictionary entries.

Can a VBA Dictionary contain duplicate keys?
No. Keys are unique under the Dictionary’s comparison mode. Adding an existing key with Add raises an error; use Exists to check first or assign to the existing key to update its item.

Can Excel for the web run a VBA Dictionary?
No. Excel for the web does not execute VBA macros. Use desktop Excel for VBA, and check platform compatibility when sharing a macro-enabled workbook with Mac users.

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.