Performance Tips for VBA

Analogy categoryAnalogies

Why VBA Macros Get Slow

The dominant cost in most VBA macros is not computation but crossing the boundary between VBA and the Excel object model. Every time you read or write a cell, Excel may recalculate formulas, repaint the screen, fire worksheet events, and marshal data across the COM interface — and doing that thousands of times in a loop is what makes a macro crawl. The fastest VBA is therefore VBA that touches the worksheet as few times as possible. Understanding this single principle explains almost every optimisation technique that follows.

🏏

Cricket analogy: It's like a fielder throwing to the keeper on every single ball instead of relaying efficiently — the constant back-and-forth to the object model, not the running itself, is what tires the side and slows the over rate.

Disable the Overhead Around Your Work

Before a bulk operation, switch off the machinery Excel runs on every change and restore it afterward. Set Application.ScreenUpdating = False to stop repainting, Application.Calculation = xlCalculationManual to stop formulas recomputing after each write, and Application.EnableEvents = False to stop event handlers (like Worksheet_Change) firing on every edit. Combined, these commonly cut runtime by an order of magnitude. Wrap them so they are always restored — ideally through an error handler's cleanup label — because leaving Calculation on manual or events disabled after a crash is a confusing, common bug.

🏏

Cricket analogy: It's like closing the roof and clearing the ground of interruptions before a crucial session so play flows uninterrupted — then reopening everything at the innings break, just as you restore ScreenUpdating and events afterward.

vba

Sub FastFill()
    Dim scr As Boolean, calc As XlCalculation, evt As Boolean
    scr = Application.ScreenUpdating
    calc = Application.Calculation
    evt = Application.EnableEvents

    On Error GoTo CleanExit
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim n As Long: n = 100000

    ' SLOW: 100,000 separate round-trips to the object model -- avoid
    ' Dim i As Long
    ' For i = 1 To n: ws.Cells(i, 1).Value = i * 2: Next i

    ' FAST: build in a VBA array, write ONCE
    Dim data() As Double
    ReDim data(1 To n, 1 To 1)
    Dim i As Long
    For i = 1 To n
        data(i, 1) = i * 2
    Next i
    ws.Range("A1").Resize(n, 1).Value = data   ' single write of the whole block

CleanExit:
    Application.ScreenUpdating = scr            ' restore captured state
    Application.Calculation = calc
    Application.EnableEvents = evt
End Sub

Read and Write Ranges as Arrays

The single biggest speedup for data-heavy macros is to load an entire range into a VBA array in one assignment ('arr = ws.Range("A1:D10000").Value'), process it entirely in memory, then write it back in one assignment ('ws.Range(...).Value = arr'). This replaces tens of thousands of COM round-trips with exactly two. A range read into a Variant becomes a 1-based, two-dimensional array indexed (row, column), even for a single column. Looping over an in-memory array is often hundreds of times faster than looping over Cells, and it keeps the worksheet untouched until the final bulk write.

🏏

Cricket analogy: It's like a team studying the whole match footage in the dressing room and returning with one agreed plan, rather than running onto the pitch to confer after every ball — two trips replace thousands.

Prefer '.End(xlUp)' or a Variant array to find and process the used range rather than looping to a fixed large number like 1,048,576. Also avoid Select/Activate entirely — 'ws.Range("A1").Value = 5' is both faster and more reliable than 'Range("A1").Select: Selection.Value = 5', because selecting forces screen and focus changes you don't need.

If you set Application.EnableEvents = False or Calculation = xlCalculationManual and your macro errors out before restoring them, Excel stays in that state for the whole session — later formulas silently stop recalculating and change-events stop firing, which looks like a broken workbook. Always restore these settings in an error handler's cleanup path, and capture their original values first rather than blindly forcing them back to defaults.

The main VBA cost is crossing into the Excel object model, not raw computation — touch the worksheet as little as possible.
Disable ScreenUpdating, set Calculation to manual, and disable EnableEvents around bulk work, then restore them.
Read a whole range into a Variant array, process in memory, and write back in one assignment to replace thousands of round-trips with two.
A range read into a Variant is a 1-based two-dimensional array indexed (row, column), even for one column.
Avoid Select/Activate; work with fully qualified Range/Cells references directly.
Use .End(xlUp) or the array bounds to size loops instead of iterating to the maximum row.
Always restore performance settings in an error-handler cleanup path, capturing their original values first.