In Excel: Read a range in one statement with vals = ws.Range("A1:A10").Value, then loop from LBound(vals, 1) to UBound(vals, 1) — the array is two-dimensional and 1-based.
VBA macro: Arrays in VBA
Sub ReadRangeIntoArray()
Dim ws As Worksheet
Dim vals As Variant
Dim i As Long
Dim total As Double
Set ws = ActiveSheet
vals = ws.Range("A1:A10").Value ' 2-D and 1-based: vals(row, 1)
For i = LBound(vals, 1) To UBound(vals, 1)
If IsNumeric(vals(i, 1)) Then total = total + CDbl(vals(i, 1))
Next i
MsgBox "Total across " & UBound(vals, 1) & " rows: " & total, vbInformation
End SubAssigning a multi-cell range to a Variant produces a two-dimensional, 1-based array — vals(3, 1), never vals(2). The second argument to LBound and UBound picks the dimension, so UBound(vals, 1) is the row count and UBound(vals, 2) the column count.
How to run this macro
- Press Alt + F11 to open the VBA editor.
- Insert > Module.
- Paste the code above.
- Press
F5, or close the editor and run it from Developer > Macros. - Save the file as .xlsm so the macro is kept.
Read the whole range in one statement: vals = ws.Range("A1:A10").Value.
Loop with For i = LBound(vals, 1) To UBound(vals, 1) rather than assuming the bounds.
Index with both subscripts — vals(i, 1) for a single-column range.
Build the results in a second array of the same shape instead of writing to cells inside the loop.
Write everything back in one statement: ws.Range("B1:B10").Value = out.
What this does
An array holds many values under one name, and in Excel VBA its main job is speed. Every read or write against a cell crosses the boundary between VBA and Excel, and that crossing dominates the cost of a loop; pulling a whole range into memory in one statement, working there, and writing back once turns a task that takes half a minute into one that takes a fraction of a second. Arrays come in three flavours. A fixed array, Dim arr(1 To 10) As Double, has a size known when you write it. A dynamic array, Dim arr() As Double followed by ReDim arr(1 To n), is sized at run time — and ReDim Preserve grows it while keeping the contents, though only the last dimension may change. The third kind is the one that matters most here: assigning a multi-cell range to a Variant creates a 2-D, 1-based array automatically, indexed as (row, column) even when the range is a single column. LBound and UBound report the actual bounds, which is why looping between them is safer than assuming where an array starts. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. For “excel vba array”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That is what makes a workflow that saves repeating the same clicks every week useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
Sub ReadRangeIntoArray() copies A1:A10 into vals in a single statement, then totals the numeric entries without touching the sheet again. Note the indexing: vals(i, 1), not vals(i) — a range assignment is always two-dimensional, and forgetting the second subscript is the first error everyone hits. The reverse direction is just as useful: build a result array of the same shape and write it back with ws.Range("B1:B10").Value = out, which posts a thousand values in one operation. For a list whose length is not known ahead of time, declare Dim names() As String, count the items, then ReDim names(1 To n) before filling it. If you need to grow the array as you go, ReDim Preserve names(1 To UBound(names) + 1) works but reallocates every time — sizing once is far faster when the count can be worked out in advance. Arrays are the single biggest performance lever in Excel VBA, and the change is structural rather than fiddly: read once, compute in memory, write once. They also make the code easier to reason about, because the values stop moving under you while the loop is running. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. The short version of “excel vba array”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.
Common mistakes
- Indexing a range-derived array with one subscript. vals(3) raises "Subscript out of range" because the array is two-dimensional — it must be vals(3, 1).
- Assuming arrays start at 0. Range assignments are always 1-based, and Option Base 1 changes the default for declared arrays, so hard-coding either bound is fragile. LBound is the reliable answer.
- Calling ReDim Preserve on a lower dimension. Only the last dimension can change size; attempting any other raises "Subscript out of range" at run time rather than at compile time.
- Assigning a single cell to a Variant and treating it as an array. One cell yields a scalar, not a 1×1 array, so code that works on a range breaks the moment the range shrinks to one cell.
- Using ReDim Preserve inside a tight loop. Each call reallocates and copies the whole array, turning a linear job quadratic — count first and size once where you can.
Frequently asked questions
Why does my array index start at 1 and not 0?
Because it came from a range. Assigning cells to a Variant always produces a 1-based, two-dimensional array. Declared arrays start at 0 unless you write Dim arr(1 To n) or Option Base 1.
What do LBound and UBound do?
They return the lowest and highest valid index. The optional second argument picks the dimension: UBound(vals, 1) is the row count, UBound(vals, 2) the column count.
How do I resize an array without losing its contents?
ReDim Preserve arr(1 To newSize). It only works on the last dimension, and it copies the whole array each call, so size once up front whenever the final count is knowable.
Is an array really faster than looping over cells?
Substantially, on anything beyond a few hundred cells. The cost is in crossing between VBA and Excel, so one read plus one write beats thousands of individual cell accesses by orders of magnitude.
Other ways people ask this
This is also commonly searched as “excel vba array of arrays”, “excel vba arrays” and “excel vba string array”. They describe the identical operation, so you are in the right place no matter how you phrased it.
Why do people search for this in so many different ways?
Because the same task has many names. “excel vba array of arrays”, “excel vba arrays”, “excel vba string array” all point at the one operation explained on this page, which is why they all lead here.