Excel VBA Dictionary Add Array: Store and Retrieve Values

VBA & macros · Updated November 21, 2026

Yes. In Excel VBA, add an array to a Dictionary by passing it as the item in the Dictionary’s Add method, then retrieve it into a Variant variable. For example, dict.Add "SKU-104", Array("Notebook", 12, True) stores a three-element array under the key SKU-104.

What a Dictionary stores

A Dictionary is a key-and-item collection: each key identifies one associated item, much like a label on a drawer. It is useful when you want to look up a record by a unique identifier, such as a product code, employee number, or category name. Unlike a worksheet range, a Dictionary is held in memory while the macro runs.

The item does not have to be a single number or string. It can be a Variant containing an array, so one key can point to several related values. For example, a product code might map to a product name, quantity, and whether the product is active. The key and the array item serve different purposes: the key identifies the record; the array holds its fields.

A Dictionary key must be unique. If you try to add an existing key with Add, VBA raises an error rather than silently replacing the existing item. Use Exists to check first when duplicate keys are possible. Arrays are appropriate as items, but they are not suitable as Dictionary keys; use a scalar key such as a string or number.

Add and retrieve a one-dimensional array

The built-in VBA Array function is a concise way to create a one-dimensional array. Its elements are indexed from zero, so the first value in the example is at index 0, the second at index 1, and the third at index 2. Assign the retrieved Dictionary item to a Variant before reading its elements.

Option Explicit

Sub AddArrayToDictionary()
    Dim dict As Object
    Dim product As Variant

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

    ' Add a Variant array as the item for this key.
    dict.Add "SKU-104", Array("Notebook", 12, True)

    ' Retrieve the array into a Variant.
    product = dict("SKU-104")

    Debug.Print product(0)  ' Notebook
    Debug.Print product(1)  ' 12
    Debug.Print product(2)  ' True
End Sub

The variable product is declared as Variant because the Dictionary item is an array value, not a separate VBA object that needs the Set keyword. Use Set when assigning an object reference; use ordinary assignment for an array held in a Variant. This distinction is a frequent source of confusion when working with Dictionaries.

The sample prints values in the Immediate window in the Visual Basic Editor. Open that window with Ctrl+G on Windows. On Mac, the Visual Basic Editor and available shortcuts can vary by keyboard and Excel release; use the View menu in the editor if a shortcut differs. Excel for the web does not run VBA macros.

Store a two-dimensional array

You can also put a two-dimensional array in an item. This is helpful when one Dictionary key represents a small table or record with rows and columns. Create the array in a Variant, add that Variant to the Dictionary, then retrieve it into another Variant. The following example uses explicit bounds, so the indexes are easy to see.

Option Explicit

Sub AddTwoDimensionalArray()
    Dim dict As Object
    Dim record As Variant
    Dim savedRecord As Variant

    Set dict = CreateObject("Scripting.Dictionary")

    ' Build a two-row, two-column array.
    ReDim record(1 To 2, 1 To 2)
    record(1, 1) = "Product"
    record(1, 2) = "Quantity"
    record(2, 1) = "Notebook"
    record(2, 2) = 12

    dict.Add "Order-501", record

    ' Retrieve, read, and update the array item.
    savedRecord = dict("Order-501")
    Debug.Print savedRecord(2, 1)  ' Notebook
    savedRecord(2, 2) = 15
    dict("Order-501") = savedRecord
End Sub

After the update, assigning savedRecord back to dict("Order-501") makes the changed array the Dictionary item. This retrieve-edit-reassign pattern is clear and dependable. Do not assume that changing a separate local variable automatically updates the item in every situation; explicitly putting the changed value back avoids ambiguity and makes the intended update visible.

Late binding and early binding

The examples use late binding: Dim dict As Object and CreateObject("Scripting.Dictionary"). This avoids selecting a reference in the Visual Basic Editor and is convenient when sharing a workbook between Windows computers with different reference settings. The object’s methods are not listed with the same compile-time assistance as an early-bound Dictionary, so careful spelling matters.

With early binding, set a reference to the Microsoft Scripting Runtime in the Visual Basic Editor through Tools > References, then declare the variable as Scripting.Dictionary and create it with New Scripting.Dictionary. Early binding provides code completion and can make errors easier to spot. A reference setting may need attention on another computer if that library is unavailable or registered differently, so late binding is often chosen for broadly shared macros.

The Scripting Runtime approach shown here is for Excel on Windows. Excel for Mac does not provide the same Windows COM automation support for Scripting.Dictionary, and Excel for the web does not execute VBA. If the workbook must run on Mac or in a browser, choose a cross-platform design rather than assuming that changing late binding to early binding will solve compatibility.

Choose between arrays, collections, and ranges

A Dictionary is especially useful when you need fast lookups by key, want to test whether a key exists, or need to group several values under one identifier. An array is a good item when those values are a compact, fixed-shape record and you can keep track of what each position means. For instance, an array with positions for name, quantity, and status is efficient, but the code must consistently use those positions.

Other structures may be clearer in different cases:

  • Use a simple variable when each key maps to just one value and there are only a few values to manage.
  • Use a Collection when you want an ordered collection and do not need the Dictionary’s direct key lookup and Exists method.
  • Use a custom class when each record has named properties, validation, or behavior. A property such as product.Quantity is easier to understand than an unexplained array position such as product(1).
  • Use a worksheet range when the data needs to be visible, edited by users, or saved as ordinary workbook data. A Dictionary in memory is not a persistent storage system.

For larger tabular data, a two-dimensional Variant array can be convenient for reading or writing a range in a block, while a Dictionary can provide lookups by a key. Some macros use both: load rows into an array, build a Dictionary for matching, and write results back to the sheet. The right choice depends on whether the main task is display, ordered processing, or key-based retrieval.

Common errors and how to avoid them

A duplicate-key error is one of the most common problems. Before adding a value that may already exist, check the key with Exists. If the key exists, decide deliberately whether to keep the first item, replace it, or combine it with the new data. For a replacement, retrieve the existing item if needed, then assign the new Variant to dict(key); do not call Add again for the same key.

Array bounds are another source of bugs. The Array function creates zero-based elements, whereas an array declared with ReDim record(1 To 2, 1 To 2) starts at 1. Use LBound and UBound if the bounds might vary, and do not assume every array starts at index zero. For a two-dimensional array, specify the dimension when checking bounds.

Also keep these practical points in mind:

  • Declare the receiving variable as Variant when retrieving an array item.
  • Check dict.Exists(key) before retrieving a key that may not be present; a missing item can produce an error.
  • Set CompareMode before adding keys if you want case-insensitive text matching. The default comparison is binary, so differently cased text keys can be treated as different keys.
  • Use Remove to delete one key or RemoveAll to clear the Dictionary when appropriate.
  • Remember that Dictionary data exists only in memory until your code writes it somewhere persistent, such as a worksheet or file.

Safe setup and workbook compatibility

Before running a macro that changes workbook data, save a copy of the workbook. VBA actions cannot be reversed with Ctrl+Z, including changes made by a macro that writes Dictionary contents to cells. Store the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA code, and only enable macros in files you trust.

To run the sample on Windows, open the workbook, press Alt+F11 to open the Visual Basic Editor, insert a standard module, paste the procedure, and run it from the editor. The first time you run a macro, Excel’s security settings may require you to enable macros for that trusted file. On Mac, keyboard shortcuts and VBA support differ, and the Windows Scripting.Dictionary creation method is not a portable solution. For a Mac-compatible workbook, consider a Collection or a custom class and verify the complete macro on the target Excel version.

Frequently Asked Questions

Can a VBA Dictionary item be an array?
Yes. An array can be stored as the item associated with a Dictionary key. Pass the array to Add, then retrieve the item into a Variant to access its elements.

How do I get an array back from a Dictionary?
Assign the item to a Variant, such as values = dict("Key1"), then read the array elements from that variable. Check that the key exists before retrieving it if it may be missing.

Can I change one value inside the stored array?
Retrieve the array into a Variant, change the required element, and assign the updated Variant back to the same Dictionary key. This makes the change to the stored item explicit.

Why does Dictionary Add give a duplicate-key error?
A Dictionary allows only one item per key. Use Exists to detect an existing key, then decide whether to skip the new record or replace or combine the current item.

Does Scripting.Dictionary work in Excel for Mac?
The Windows COM-based Scripting.Dictionary method is not a reliable cross-platform choice for Mac Excel. Excel for the web also cannot run VBA; use a supported alternative when the workbook needs to run outside Windows desktop Excel.

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.