🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA 2D Arrays in Excel — Two-Dimensional Arrays, Grids, and Range.Value

|

VBA 2D Arrays in Excel — Two-Dimensional Arrays, Grids, and Range.Value

TL;DR — A VBA 2D array is one array with two subscripts — a grid of rows and columns, g(row, column) — not an array of arrays. Dim g(1 To 3, 1 To 4) is three rows by four columns. The single most useful fact for Excel work: reading Range("A1:D3").Value hands you a 1-based two-dimensional Variant array in one step, and writing an array back with Range(...).Value = arr writes it in one step — which is why array processing is dramatically faster than looping cell by cell.

Sub SheetToArrayAndBack()
    Dim data As Variant
    data = Range("A1:D100").Value          ' one hit on the sheet -> 1-based 2D array

    Dim r As Long
    For r = 1 To UBound(data, 1)           ' rows
        data(r, 4) = data(r, 2) * data(r, 3)   ' col D = col B * col C, in memory
    Next r

    Range("A1:D100").Value = data          ' one hit back -> instant
End Sub

A 2D array is where arrays and worksheets meet: a sheet is a grid, and the fastest way to work with a block of cells is to pull it into a 2D array, compute in memory, and write it back. It builds directly on sizing arrays with ReDim and reading their shape with UBound — a grid just has a bound per dimension. The one thing that trips people up is that Range.Value arrays are 1-based, and that ReDim Preserve cannot add rows.

What you'll learn

  • The mental model — a grid with two subscripts, g(row, column), not nested arrays
  • Declaring and addressing a 2D array with Dim g(1 To 3, 1 To 4) and g(r, c)
  • The big one: Range.Value gives you a 1-based 2D Variant array in a single step
  • Writing an array back to a range in one shot — and why that is so much faster
  • Two edge cases: a single cell is not an array, and a single column is (n, 1)
  • Adding rows: why ReDim Preserve cannot, and what to do instead
  • How a jagged "array of arrays" differs from a true 2D array

The mental model: one array, two subscripts

A one-dimensional array is a numbered row of boxes: a(i). A two-dimensional array is a grid — you address each box with two numbers, a row and a column: g(r, c). Crucially it is a single array that happens to be addressed two ways, not a list whose elements are themselves lists:

Dim g(1 To 3, 1 To 4) As Long   ' 3 rows, 4 columns = 12 elements
g(1, 1) = 10                     ' row 1, column 1
g(3, 4) = 99                     ' row 3, column 4

The first subscript is conventionally the row, the second the column — the same order you read a cell reference. Keep that order consistent and a 2D array maps cleanly onto a rectangular block of cells.

Declaring and looping a grid

Declare the two bounds, then loop with a nested For — outer over rows, inner over columns — using UBound with a dimension number:

Dim g(1 To 3, 1 To 4) As Long
Dim r As Long, c As Long
For r = LBound(g, 1) To UBound(g, 1)       ' rows
    For c = LBound(g, 2) To UBound(g, 2)   ' columns
        g(r, c) = r * 10 + c
    Next c
Next r

UBound(g, 1) is the row count, UBound(g, 2) the column count. Looping from LBound to UBound on each dimension means the same code works whether the array is 1 To n (as Range.Value gives you) or zero-based.

The big one: Range.Value is a 1-based 2D array

This is the fact that makes 2D arrays worth learning. Reading the .Value of a multi-cell range gives you, in a single step, a two-dimensional Variant array whose bounds start at 1 — regardless of where the range sits on the sheet:

Dim data As Variant
data = Range("C5:E7").Value      ' a 3-row, 3-column block anywhere on the sheet
' data is now data(1 To 3, 1 To 3) - ALWAYS 1-based, row then column
Debug.Print data(1, 1)          ' the value of C5
Debug.Print data(3, 3)          ' the value of E7

Note two things. First, the array is 1-based, not 0-based — so data(1, 1) is the top-left cell, and a For i = 0 loop would miss the first row and hit error 9. Second, the indexes are relative to the block, not to the sheet: data(1, 1) is the range's top-left cell whether the range is A1 or C5. Declare the receiving variable as Variant (not As Long() etc.) — VBA fills a fresh 2D Variant for you.

Write it back in one shot

The same trick works in reverse. Assign a correctly shaped 2D array to a range's .Value and Excel writes the whole block at once:

Range("C5:E7").Value = data     ' writes all 9 cells in a single operation

This is why the array approach is fast. Touching cells one at a time crosses the boundary between VBA and Excel on every read and write — thousands of round trips for a big range. Reading into an array, computing in memory, and writing back means just two crossings total. For anything past a few hundred cells, this is the difference between a macro that feels instant and one you watch grind. The written range must match the array's shape (rows × columns), so size the target range to UBound(arr, 1) rows by UBound(arr, 2) columns.

Two edge cases that bite

A single cell is not an array. Range("A1").Value returns a plain scalar, not a 1 To 1, 1 To 1 array — so code that assumes an array will fail on a one-cell range. If the range might be a single cell, test with IsArray() before you index it.

A single row or column is still 2D. Range("A1:A10").Value is data(1 To 10, 1 To 1) — ten rows, one column — so you still address it as data(i, 1), not data(i). The same goes for a single row: data(1, j). It is easy to forget the second subscript on a "one-dimensional" slice of a sheet.

Adding rows: ReDim Preserve cannot

Because ReDim Preserve only resizes the last dimension, you cannot grow the row count (the first dimension) of a 2D array while keeping data. Three practical options:

  • Size once. If you know the row count up front (a last row, a record count), ReDim the grid to its final size before the loop and never Preserve.
  • Grow the columns instead. Declare the array as columns × rows so the dimension you extend is the last one, then transpose on the way out with Application.Transpose.
  • Rebuild. For genuinely unknown growth, collect into a Collection or a jagged structure and build the 2D array once at the end.

For sheet work the first option almost always applies, because the row count is a cheap thing to learn before you start.

Jagged arrays are a different thing

A true 2D array is rectangular — every row has the same number of columns. A jagged array is a one-dimensional array whose elements are themselves arrays, so each "row" can have a different length. You address it with two sets of brackets, not two subscripts:

Dim jagged(1 To 2) As Variant
jagged(1) = Array(1, 2, 3)      ' a 3-element row
jagged(2) = Array(9, 8)         ' a 2-element row
Debug.Print jagged(1)(3)        ' 3   <- two bracket pairs, not (1, 3)

Jagged arrays are handy for ragged data, but they do not interoperate with Range.Value, which is always rectangular. When the data is a rectangle — which a sheet block always is — reach for a true 2D array.

How ExcelMaster helps

Working with a block of cells efficiently is one pattern — read Range.Value into a 1-based 2D array, compute in memory, write it back in one shot — and the traps are all in the details: the 1-based index, the single-cell scalar, the row you cannot grow with Preserve.

ExcelMaster lets you describe the task — "for every row in the used range, put quantity times price in column E" — and it reads the block into a 2D array, loops from 1 to UBound(arr, 1), writes the result back in a single assignment, and handles the single-cell and single-column cases so the macro is both correct and fast. You keep the workbook and the code.

Frequently asked questions

How do I declare a 2D array in VBA?

Give two bounds separated by a comma: Dim g(1 To 3, 1 To 4) As Long creates a grid of three rows and four columns. Address an element with two subscripts, g(row, column), for example g(2, 3). For a dynamic 2D array, declare it open with Dim g() and size it later with ReDim g(1 To 3, 1 To 4).

How do I read a range into a 2D array in VBA?

Assign the range's .Value to a Variant: Dim data As Variant: data = Range("A1:D100").Value. VBA fills a 1-based two-dimensional array, data(1 To rows, 1 To columns), in a single step. Address cells as data(row, column), where the indexes are relative to the top-left of the range. This is the fastest way to process many cells.

Why is my Range.Value array 1-based instead of 0-based?

Because that is how Excel returns it — a Range.Value array always starts at index 1 on both dimensions, no matter where the range is on the sheet. Loop from LBound(data, 1) to UBound(data, 1) (or simply 1 To UBound) so you do not skip the first row or run off the end with a 0-based loop.

How do I write a 2D array back to a range?

Assign it to the range's .Value: Range("A1:D100").Value = data. The range must have the same shape as the array — the same number of rows and columns — so size the target with UBound(data, 1) rows and UBound(data, 2) columns. This writes the whole block in one operation, far faster than cell by cell.

Can I add a row to a 2D array in VBA?

Not with ReDim Preserve — it can only resize the last (column) dimension, so growing the row count raises error 9. Either size the array to its final row count before the loop, declare it as columns × rows and transpose, or build it from a Collection and create the 2D array once at the end.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-24.

Related guides: VBA ReDim · VBA UBound · VBA Array · VBA For Loop · VBA Range