VBA Array of Arrays: How Nested Arrays Work

Yes. VBA can store an array inside an element of a Variant array, creating a jagged or “array of arrays” structure. You cannot declare a strongly typed array-of-arrays with syntax such as Dim values()() As Long; instead, use Variant elements to hold the inner arrays, or use a two-dimensional array when every row has the same size.

What an Array of Arrays Means in VBA

An array of arrays is an outer array whose elements each contain a separate inner array. For example, one inner array might hold a region name and its monthly figures, while another holds a different region and a different number of figures. Because each inner array is independent, the rows do not have to be the same length. This is also called a jagged array.

VBA does not offer a dedicated jagged-array type declaration. A practical workaround is to use Variant variables: a Variant can contain an array, and an element of a Variant array can hold that Variant array. The result is flexible, but it is less strictly checked than an array declared with a specific element type such as Long.

This distinction matters when planning your data. If all records have the same fields and number of values, a conventional two-dimensional array is usually clearer. If groups have different lengths or different contents, an array of arrays can represent that irregular structure without adding empty placeholder values.

Create a Nested Array

The following procedure creates an outer array with two elements. Each element stores an inner array. It uses Array to make the inner arrays and reads one of their values.

Sub CreateNestedArray()
    ' Save a copy of the workbook before running a macro.
    Dim groups(0 To 1) As Variant
    Dim rowData As Variant

    groups(0) = Array("North", 12, 15)
    groups(1) = Array("South", 9, 11)

    rowData = groups(0)
    Debug.Print rowData(LBound(rowData))
End Sub

The first inner array contains three items: a label and two numbers. The second also has three items, but VBA does not require it to. For example, you could store a different number of values in each outer element. The line that copies groups(0) into rowData gives you a convenient local Variant containing that inner array.

The example uses an explicitly zero-based outer array, so its outer indexes are 0 and 1. The lower bound of an array created with Array can depend on the module’s Option Base setting. Use LBound and UBound rather than assuming the inner array starts at zero or one.

Read and Loop Through the Inner Arrays

A dependable pattern is to get one inner array into a temporary Variant, then loop over its bounds. This makes the two levels of indexing explicit and works even when inner arrays have different lengths.

Sub PrintNestedArray()
    ' Save a copy of the workbook before running a macro.
    Dim groups(0 To 1) As Variant
    Dim rowData As Variant
    Dim i As Long
    Dim j As Long

    groups(0) = Array("North", 12, 15)
    groups(1) = Array("South", 9)

    For i = LBound(groups) To UBound(groups)
        rowData = groups(i)
        For j = LBound(rowData) To UBound(rowData)
            Debug.Print rowData(j)
        Next j
    Next i
End Sub

The outer loop visits each stored array. The inner loop uses that particular array’s own lower and upper bounds, so the shorter second array does not cause an attempt to read a nonexistent third element. In real code, you can replace Debug.Print with processing, validation, or output logic appropriate to the data.

It may be possible to write compact expressions that appear to index both levels at once, but using a temporary Variant is easier to understand and troubleshoot. It is especially helpful when you need to modify an inner array, inspect its bounds, or pass it to another procedure.

Change a Value Inside a Nested Array

When updating a nested value, retrieve the inner array, change the local array, and assign it back to the outer element. This explicit round trip avoids relying on assumptions about how a particular expression handles the nested Variant.

Sub UpdateNestedArray()
    ' Save a copy of the workbook before running a macro.
    Dim groups(0 To 1) As Variant
    Dim rowData As Variant
    Dim itemIndex As Long

    groups(0) = Array("North", 12, 15)
    groups(1) = Array("South", 9, 11)

    rowData = groups(0)
    itemIndex = LBound(rowData) + 1
    rowData(itemIndex) = 20
    groups(0) = rowData
End Sub

Here, the second item in the first inner array changes from 12 to 20. Calculating the position from LBound makes the example resilient to a different lower bound for arrays created with Array. The final assignment stores the changed inner array back into the outer array.

These are macros: save a copy of the workbook before running them. VBA changes are not undone with Excel’s Ctrl+Z command, and running a macro can change workbook data or structure.

When to Use a Two-Dimensional Array Instead

If the data forms a regular grid, a two-dimensional array is normally the simpler choice. For example, a table with a fixed set of columns for region, January, and February has a consistent shape. A two-dimensional Variant array can represent its rows and columns directly, with indexing such as values(rowIndex, columnIndex).

Choose a two-dimensional array when every row has the same number of fields.
Choose nested arrays when groups have different lengths or when each group is itself a separately managed collection.
Choose a collection or dictionary when you need convenient keyed lookup or frequent additions and removals.

Excel ranges naturally fit the rectangular case. Assigning a multi-cell range’s Value property to a Variant produces a two-dimensional array of values. That array is convenient for reading or writing a block of worksheet cells, but it is not the same shape as a jagged array. If the ultimate destination is a worksheet range, a rectangular two-dimensional array is usually easier to transfer back in one operation.

A nested structure can still be useful while processing irregular input, but you may need to convert it to a rectangular layout before writing it to cells. Decide what should fill missing positions—blank values, an error marker, or a separate status field—rather than silently assuming Excel will reshape the nested data for you.

Common Errors and Boundaries to Check

Nested arrays are flexible, but their flexibility shifts responsibility to the code. Each inner array can have its own bounds and length, and an outer element might not contain an array at all if it has not been initialized as expected. Check the data before indexing it, especially when it comes from user input or another procedure.

Assuming a zero lower bound: Use LBound(inner) and UBound(inner). Do not infer the bounds from the outer array.
Reading beyond an inner array: A valid outer index does not guarantee that a requested inner index exists. Check that index against the inner array’s bounds.
Mixing values and arrays: A Variant array can hold different kinds of values. Establish a consistent convention, such as “every outer element contains an array,” and validate it before processing.
Expecting a typed jagged declaration: VBA does not support declaring a nested typed array with repeated parentheses. Use Variant containers or select a different data structure.
Trying to resize the wrong level: ReDim applies to a dynamic array variable. To resize an inner array, first retrieve it into a suitable Variant variable, resize that array as appropriate, and store it back in the outer element.

Also consider what happens when an inner array is empty or uninitialized. Calling LBound or UBound on an array that has not been allocated can raise an error. If empty groups are possible, include an initialization or validation step rather than assuming every element is ready to loop through.

Alternatives for More Structured Data

An array of arrays is not the only way to store related data. A two-dimensional array is compact for uniform records, while a Collection can hold a sequence of objects or values that grows as items are added. A Scripting.Dictionary is useful when items should be found by a unique key, such as a department code. These alternatives have different behaviors and are not interchangeable without considering how the data will be accessed.

For records with named fields, a user-defined type can make code easier to read, for example by naming fields rather than relying on positions such as item 0 or item 1. User-defined types have their own rules about where they can be declared and what they can contain, so they are not a drop-in substitute for arbitrary nested Variants. For a modest macro, a Variant-based nested array may be perfectly adequate; for a larger project, named structures can make the meaning of each value clearer.

Choose based on the operations you need, not only on how the data looks when first created. Ask whether you will frequently search by name, append or remove records, write a rectangular block to a sheet, or allow groups to have different sizes. Those requirements often make the appropriate structure obvious.

Frequently Asked Questions

Can VBA have an array of arrays?
Yes. Store inner arrays in elements of a Variant array. VBA does not provide a dedicated strongly typed jagged-array declaration.

Can I write Dim data()() As Long?
No. That is not valid VBA syntax for declaring an array of arrays. Use Variant elements containing arrays, or use a two-dimensional array if the data is rectangular.

How do I find the size of an inner array?
Copy the selected inner array into a Variant variable, then use LBound and UBound on that variable. The bounds may differ from those of the outer array.

Should I use a nested array or a two-dimensional array?
Use a nested array for groups that can have different lengths. Use a two-dimensional array when all rows and columns have a consistent shape, especially when transferring a block of values to or from worksheet cells.

Can I put an array of arrays directly into a worksheet range?
Do not assume a jagged array will map to a rectangular range. Excel range values are naturally represented as a two-dimensional array; convert irregular data to a rectangular structure before writing it if that is the intended layout.

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.