Excel VBA Dictionary of Arrays: How to Store and Use Data

VBA & macros · Updated November 21, 2026

Yes—you can store arrays as values in an Excel VBA Dictionary. On Windows, create a Scripting.Dictionary, assign an array to a key, then retrieve the value into a Variant to read or change its elements.

What a dictionary of arrays means

A VBA dictionary stores pairs: a key and its associated item. The key is typically a unique identifier, such as a product code or customer ID. The item can be an array, so one key can point to several related values rather than just one value.

For example, a dictionary might map the key P-104 to an array containing a product name, quantity, and unit price. The dictionary is useful when you want to find a record by its key without scanning a worksheet row by row. It is also useful for grouping or building data in memory before writing results back to a sheet.

The important distinction is that the dictionary key is not an array: the dictionary item is. Dictionary keys must be unique, while an array stored as an item can contain multiple values. A VBA array can hold different data types when it is a Variant array, which is convenient for small records with mixed fields.

Create a dictionary in Excel VBA

In Excel for Microsoft 365 on Windows, a common option is late binding: declare the dictionary as an Object and create it with CreateObject. This avoids setting a project reference manually. Alternatively, in the Visual Basic Editor, choose Tools > References and select Microsoft Scripting Runtime; then you can declare a variable as Scripting.Dictionary. Early binding provides IntelliSense and compile-time checking, but a workbook using that reference may need the same library available on another computer.

The Scripting Runtime dictionary is a Windows COM component. Do not assume that CreateObject("Scripting.Dictionary") will work in Excel for Mac or Excel for the web. Excel for the web does not run VBA macros. For a workbook that must run on Mac, use a portable design such as a Collection or a custom class, or check the target Mac setup and test the required component there.

Before running any macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, and this example is intended to be run from the Visual Basic Editor or as a macro in a saved macro-enabled workbook.

Store and retrieve an array value

This Windows example adds a product record, reads its values, changes the quantity, and assigns the modified array back to the same key. In the Visual Basic Editor, insert a standard module and run BuildDictionaryOfArrays. The values are for illustration; the macro prints its results in the Immediate window, which you can show with Ctrl+G in the editor.

Sub BuildDictionaryOfArrays()
    Dim dict As Object
    Dim record As Variant

    ' Create a late-bound Windows Scripting.Dictionary.
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare

    ' Store a zero-based Variant array under a unique key.
    dict.Add "P-104", Array("Notebook", 3, 2.5)

    ' Retrieve the array, then read and update its elements.
    record = dict("P-104")
    Debug.Print record(0), record(1), record(2)
    record(1) = record(1) + 1

    ' Write the updated array value back to the dictionary.
    dict("P-104") = record
    Debug.Print dict("P-104")(1)
End Sub

The first printed record contains Notebook, 3, and 2.5; after the quantity is increased, the final printed value is 4. The code uses Array(...), which creates a zero-based array: its elements are numbered 0, 1, and 2. That is why the product name is at index 0 rather than index 1.

When updating an array item, the clearest pattern is to retrieve it into a Variant, change the element, and assign the Variant back to the dictionary. This makes the read-modify-write operation explicit and avoids treating the dictionary item as though it were a worksheet range.

Read fields and loop through dictionary entries

Once an array is stored, its indexes act like field positions. In the example, index 0 is the name, index 1 is the quantity, and index 2 is the price. VBA does not know that index 1 means “quantity”; the code author must keep that layout consistent wherever the record is created and read.

For a single key, use the key to get the array and then access its indexes. Before requesting a key that may not exist, check dict.Exists(key). Requesting a missing key through the item property can create an empty entry, which is often an unwanted side effect when the intent was only to look up data.

To process all records, loop over the dictionary’s keys and retrieve each item. In late-bound VBA, declare the loop variable as a Variant. The basic pattern is:

  • Use dict.Keys to obtain the keys, or enumerate the dictionary directly when you need each key.
  • For each key, assign dict(key) to a Variant variable.
  • Read the record’s elements by their agreed indexes, then write or calculate with those values.

For larger projects, define named constants for the field positions, such as a constant for the product-name index. That is less error-prone than scattering unexplained numbers such as record(2) throughout a long procedure. A custom class with named properties can be even clearer when records have many fields.

Choose an array layout that fits the data

A simple one-dimensional array is appropriate for a small, fixed set of fields. For instance, a customer key might map to an array of name, city, and status. Keep the field order stable: if one part of the code treats position 2 as a city and another treats it as a status, the values may still run without an error but produce incorrect results.

Arrays created with Array(...) are convenient because they are compact and zero-based. You can also store a separately declared array in a Variant, but pay attention to its lower and upper bounds. Code that assumes an index of 0 can fail if the array instead begins at 1. Avoid relying on Option Base to change the bounds of an array made with the Array function; that function returns a zero-based array.

You can also store nested data, such as an array whose elements are themselves arrays, but the code becomes harder to read and maintain. For records with a variable number of values, or where fields need meaningful names, a Collection or a custom class may better communicate the structure. The right choice depends on whether the data is small and fixed or more complex and evolving.

Handle keys, duplicates, and text comparison

A dictionary can contain only one item for each key. Use Add when a duplicate key should be treated as an error. If you intend to replace an existing item, first check whether the key exists and then assign the new array to that key. Being explicit prevents accidental overwrites and makes the intended behavior easier to review.

For text keys, a dictionary’s CompareMode controls how it compares strings. Setting it to vbTextCompare makes comparisons case-insensitive, so keys such as abc and ABC are treated as equivalent. The example sets this property before adding any entries. Set the comparison mode before the dictionary contains data; changing it later can raise an error.

Choose keys that are stable and unique. A product code is usually safer than a product name if names can repeat or change. If a source worksheet contains duplicate identifiers, decide whether to reject duplicates, combine their data, or deliberately replace earlier values. A dictionary will not make that business rule decision for you.

Common errors and ways to avoid them

  • ActiveX or automation error at creation: CreateObject("Scripting.Dictionary") depends on the Windows Scripting Runtime. Confirm that the code is running on a supported Windows desktop setup; use a different data structure if cross-platform VBA is required.
  • Subscript out of range: Check the array bounds. An array made with Array(...) starts at zero, so the last element in a three-value example is record(2), not record(3).
  • Duplicate-key error: Check Exists before calling Add, or use an intentional update path when replacement is appropriate.
  • Wrong field appears in output: Confirm that every code path uses the same index-to-field mapping. Named constants or a class with named properties can reduce this risk.
  • Changes do not appear to persist: Retrieve the array into a Variant, make the change, and assign the whole value back to the dictionary item. This explicit approach is easier to reason about than relying on an indirect edit.

Also distinguish a missing key from an empty value. A key can exist while its item is empty or contains an array with empty elements. Use Exists to test for the key, then validate the retrieved data separately before using it.

When to use a dictionary instead of a worksheet or collection

A dictionary of arrays is most useful for in-memory lookups by unique key. If a macro repeatedly searches a large list for the same identifiers, building a dictionary once can make the code easier to organize than repeatedly scanning cells. It can also help consolidate rows by key before outputting a summary.

A worksheet table is often better when users need to inspect, filter, or edit the records directly. A Collection can be a more portable VBA option, but it does not provide the same built-in key lookup methods and update pattern as Scripting.Dictionary. A custom class is preferable when field names and validation matter more than compact storage. For a handful of values in a short macro, a dictionary may be unnecessary complexity.

For large data sets, avoid reading and writing one worksheet cell at a time if performance becomes a concern. A common design is to load a range into a Variant array, process values in memory, then write results to a range in one operation. The dictionary can support key-based lookups during that processing, while the worksheet remains the source or destination for the data.

Frequently Asked Questions

Can a VBA Dictionary item be an array?

Yes. A dictionary item can hold an array, commonly a Variant array created with Array(...). Retrieve it into a Variant to access its elements, and assign the changed array back to the item when updating it.

How do I get the second value from an array in a dictionary?

First retrieve the item into a Variant variable, then use its index. For a value created with Array(...), the second value is at index 1 because the first value is at index 0.

Can a dictionary key also be an array?

No. A dictionary uses individual keys for lookup, not arrays as keys. Use a suitable scalar key, such as a string or number. If a record has multiple identifying fields, you can create a carefully designed combined key, or use a different data structure.

Why does CreateObject fail for Scripting.Dictionary?

The late-bound creation call relies on the Windows Scripting Runtime. It may fail on a platform where that COM component is unavailable, including setups where the code must run in Excel for Mac or Excel for the web. Excel for the web does not run VBA; consider a Collection or another cross-platform design for Mac VBA.

Should I use an array, Collection, or custom class?

Use an array for a compact record with a small, fixed set of fields. Choose a Collection when portability or a simple ordered group matters, and a custom class when named fields, validation, or more complex behavior will make the code easier to maintain.

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.