Excel VBA Jagged Arrays: How to Create and Use Them

VBA & macros · Updated November 21, 2026

Excel VBA does not have a dedicated jagged-array type, but you can make a jagged array by storing separate arrays inside the elements of a Variant array. This lets each inner array hold a different number of values, which is useful when rows or groups do not all have the same shape.

What a jagged array means in VBA

A regular two-dimensional array has a fixed rectangular shape: every row has the same number of columns. A jagged array is different. It is an outer array whose elements each hold their own inner array, so one element might contain two values while another contains four. The inner arrays can also represent different kinds of data, such as a name and score in one row and a name, score, and note in another.

VBA does not declare a special type such as JaggedArray. The usual approach is to use Variants, because a Variant can hold an array as well as a single value. The outer array is therefore an array of Variant elements, and each element contains an inner array. This is a practical VBA pattern rather than a separate built-in data structure.

Jagged arrays are distinct from multidimensional arrays. In a two-dimensional array, an item is addressed with two indices, such as a row and a column, and its dimensions form a rectangle. In a jagged array, you first select an outer element and then access an index in the inner array stored there. The lengths of those inner arrays can differ.

Create a jagged array with Array

The Array function provides a compact way to build a small jagged array. Qualifying it as VBA.Array makes the zero-based bounds explicit and avoids dependence on the module’s Option Base setting.

Sub ShowJaggedArray()
    Dim records As Variant
    Dim person As Variant

    ' Each outer element contains an inner array.
    records = VBA.Array( _
        VBA.Array("Name", "Score"), _
        VBA.Array("Mia", 92), _
        VBA.Array("Leo", 85))

    ' Copy one inner array to a Variant before reading its values.
    person = records(1)
    Debug.Print person(0), person(1)
End Sub

The example creates three inner arrays, each with two items. The outer index 1 selects the second inner array, which contains Mia and 92. The Immediate window displays those two values when the procedure runs. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows. In Excel for Mac, the VBA editor and its window shortcuts can differ by keyboard and Office version; use the editor’s View menu if the shortcut is unavailable.

To show unequal lengths, replace the assignment with a set of inner arrays containing different numbers of items:

Sub ShowUnequalRows()
    Dim records As Variant
    Dim person As Variant

    ' The inner arrays have two, three, and four values.
    records = VBA.Array( _
        VBA.Array("Mia", 92), _
        VBA.Array("Leo", 85, "Retake"), _
        VBA.Array("Noah", 78, "Review", True))

    person = records(2)
    Debug.Print person(0), person(1), person(2), person(3)
End Sub

Here, the final record has four values, but the first has only two. That variation is the defining feature. Since a row can have no value at a requested position, code should check its bounds before trying to read that position.

Read and loop through the inner arrays

VBA’s LBound and UBound functions return the lower and upper index bounds of an array. Use them instead of assuming every inner array starts at zero or has a fixed length, particularly when arrays may come from different sources. The following loop prints every item in every inner array, even when their lengths differ.

Sub PrintJaggedArray()
    Dim records As Variant
    Dim rowItem As Variant
    Dim rowIndex As Long
    Dim itemIndex As Long

    ' Build rows with different numbers of items.
    records = VBA.Array( _
        VBA.Array("Mia", 92), _
        VBA.Array("Leo", 85, "Retake"), _
        VBA.Array("Noah", 78, "Review", True))

    For rowIndex = LBound(records) To UBound(records)
        rowItem = records(rowIndex)

        For itemIndex = LBound(rowItem) To UBound(rowItem)
            Debug.Print rowItem(itemIndex);
        Next itemIndex

        Debug.Print
    Next rowIndex
End Sub

Assigning the selected inner array to rowItem makes the two levels clear: use records(rowIndex) to select a row, then rowItem(itemIndex) to select a value. It also avoids relying on chained indexing such as records(rowIndex)(itemIndex), which is less clear and can be awkward in VBA. If your inner arrays might be empty, test for that case before looping; an empty or uninitialized array does not always behave like a populated array with ordinary bounds.

Build jagged arrays dynamically

For data that is not known until a macro runs, declare the outer structure as a dynamic Variant array and size it with ReDim. Then assign an array to each element. This is useful when the number of groups is calculated from worksheet data or user input.

Sub BuildGroups()
    Dim groups() As Variant
    Dim groupItem As Variant

    ' Size the outer array for three groups.
    ReDim groups(0 To 2)

    ' Store a separate inner array in each Variant element.
    groups(0) = VBA.Array("Red", "Blue")
    groups(1) = VBA.Array("Green")
    groups(2) = VBA.Array("Gold", "Silver", "Bronze")

    groupItem = groups(2)
    Debug.Print groupItem(0), groupItem(1), groupItem(2)
End Sub

This uses a dynamic outer array, but the inner arrays are still independently sized. To add another outer element while preserving existing elements, use ReDim Preserve on the one-dimensional outer array. In VBA, preserving existing values while resizing a multidimensional array is more restricted: ReDim Preserve can change only its last dimension. Keeping the outer array one-dimensional avoids that particular limitation, though each inner array still needs to be managed separately.

When the amount of data grows, repeatedly resizing arrays can make code harder to maintain. A common alternative is to determine the required outer size first, allocate it once, and then fill each element. If you do not know each inner length in advance, create each inner array separately or use a collection while gathering items, then convert the results if a Variant-array structure is required.

Choose between jagged and rectangular arrays

Use a jagged array when the number of items genuinely varies by group. Examples include a list of students with different numbers of test results, categories with different numbers of entries, or records where optional fields are present only for some items. A rectangular two-dimensional array is generally simpler when every record has the same fields and the same number of columns.

  • Choose a rectangular array for uniform tabular data. Its row-and-column structure is straightforward to traverse and often maps naturally to a worksheet range.
  • Choose a jagged array when different groups need different lengths, and preserving those lengths is useful to the program.
  • Choose a Collection when you need to add or remove items flexibly and do not need array indexing for every operation.
  • Choose a Dictionary when entries need to be retrieved by unique keys rather than by numeric position. The Dictionary object is not itself a jagged array, but it may better fit keyed data.

A jagged array is not automatically a better way to represent worksheet data. Excel ranges are rectangular, so writing uneven rows back to a range requires deciding what to put in missing positions, often an empty value. For a fixed table, a two-dimensional array can usually be transferred to a range more directly. A jagged structure is most helpful while processing irregular groups in VBA, rather than as a direct mirror of a worksheet grid.

Common problems and bounds

Index assumptions are a frequent source of errors. VBA.Array creates zero-based arrays, while arrays created with other declarations may have different bounds depending on how they were declared and whether Option Base is in effect. Do not assume that every array starts at zero. Check bounds with LBound and UBound, or deliberately establish a consistent convention when constructing the data.

  • Subscript out of range: The requested outer or inner index is outside that array’s bounds. Check the appropriate bounds before reading or writing.
  • Type mismatch: An element may contain a single value rather than an array, or may hold a value of a type your code cannot use as expected. Validate the contents before treating an element as an inner array.
  • Different row lengths: Do not loop through every row using the length of the first row. Find the bounds of the current inner array during each iteration.
  • Empty or uninitialized values: An outer element that has not been assigned an inner array is not ready for normal inner-array indexing. Initialize every element you plan to read.

Another misconception is that a jagged array is simply a two-dimensional array with blank cells. A rectangular array with empty values still has the same number of allocated positions in each row. A jagged array stores separate inner arrays whose lengths can vary. That distinction matters when counting items, checking bounds, and deciding how to export the data.

Use jagged arrays with Excel and VBA

For small or irregular in-memory data, jagged arrays can make the structure explicit. For larger datasets, consider whether a rectangular Variant array loaded from a range would be easier to process. Excel ranges are rectangular, and reading a block into a two-dimensional Variant array can reduce repeated worksheet reads. You can then use a jagged structure only for groups that actually need variable lengths.

VBA macros run in the desktop versions of Excel for Windows and Mac, but Excel for the web does not create or run VBA macros. A workbook containing a macro should be saved in a macro-enabled format such as .xlsm if the code needs to be retained. On Windows, open the Visual Basic Editor with Alt+F11; some Mac keyboards require Fn with the corresponding function-key shortcut, or you can open the editor from Excel’s Developer tools if available.

Before running a macro that changes workbook data, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, so a backup is the reliable way to recover from unintended changes. This is particularly important if the macro writes the processed data back to worksheet cells or changes workbook structure.

Frequently Asked Questions

Can VBA create a jagged array?
Yes. VBA has no dedicated jagged-array declaration, but a Variant array can contain inner arrays. The outer elements can therefore hold arrays of different lengths.

How do I access an item inside a jagged array?
Select the outer element, assign it to a Variant variable, and then index that variable. For example, assign records(1) to rowItem, then read rowItem(0). Check the relevant bounds before accessing an index.

Are jagged arrays zero-based in VBA?
Not necessarily. Bounds depend on how an array was created and, for some forms, the module’s Option Base setting. VBA.Array uses zero-based bounds, and LBound and UBound let code inspect the actual bounds.

Can I write a jagged array directly to a worksheet range?
Not as an uneven block: a worksheet range is rectangular. Decide how to represent missing positions, such as filling them with empty values, and build a rectangular two-dimensional array before assigning the result to a range.

Does Excel for the web support VBA jagged arrays?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to edit and run VBA code; Mac may have different editor shortcuts and interface details.

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.