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. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel vba ubound”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. 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. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
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. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
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. Here is the takeaway for “excel vba ubound”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.
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.