VBA Array in Excel — Declare, Loop & Read a Range 100× Faster (7 Examples)

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-05

TL;DR — An array is one variable with many slots. The killer move in Excel VBA is reading a whole range into an array, working in memory, and writing it back once — instead of touching cells one at a time:

Sub FastUpdate()
    Dim arr As Variant
    arr = Range("A2:C10000").Value2          ' ONE read -> 2D array

    Dim i As Long
    For i = 1 To UBound(arr, 1)
        arr(i, 3) = arr(i, 2) * 1.1          ' work in memory (instant)
    Next i

    Range("A2:C10000").Value2 = arr          ' ONE write
End Sub

On 10,000 rows that's the difference between ~8 seconds and ~0.1. The rest of this guide builds up to that line and the gotchas around it.

Why arrays — and what they are

A normal variable holds one value. An array holds a list of values under one name, reached by index:

Dim scores(1 To 3) As Long
scores(1) = 90
scores(2) = 75
scores(3) = 60

Why bother? Speed and structure. Every time VBA reads or writes a worksheet cell it crosses a slow boundary between code and Excel. An array lives in memory, so looping over 10,000 array slots is thousands of times faster than looping over 10,000 cells.

Example 1 — Static arrays and the index base

A static array has a fixed size you set at declaration. Be explicit about the bounds so you never guess:

Dim months(1 To 12) As String     ' indexes 1..12 — readable
Dim zeroBased(0 To 9) As Double    ' indexes 0..9

Tip: Always write (1 To n) rather than (n). Dim a(10) actually creates 11 slots (0–10) unless you set Option Base 1. Spelling out (1 To 10) removes all doubt.

Example 2 — Dynamic arrays with ReDim

When you don't know the size up front, declare an empty array and size it later with ReDim:

Dim names() As String
Dim n As Long
n = Cells(Rows.Count, 1).End(xlUp).Row - 1   ' rows of data

ReDim names(1 To n)                          ' size it now
Dim i As Long
For i = 1 To n
    names(i) = Cells(i + 1, 1).Value
Next i

Plain ReDim wipes the contents. To keep what's already there, use ReDim Preserve.

Example 3 — ReDim Preserve (and its one big gotcha)

ReDim Preserve resizes without losing data — perfect for a growing list:

Dim hits() As String
Dim count As Long
count = 0

Dim cell As Range
For Each cell In Range("A2:A100")
    If cell.Value <> "" Then
        count = count + 1
        ReDim Preserve hits(1 To count)      ' grow by one, keep the rest
        hits(count) = cell.Value
    End If
Next cell

The gotcha: for a 2-D array, ReDim Preserve can only resize the last dimension. ReDim Preserve grid(1 To 5, 1 To 10) is fine if only the 10 changed; changing the 5 throws "Subscript out of range." Work around it by storing rows in the last dimension, or transpose.

Example 4 — UBound and LBound (never hard-code the size)

Ask the array how big it is instead of assuming:

Dim arr As Variant
arr = Array("Mon", "Tue", "Wed")             ' a 0-based array

Dim i As Long
For i = LBound(arr) To UBound(arr)           ' works whatever the base
    Debug.Print arr(i)
Next i

For a 2-D array from a range, pass the dimension: UBound(arr, 1) is the row count, UBound(arr, 2) the column count.

Example 5 — Read a range into an array (the 100× trick)

This is the whole reason to learn arrays. SomeRange.Value2 hands you a two-dimensional, 1-based array in a single read:

Sub SumColumnFast()
    Dim arr As Variant
    arr = Range("A2:C10000").Value2          ' arr(row, col), both 1-based

    Dim total As Double, i As Long
    For i = 1 To UBound(arr, 1)
        total = total + arr(i, 2)            ' column 2 = Qty
    Next i

    MsgBox "Total Qty: " & total
End Sub

Two things to remember:

1. The result is always 1-based — arr(1, 1) is the top-left cell, regardless of Option Base.
2. A single cell does not return an array. Range("A1").Value2 is just the value; only a multi-cell range becomes a 2-D array.

Example 6 — Loop an array with For Each

When you only need the values (not their position), For Each reads cleanest:

Dim regions As Variant
regions = Array("North", "South", "East", "West")

Dim r As Variant
For Each r In regions
    Debug.Print r
Next r

Example 7 — Build arrays fast with Array() and Split()

Two one-liners that save a pile of assignment statements:

' Array() — a quick literal list (0-based)
Dim days As Variant
days = Array("Mon", "Tue", "Wed", "Thu", "Fri")

' Split() — turn delimited text into an array (0-based)
Dim parts As Variant
parts = Split("apple;banana;cherry", ";")
Debug.Print parts(0)        ' apple
Debug.Print UBound(parts)   ' 2  (three items, 0..2)

Join(parts, ", ") does the reverse — array back to a string.

Common array mistakes (and the fix)

Skip the array plumbing — just describe the transform

Arrays are fast, but ReDim Preserve, UBound(arr, 2), and 1-based-vs-0-based bookkeeping are exactly the kind of detail that turns a five-minute job into an afternoon. ExcelMaster Agent lets you state the goal — "load the whole table, raise every price by 10%, and write it back" — and it generates the read-into-array, process, write-back pattern correctly, including the bounds. Try it free →

Related guides

• VBA Range in Excel — Reference, Read & Write Cells
• VBA Dictionary in Excel — Lookups, Dedup & Grouping
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

What does an array do in VBA? An array stores many values of the same kind under one name, reached by index (arr(1), arr(2)…). It lets you read and process a list of data in memory, which is far faster than reading the same data cell by cell from the worksheet.

How do I use an array instead of a range in Excel VBA? Read the range into an array in one shot — arr = Range("A1:C10000").Value2 — loop over the array with UBound, then write the array back with Range(...).Value2 = arr. This replaces thousands of slow cell touches with one read and one write.

What is the difference between ReDim and ReDim Preserve? ReDim resizes a dynamic array and erases its contents. ReDim Preserve resizes while keeping the existing values — but for a 2-D array it can only change the last dimension.

Why is my VBA array 0-based? Arrays from Array() and Split() start at index 0 by default (unless you set Option Base 1). Arrays read from a range with .Value2 are always 1-based. Loop LBound(arr) To UBound(arr) so the base never matters.

How do I get the length of a VBA array? Use UBound(arr) - LBound(arr) + 1. For a 2-D array from a range, the row count is UBound(arr, 1) and the column count is UBound(arr, 2).