Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Read a range into an array, then write it back

Pull the whole block into memory in one hit, loop it there, and write it back in one hit — the real speed pattern for big data.

1What it does

Reads an entire range into a 2-D array with a single assignment (data = Range.Value), loops over it entirely in memory, then writes the whole thing back in one assignment. Touching the sheet is by far the slowest thing VBA does — doing it twice instead of once per cell is what takes a heavy macro from minutes to under a second on large data.

2The code

Sub ArrayReadWrite()
    Dim ws As Worksheet, lastRow As Long, i As Long
    Dim data As Variant
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Pull the whole block into a 2-D array in ONE hit. data(row, col) now lives
    ' in memory — looping it is far faster than reading cells one at a time.
    data = ws.Range("A2:C" & lastRow).Value

    ' Work entirely in memory. Here: column 3 (total) = column 1 (qty) x column 2 (price).
    For i = 1 To UBound(data, 1)
        data(i, 3) = data(i, 1) * data(i, 2)
    Next i

    ' Write the whole array back in ONE hit.
    ws.Range("A2:C" & lastRow).Value = data
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾

1
Don't see the Developer tab in the ribbon?
You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
- Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
- Mac:Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
2
Paste in the code
Press Alt + F11 to open the Visual Basic editor, then Insert → Moduleand paste the snippet's code into the blank window. Close it with Alt + Q.
3
Run it
Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
4
Keep the macro — save as .xlsm
File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
5
Macros blocked?
Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

Transform tens of thousands of rows fast (recompute a column, reprice, reclassify).
Any heavy loop that reads and writes cells one at a time today.
Build a result in memory before touching the sheet at all.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Qty (A)	Price (B)	Total (C)
3	$20
5	$12
8	$15

Column C is empty.

5Pitfalls

A range read is 1-based and 2-D, so 0-based loops are off by one.

Fix: data(1, 1) is the top-left; loop For i = 1 To UBound(data, 1) and index columns from 1.

A single cell doesn't come back as an array.

Fix: Range("A2").Value is a scalar, not a 1x1 array — guard for a one-cell range, or force a multi-row range.

6No-code alternatives

Loop the cells directly — Fine for a few hundred rows; the array pattern only pays off at scale.
Speed wrapper — Stack with ScreenUpdating / Calculation off for the fastest run — see the speed snippet.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →