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. Excel for the web cannot create, run, or edit VBA macros. Open the workbook in the Excel desktop app to work with macros.
On this page8
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. Before the code does anything, check the environment it needs: desktop Excel, a macro-enabled workbook (.xlsm) and macros allowed for this file. If the workbook came by email or download, Office can block its macros until the file is trusted. 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 outcome you expected 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. Step through the code once (Debug ▸ Step Into in the VBA editor) and watch the sheet after each line. It shows exactly what the macro changes before you let it run end to end.
In Google Sheets
Google Sheets does not run VBA. A macro written for Excel has to be rewritten in Google Apps Script (Extensions ▸ Apps Script), and an .xlsm file converted to Sheets keeps its cells but drops the code. 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 and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “excel vba ubound” as code you are responsible for: know what it changes, keep a copy of the workbook before running it and keep the file in a macro-enabled format. In Google Sheets and Excel for the web this code does not run; those need a different mechanism.
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.