VBA Dictionary With Multiple Values: Practical Methods

VBA & macros · Updated November 18, 2026

Yes. A VBA Scripting.Dictionary can hold multiple values for one key, but each key still maps to a single item: make that item a Collection, an array, or another dictionary. For values that can vary in number, a dictionary of Collections is usually the clearest option; for a fixed set of fields, an array or nested dictionary may fit better.

What a VBA dictionary stores

A dictionary is a key-to-item lookup structure. Each key must be unique, and its item can be a number, text, an array, or an object. The important distinction is that “multiple values” are not separate items attached to the same key automatically. Instead, the item itself must be a container that holds those values.

For example, imagine grouping several email addresses under each person’s name. The dictionary key could be the person’s name, and its item could be a Collection of addresses. Looking up the name returns the container; you then read or add entries in that container. This pattern also works for grouping order numbers by customer, categories by product, or worksheet row details by an identifier.

On Windows, VBA can use the COM-based Scripting.Dictionary. The examples below use late binding, which avoids adding a project reference and is suitable for Excel for Microsoft 365 on Windows. A dictionary is useful when fast lookups by key matter; it is not a worksheet range and does not automatically keep its contents synchronized with cells.

Choose the right container

Choose the inner structure according to how the grouped values behave. The outer dictionary provides the lookup by key; the inner container determines how the values are stored and accessed.

  • Collection: A good default when each key can have a changing number of values. Collections grow as items are added and are easy to loop through.
  • Array: Useful when every key has a known, fixed set of values, such as a latitude and longitude or three monthly totals. You need to manage array bounds and reassign an edited array to the dictionary item.
  • Nested dictionary: Useful when values have their own meaningful keys, such as a customer’s order ID mapped to an order date. It supports lookups at both levels.
  • Custom class: Appropriate when each key represents a structured record with named properties and validation rules. It takes more setup than a Collection or array.

If your values are just a short, fixed group, an array can be concise. If the count changes, a Collection is usually easier to maintain. If you need to find an inner value by a second key rather than by position, use a nested dictionary.

Store a changing number of values in a Collection

This example groups text values under a person’s name. It creates a Collection the first time a name appears, then adds subsequent values to the same Collection. The Exists check is essential: adding the same name to the dictionary twice would raise an error because dictionary keys must be unique.

Sub GroupValuesByName()
    Dim groups As Object
    Dim items As Collection
    Dim person As String

    ' Create a late-bound dictionary (Windows Excel).
    Set groups = CreateObject("Scripting.Dictionary")

    person = "Ravi"
    If Not groups.Exists(person) Then
        Set items = New Collection
        groups.Add person, items
    End If
    Set items = groups(person)
    items.Add "ravi@example.com"

    person = "Ravi"
    If Not groups.Exists(person) Then
        Set items = New Collection
        groups.Add person, items
    End If
    Set items = groups(person)
    items.Add "ravi.work@example.com"

    Debug.Print groups("Ravi").Count  ' Prints 2
    Debug.Print groups("Ravi")(1)     ' Prints the first address
End Sub

The first lookup returns a Collection object, so the assignment uses Set. The item number in a VBA Collection starts at 1, not 0. Here, the key “Ravi” has one dictionary item—the Collection—and that Collection contains two email addresses. For a real macro, put the repeated add-and-check logic in a helper procedure if you will use it in several places.

Run macros only on a copy of the workbook until you have verified the result. VBA macros cannot be undone with Ctrl+Z, so a mistaken change may require closing without saving or restoring a backup.

Use an array for a fixed set of values

An array suits data whose shape is predictable. For instance, each product might have exactly three attributes: color, size, and material. Store those three values as the dictionary item, then retrieve the array into a Variant before reading or changing it.

Sub StoreFixedValues()
    Dim records As Object
    Dim fields As Variant

    Set records = CreateObject("Scripting.Dictionary")
    records.Add "Mug-17", Array("blue", "large", "ceramic")

    fields = records("Mug-17")
    Debug.Print fields(0)  ' Prints blue
    Debug.Print fields(1)  ' Prints large
End Sub

The built-in Array function produces a zero-based array here, so its first element is index 0. That differs from the one-based indexing of a Collection. Keep the indexing convention clear when you combine the two structures.

When editing an array stored in a dictionary, retrieve it, change the local array, and assign it back to the key. This makes the update explicit and avoids confusion about whether a change to a temporary value has been retained:

Sub UpdateFixedValues()
    Dim records As Object
    Dim fields As Variant

    Set records = CreateObject("Scripting.Dictionary")
    records.Add "Mug-17", Array("blue", "large", "ceramic")

    fields = records("Mug-17")
    fields(1) = "medium"
    records("Mug-17") = fields

    Debug.Print records("Mug-17")(1)  ' Prints medium
End Sub

Arrays are less convenient when some keys have two values and others have twenty. They also do not provide named fields: fields(1) is less self-explanatory than a property named Size. Use comments or constants for indexes if the array’s positions are not obvious.

Use a nested dictionary for named values

When values have their own identifiers, use a dictionary inside a dictionary. In the example below, a customer key leads to another dictionary whose keys are order IDs. The inner dictionary can then hold an order date, status, or another object as its item. The same pattern can be extended to more levels, though deeply nested structures can become harder to read.

Sub StoreValuesByTwoKeys()
    Dim customers As Object
    Dim orders As Object

    Set customers = CreateObject("Scripting.Dictionary")
    Set orders = CreateObject("Scripting.Dictionary")

    orders.Add "Order-104", "2026-10-12"
    orders.Add "Order-105", "2026-10-18"
    customers.Add "Northwind", orders

    Set orders = customers("Northwind")
    Debug.Print orders("Order-105")  ' Prints 2026-10-18
End Sub

As with the Collection example, use Set when assigning a dictionary object to an object variable. Nested dictionaries are particularly useful when you need to test whether an inner key exists before retrieving it. Check both levels: first confirm the customer exists in customers, then confirm the order exists in that customer’s dictionary.

Loop through the grouped values

To process every group, loop over the outer dictionary’s keys, retrieve the container for each key, and then loop through that container. This example prints every value from the Collection-based structure. The outer dictionary’s keys are used to find the corresponding Collections.

Sub PrintGroupedValues()
    Dim groups As Object
    Dim items As Collection
    Dim key As Variant
    Dim i As Long

    Set groups = CreateObject("Scripting.Dictionary")
    Set items = New Collection
    items.Add "ravi@example.com"
    items.Add "ravi.work@example.com"
    groups.Add "Ravi", items

    For Each key In groups.Keys
        Set items = groups(key)
        For i = 1 To items.Count
            Debug.Print CStr(key), items(i)
        Next i
    Next key
End Sub

The code uses For Each over Keys and a numeric loop for the Collection’s one-based item positions. Do not assume dictionary keys will be returned in a particular order. If order is important, store an explicit ordering field or maintain a separate ordered list. A dictionary is primarily for key-based lookup, not for enforcing display order.

Common problems and how to avoid them

  • Duplicate-key error: A key can be added only once. Use Exists before Add, or retrieve the existing container and append to it.
  • Missing Set for objects: Assigning a Collection or nested dictionary to an object variable requires Set. A missing Set often causes an object-related error.
  • Confusing indexes: Collections normally start at 1. Arrays created with Array normally start at 0. Check the array’s bounds rather than assuming all containers index the same way.
  • Changing key comparison after adding items: If case-insensitive key matching is required, set CompareMode before adding keys. Changing it after entries exist can cause an error.
  • Assuming keys are ordered: Do not rely on the order returned by Keys for reports or output.
  • Using a key that is not unique: If two records share a name, the name alone may not identify a record. Use a stable identifier or a composite key, and handle separators carefully if you combine fields into one string.

For example, if “Alex” can identify more than one person, a dictionary keyed only by first name will group those records together. That may be exactly what you want for aggregation, but it is wrong for individual record lookup. Decide whether the key represents a group or a unique record before designing the structure.

Windows, Mac, and alternatives

CreateObject("Scripting.Dictionary") relies on Windows COM automation. In Excel for Microsoft 365 on Windows, it is a common late-binding approach. Early binding is also possible by enabling Tools > References > Microsoft Scripting Runtime in the VBA editor, then declaring a Scripting.Dictionary variable. Early binding provides access to the type information while writing code, but a project that depends on that reference may need adjustment on another computer.

Excel for Microsoft 365 on Mac does not provide the Windows Scripting Runtime COM component in the same way, so CreateObject("Scripting.Dictionary") may fail there. For code that must run on both Windows and Mac, consider VBA’s built-in Collection for key-based storage, a custom class or dictionary implementation designed for cross-platform VBA, or worksheet-based lookup structures. Collections are available in VBA on both platforms, but they do not offer all the same dictionary features, such as an Exists method; code must handle missing keys accordingly.

For simpler tasks, a two-column worksheet table can be easier to inspect and maintain than an in-memory object. A dictionary is most useful when a macro repeatedly needs to group or retrieve data during a run. If the data must persist after the macro ends, write it back to a worksheet or save it elsewhere: the dictionary itself is held in memory and disappears when the procedure or Excel session ends.

Frequently Asked Questions

Can one VBA dictionary key have more than one value?
Yes. Store a Collection, array, nested dictionary, or custom object as the key’s single item. Add or retrieve the individual values through that container.

What is the simplest way to add multiple values to one key?
Use a dictionary of Collections when the number of values can change. Create the Collection when the key first appears, then retrieve it and add each new value.

Can a VBA dictionary item be an array?
Yes. An array can be stored as the item, and it works well for a fixed set of values. Retrieve it into a Variant, edit it, and assign it back when you need to update an element.

Why does CreateObject(“Scripting.Dictionary”) fail on my Mac?
The Scripting Runtime dictionary is a Windows COM component and is not available through the same mechanism in Mac Excel. Use a Collection, a cross-platform VBA implementation, or another suitable data structure.

Does a VBA dictionary keep keys in insertion order?
Do not rely on it for a required ordering. If output order matters, store that order separately or sort the keys before writing results.

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.