Work with Arrays – Speed Up Your Code

Home » pub » Tutorials » Work with Arrays – Speed Up Your Code

Boost your VBA programming skills by learning how to work with arrays in Excel. This tutorial explains one-dimensional and multi-dimensional arrays, declaring and resizing arrays, storing worksheet data in memory, looping through array elements, and writing results back to Excel efficiently. Ideal for Excel users, VBA developers, analysts, and automation professionals who want to create faster and more efficient macros.

Every time VBA reads or writes a cell individually in a loop, it crosses the boundary between VBA and the Excel object model. Each crossing has overhead. Across 10,000 cells, that overhead dominates execution time. Arrays eliminate this entirely. Read the whole range into an array in one call, process every value at VBA speed in memory, then write results back in one call. The same 10,000-row operation typically runs 50–100× faster. This guide covers array declaration, all three array types, reading and writing ranges, multi-dimensional arrays, and six complete practical examples.

The Three Array Types in VBA

VBA supports three array types distinguished by when and how their size is determined. Choosing the wrong type is the most common source of array-related runtime errors — specifically the "subscript out of range" error that occurs when you access an index beyond the array boundary.

Three array types — declaration and use: 1. FIXED-SIZE — size known at compile time: Dim scores(1 To 10) As Double ' 10 elements, indices 1–10 Dim grid(0 To 4, 0 To 3) As String ' 5×4 grid, 0-based 2. DYNAMIC — size set at runtime with ReDim: Dim names() As String ' declared without size ReDim names(1 To rowCount) ' sized once count is known ReDim Preserve names(1 To rowCount + 10) ' resize, keep existing values 3. VARIANT — from reading a Range: Dim data As Variant data = Range("A1:C1000").Value ' reads entire range into 2D array ' data(1,1) = first cell, data(1000,3) = last cell ' Always 1-based, always 2-dimensional even for a single column Best practice: always declare explicit bounds — 1 To n or 0 To n-1

Reading a Range into an Array

Assigning a Range's Value property to a Variant variable is the fastest way to read data from a worksheet. The result is always a two-dimensional array regardless of the range shape — even a single column produces a 2D array where the second dimension is always 1. Understanding this prevents the most common beginner error: treating a single-column range read as a 1D array.

Reading and writing ranges via arrays: ' ── READ ───────────────────────────────────────────────────── Dim data As Variant data = Worksheets("Data").Range("A1:C1000").Value ' data is a 2D array: data(row, col) ' data(1,1) = A1, data(1,2) = B1, data(1000,3) = C1000 ' First dimension = rows (1 To 1000) ' Second dimension = columns (1 To 3) ' ── PROCESS IN MEMORY ──────────────────────────────────────── Dim results() As Double ReDim results(1 To UBound(data, 1), 1 To 1) Dim i As Long For i = 1 To UBound(data, 1) results(i, 1) = data(i, 1) * data(i, 2) ' Col A × Col B Next i ' ── WRITE BACK ──────────────────────────────────────────────── Worksheets("Data").Range("D1:D1000").Value = results ' One write call puts all 1000 results back to the sheet

Example 1: Bulk Calculation — 50× Faster Than Cell-by-Cell

A macro needs to calculate a gross margin percentage for 20,000 rows and write results to column E. The cell-by-cell version takes 8–15 seconds on a typical machine. The array version takes under 0.2 seconds because it makes exactly two calls to the Excel object model — one read and one write — instead of 40,000 individual cell interactions. The performance difference scales linearly with row count.

Bulk margin calculation — slow vs fast: ' SLOW — 40,000 individual cell interactions: Sub CalcMargin_Slow() Dim i As Long For i = 2 To 20001 Cells(i,5).Value = (Cells(i,3).Value - Cells(i,4).Value) / Cells(i,3).Value Next i End Sub ' FAST — 2 object model calls total: Sub CalcMargin_Fast() Dim data As Variant, result() As Double, i As Long data = Range("A2:D20001").Value ' 1 read call ReDim result(1 To UBound(data,1), 1 To 1) For i = 1 To UBound(data, 1) If data(i, 3) <> 0 Then result(i, 1) = (data(i,3) - data(i,4)) / data(i,3) End If Next i Range("E2:E20001").Value = result ' 1 write call End Sub ' Typical: Slow ≈ 12 seconds | Fast ≈ 0.15 seconds

Example 2: Filtering Data in Memory

Reading data, filtering to matching rows, and writing only the matches avoids the need to use AutoFilter or Advanced Filter via VBA — both slower and more fragile. Instead, read the source into an array, collect matching rows into a second dynamic array, then write the collected rows in one operation. This approach also preserves the original data completely untouched, which is specifically important for audit compliance and undo requirements.

In-memory filter — extract rows where column D > 10000: Sub FilterLargeOrders() Dim src As Variant, matches() As Variant Dim i As Long, matchCount As Long src = Range("A2:D5001").Value matchCount = 0 For i = 1 To UBound(src, 1) If src(i, 4) > 10000 Then matchCount = matchCount + 1 Next i If matchCount = 0 Then Exit Sub ReDim matches(1 To matchCount, 1 To 4) Dim outRow As Long: outRow = 0 For i = 1 To UBound(src, 1) If src(i, 4) > 10000 Then outRow = outRow + 1 Dim c As Integer For c = 1 To 4: matches(outRow, c) = src(i, c): Next c End If Next i Sheets("Output").Range("A2").Resize(matchCount, 4).Value = matches End Sub

Example 3: Sorting an Array

VBA has no built-in array sort function. For small arrays (under a few hundred elements), bubble sort is sufficient and straightforward to implement. For larger arrays, a quicksort is significantly faster. Sorting in memory and writing the sorted result back to the sheet in one operation is still faster than any approach involving repeated cell reads during comparison, because all the comparison work happens at VBA speed without any COM overhead.

Bubble sort on a 1D array: Sub SortArray(arr() As Variant) ' Ascending bubble sort — modifies arr in place Dim i As Long, j As Long, temp As Variant Dim n As Long: n = UBound(arr) For i = 1 To n - 1 For j = 1 To n - i If arr(j) > arr(j + 1) Then temp = arr(j): arr(j) = arr(j+1): arr(j+1) = temp End If Next j Next i End Sub ' Usage — convert 2D single-column to 1D, sort, write back: Dim values() As Variant values = Application.Transpose(Range("A1:A100").Value) SortArray values Range("B1:B100").Value = Application.Transpose(values)

Example 4: Multi-Dimensional Array — Build a Summary Grid

A two-dimensional array models a grid directly — row and column indices map to a report layout. This example reads a transaction table and builds a 12×5 summary grid (12 months × 5 regions) by accumulating values into the correct array cell based on the month and region in each source row. Writing the completed grid to the sheet takes one call regardless of how many transactions were processed — consequently, the output is always fast even on very large source datasets.

Build a month × region summary grid: Sub BuildSummaryGrid() Dim src As Variant, grid(1 To 12, 1 To 5) As Double Dim i As Long, mo As Integer, reg As Integer src = Range("A2:C10001").Value ' Date, Region(1-5), Revenue For i = 1 To UBound(src, 1) mo = Month(src(i, 1)) reg = src(i, 2) If mo >= 1 And mo <= 12 And reg >= 1 And reg <= 5 Then grid(mo, reg) = grid(mo, reg) + src(i, 3) End If Next i Sheets("Summary").Range("B2:F13").Value = grid End Sub

Example 5: ReDim Preserve — Growing an Array Dynamically

ReDim Preserve resizes a dynamic array without losing existing values — specifically growing the last dimension only. This is useful when the final output count is not known before processing begins. However, ReDim Preserve inside a tight loop is slow because it copies the entire array on each resize. The best practice is to pre-size to an estimated maximum, then ReDim Preserve once at the end to trim to the actual count. This pattern avoids repeated copying while still adapting to the real data size.

Efficient pattern — avoid ReDim Preserve in loops: ' SLOW — resizes on every match (copies entire array each time): ReDim found(1 To 1): Dim fCount As Long: fCount = 0 For i = 1 To 10000 If data(i,1) = "Error" Then fCount = fCount + 1 ReDim Preserve found(1 To fCount) ' ← slow found(fCount) = data(i, 2) End If Next i ' FAST — pre-size then trim once: ReDim found(1 To 10000) fCount = 0 For i = 1 To 10000 If data(i,1) = "Error" Then fCount = fCount + 1 found(fCount) = data(i, 2) ' no resize in loop End If Next i If fCount > 0 Then ReDim Preserve found(1 To fCount)

Example 6: Array of Arrays — Jagged Structures

VBA does not natively support jagged arrays (arrays where each row has a different length), but a Variant array of Variant arrays achieves the same result. Each element of the outer array holds an inner array of varying size. This is particularly useful for grouping data rows by category where each category has a different number of members — for example, grouping order lines by customer ID before processing each customer's batch separately in memory.

Variant array of arrays — group rows by region: Dim groups(1 To 5) As Variant Dim i As Integer For i = 1 To 5 groups(i) = Array() ' initialise each group as empty Next i ' groups(1) might hold (100, 200, 150) ' groups(3) might hold (300, 250, 180, 220) ' Access item: groups(3)(2) = 180 (0-based inner array) ' Practical: collect row numbers per region, then process per region Dim rowsByRegion(1 To 5) As Variant For i = 1 To 5: rowsByRegion(i) = Array(): Next i For i = 1 To UBound(src, 1) Dim r As Integer: r = src(i, 2) ' Append i to rowsByRegion(r) using a helper function Next i

Troubleshooting VBA Arrays

All three issues below are the most common VBA array errors. Each has a specific cause that is identifiable in under one minute.

Subscript out of range — Run-time error 9

This error means you accessed an index outside the array's declared bounds. First, check the actual bounds using LBound(arr) and UBound(arr) in the Immediate Window (Ctrl+G in the VBA editor). When reading a Range into a Variant array, bounds are always (1 To rowCount, 1 To colCount) regardless of where the range sits on the sheet — row 500 in the sheet becomes index 1 in the array. Also check whether a single-column range read produced a 2D array when you expected 1D — use Application.Transpose to convert between them.

ReDim Preserve fails with type mismatch or subscript error

ReDim Preserve can only resize the last dimension of a multi-dimensional array. If you try to resize the first dimension of a 2D array, VBA raises an error. The solution is to declare 2D arrays with the dimension you want to extend as the last dimension — for example, (1 To colCount, 1 To rowCount) if rows need dynamic resizing. Alternatively, restructure the logic to collect into a 1D array whose final size is known before ReDim Preserve, or use a separate collection step followed by a single sized allocation at the end.

Writing an array back to the sheet produces #VALUE! in cells

When you assign an array to a Range's Value property, the range dimensions must exactly match the array dimensions. If the array dimensions are mismatched — for example, assigning a 1D horizontal array to a vertical range — the cells show #VALUE!. Use Resize to make the output range match the array precisely: Range("A1").Resize(UBound(arr,1), UBound(arr,2)).Value = arr. For a 1D array written to a column, transpose it first: Range("A1").Resize(UBound(arr)).Value = Application.Transpose(arr).

Frequently Asked Questions

How do I read a range into an array in VBA?+

Declare a Variant variable and assign the Range's Value property to it: Dim data As Variant, then data = Range("A1:D1000").Value. The result is a 2D array where the first index is the row (1 to 1000) and the second is the column (1 to 4), regardless of where the range sits on the sheet. A single-column range also produces a 2D array — data(row, 1) — not a 1D array. To get a 1D array from a single row or column, use Application.Transpose on the result to convert the dimensions.
Why are arrays so much faster than reading cells in a loop?+

Each call to Cells(i, j).Value crosses the boundary between the VBA runtime and the Excel COM object model, involving marshalling, type conversion, and context switching. This overhead is small per call but accumulates to seconds or minutes across tens of thousands of calls. Reading a range into an array makes one crossing to transfer all values at once, then all loop iterations operate in plain VBA memory with no COM overhead. Writing the array back makes one more crossing — total overhead is two COM calls regardless of how many rows are processed.
What is the difference between a fixed-size and dynamic array in VBA?+

A fixed-size array has its dimensions declared at compile time and cannot be resized: Dim scores(1 To 100) As Double. A dynamic array is declared without dimensions and resized at runtime using ReDim: Dim scores() As Double, then ReDim scores(1 To n) when n is known. Use fixed-size arrays when the count is constant and known at design time. Use dynamic arrays when the count depends on data — for example, the number of rows in a worksheet that changes with each run. ReDim Preserve resizes a dynamic array while keeping existing values, but can only extend the last dimension.
How do I write a VBA array back to a worksheet range?+

Assign the array to a range's Value property. The range must match the array dimensions exactly — use Resize to ensure this: Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr. For a 1D array being written to a column, transpose it first: Range("A1").Resize(UBound(arr)).Value = Application.Transpose(arr). Writing a correctly sized array to a range is a single COM call regardless of array size, making it dramatically faster than writing values to cells individually in a loop.