Excel VBA Dictionary Items: How to Read and Loop Through Them

VBA & macros · Updated November 21, 2026

In Excel VBA, a Dictionary’s Item property returns the value stored under a particular key, while its Items method returns an array of all stored values. Use dict.Item("key") to get one value, or loop through dict.Keys and retrieve each matching item when you need key-value pairs.

What Item and Items Mean in a VBA Dictionary

A Dictionary stores pairs: a unique key and an associated item, often called a value. For example, a product code can be the key and its description or price the item. The key is used to locate the item; the item is the data you want to retrieve.

  • dict.Item(key) is a property that gets or sets the value for one key. It is the Dictionary’s default property, so dict(key) is a shorter equivalent.
  • dict.Items is a method that returns an array containing the values for all keys.
  • dict.Keys is a method that returns an array containing all the keys.

Singular and plural matter here: Item is not the same member as Items. Use the singular property when you know a key; use the plural method when you want the complete set of values. A Dictionary is not an Excel worksheet range, and its items are not automatically written to cells.

Create a Dictionary in Excel VBA

In Excel for Microsoft 365 on Windows, a common approach is late binding. It creates the Dictionary in code without requiring you to select a reference in the Visual Basic Editor. This example stores two product descriptions and retrieves one item:

Sub ReadDictionaryItem()
    Dim dict As Object
    Dim description As Variant

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

    dict.Add "P-104", "Desk lamp"
    dict.Add "P-208", "USB hub"

    ' Read the item associated with a known key.
    description = dict.Item("P-104")
    Debug.Print description
End Sub

The Immediate window displays Desk lamp. To view it in the Visual Basic Editor, press Ctrl+G on Windows. On a Mac, the equivalent shortcut is usually Control+G in the Visual Basic Editor, though keyboard mappings can vary. Save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z.

The example uses Dim dict As Object because late binding does not require a Dictionary-specific type declaration. Early binding is another Windows option: in the Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime, then declare the variable as Scripting.Dictionary. Early binding can provide autocomplete and compile-time checks, but a workbook using that reference may need the same library available on another computer.

Retrieve One Item by Its Key

When you have a key, use the Item property directly. For instance, after adding a key named P-104, dict.Item("P-104") returns its associated value. Because Item is the default property, dict("P-104") also works. The explicit form is often easier to read, especially for someone learning the Dictionary object.

The returned value is a Variant, so it can hold many kinds of data, including text, numbers, dates, or an Empty value. Assign it to a suitably typed variable when you know what the Dictionary contains; otherwise, a Variant is a flexible choice. If the stored value is an object rather than a simple value, object assignment requires object-reference handling with Set.

One important precaution: asking for an Item using a key that is not present can create that key with an Empty item. That can silently change the Dictionary and make a later lookup appear to have succeeded. Check for the key first when absence is possible:

Sub ReadItemIfPresent()
    Dim dict As Object
    Dim result As Variant

    ' Create a Scripting.Dictionary on Windows.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "P-104", "Desk lamp"

    If dict.Exists("P-104") Then
        result = dict.Item("P-104")
        Debug.Print result
    Else
        Debug.Print "Key not found"
    End If
End Sub

Exists is the Dictionary method intended for this check. Testing whether the returned value is blank is not a reliable substitute: an existing key can legitimately contain an empty string or Empty value, and a missing-key lookup can itself affect the Dictionary.

Loop Through Keys and Their Items

For most tasks involving pairs, loop over the keys and use each key to retrieve its corresponding item. The loop variable should be a Variant, because the Keys array contains Variants and keys do not have to be text:

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

    ' Create and populate a Scripting.Dictionary on Windows.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "P-104", "Desk lamp"
    dict.Add "P-208", "USB hub"

    For Each key In dict.Keys
        Debug.Print key & " = " & dict.Item(key)
    Next key
End Sub

This prints each key alongside its value in the Immediate window. Using dict.Item(key) makes the relationship clear: the loop gets a key, then looks up the item associated with that key. The same approach is useful when values need to be checked, transformed, or written to a worksheet.

You can also loop over dict.Items if you only need the values and do not need to know which key belongs to each one. However, a values-only loop loses that key association in your code. If the relationship matters, iterate over Keys and retrieve each Item instead.

Use Items as an Array

The Items method returns the Dictionary’s values as an array. Store that result in a Variant, then use array bounds to visit each value. Check Count first so the code does not try to find array bounds for an empty Dictionary:

Sub PrintAllItems()
    Dim dict As Object
    Dim values As Variant
    Dim i As Long

    ' Create and populate a Scripting.Dictionary on Windows.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "P-104", "Desk lamp"
    dict.Add "P-208", "USB hub"

    If dict.Count > 0 Then
        values = dict.Items
        For i = LBound(values) To UBound(values)
            Debug.Print values(i)
        Next i
    End If
End Sub

For this example, the array contains Desk lamp and USB hub. The values returned by Items correspond by position to the keys returned by Keys. That makes paired arrays useful in some situations, but do not assume they are sorted alphabetically or numerically. If order matters, sort the results explicitly or build a separate ordered structure.

Add, Change, and Count Dictionary Items

Use Add to create a new key-item pair. Keys must be unique: trying to add a key that already exists raises an error. To change the value for a key that is already present, assign to its Item property. For example, after creating the Dictionary, dict.Item("P-104") = "LED desk lamp" replaces that key’s prior value.

The Item property can also create a key when assigning to a key that does not yet exist. This differs from Add mainly in how the code expresses its intent: use Add when a duplicate should be treated as a problem, and assignment when you want to set or replace the value. In data-import routines, checking Exists before adding is a practical way to handle duplicates deliberately.

Use dict.Count to find how many key-item pairs are stored. Use dict.Remove key to remove one pair, or dict.RemoveAll to clear the Dictionary. If you change a key’s spelling or identity, the Dictionary also has a Key property for that purpose; confirm the new key will not conflict with an existing one.

CompareMode, Case, and Key Matching

By default, a Dictionary compares text keys using binary comparison, which treats capitalization as significant: "East" and "east" can be distinct keys. If keys should be case-insensitive, set CompareMode to vbTextCompare before adding any entries. Changing CompareMode after the Dictionary contains data can cause an error.

For example, after creating the Dictionary and before its first Add, set dict.CompareMode = vbTextCompare. The choice is a data rule, not just a formatting preference. If case carries meaning in your identifiers, retain binary comparison; if users may enter the same name with different capitalization, text comparison may better fit the task. Also consider trimming accidental spaces in source data: text comparison does not make "East" and "East " the same key.

Common Problems and Platform Limits

  • Missing key: Use Exists before reading an uncertain key to avoid creating an unintended empty entry.
  • Duplicate key: Add fails if the key already exists. Check first or intentionally assign to the Item property to replace the value.
  • Unexpected capitalization behavior: Set CompareMode before adding entries, and choose a comparison rule that matches the data.
  • Assumed order: Dictionary results are not a sorting feature. Do not use their current enumeration order as a guarantee of alphabetical or chronological order.
  • Object creation error: CreateObject("Scripting.Dictionary") is intended for Windows. The Scripting Runtime is not a dependable cross-platform option in Excel for Mac; a macro that relies on it may not run there.

On Mac, Excel VBA is available, but Windows COM automation objects such as Scripting.Dictionary are not generally available in the same way. If a workbook must run on both Windows and Mac, consider a VBA Collection, arrays, or a custom class-based solution, and design for the differences. A Collection can store keyed values but does not provide the same Exists, Keys, and Items interface, so it is not a drop-in replacement. Excel for the web does not run VBA macros; use desktop Excel to run this code.

When a Dictionary Is Better Than Other Options

A Dictionary is especially useful when a macro needs fast lookups by unique identifier, such as mapping employee IDs to names or counting occurrences of product codes. Compared with scanning a worksheet range repeatedly, loading keys and values into memory can simplify repeated lookups. It is also convenient when building a unique list, because each key can appear only once.

Choose a different structure when the task does not need keyed lookup. A Collection may suit a simple ordered group of values, though it has different lookup behavior. A worksheet range is easier to inspect and share with users, and Excel formulas such as XLOOKUP or UNIQUE may solve a worksheet task without VBA in Microsoft 365 or Excel 2021 and later. A Dictionary is a VBA object, not a worksheet function; it is most appropriate when the work is part of a macro or other VBA procedure.

Frequently Asked Questions

What is the difference between Item and Items in VBA?

Item gets or sets the value for one specified key. Items returns an array of all values in the Dictionary. Use Keys with Items when you need to keep each value connected to its key.

How do I get a Dictionary value in VBA?

Use dict.Item("your key"), or the shorter default-property form dict("your key"). If the key may not exist, call dict.Exists("your key") first.

Can I loop through Dictionary items in Excel VBA?

Yes. Loop through dict.Keys with a Variant variable and print or process dict.Item(key). If you need only values, assign dict.Items to a Variant array and loop over its bounds.

Why does a missing Dictionary item look blank?

A missing key’s Item lookup can produce an Empty value and may add that key to the Dictionary. Because a real item can also be empty, use Exists to distinguish a missing key from a stored blank value.

Does Scripting.Dictionary work in Excel for Mac?

It is a Windows scripting object and should not be assumed to work in Mac Excel. For a workbook that must support both platforms, use a cross-platform design such as arrays or a Collection, while accounting for their different features.

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.