VBA Best Practices

Analogy categoryAnalogies

Why Discipline Matters in VBA

VBA is forgiving to a fault: it will happily let you use undeclared variables, silently coerce types, and record clunky macros full of Select and Activate. That leniency is exactly why disciplined conventions matter — VBA projects rarely start large, but they accrete over years until a workbook holds thousands of lines that no one dares refactor. Best practices in VBA are less about clever tricks and more about defensive habits that keep code readable, fast, and debuggable long after the original author has moved on. The single highest-leverage habit is turning on Option Explicit at the top of every module.

🏏

Cricket analogy: Skipping discipline in VBA is like batting without a helmet because the net session felt easy; it works until a bouncer arrives and the whole innings collapses.

Declare Everything: Option Explicit

Placing Option Explicit at the top of every module forces you to declare every variable with Dim, and the compiler will refuse to run if a name is undeclared. This single line catches an entire class of bugs: a mistyped variable name that would otherwise silently create a new empty Variant. Combine it with the 'Require Variable Declaration' setting in the VBE options so new modules include it automatically. Pair explicit declaration with meaningful names and specific types — Dim invoiceTotal As Currency is both self-documenting and faster than an untyped Variant, because VBA no longer has to resolve the type at runtime.

🏏

Cricket analogy: Option Explicit is the third umpire reviewing every catch; a typo that would have slipped through gets caught before the batsman walks off wrongly.

vba

Option Explicit

' Good: declared, typed, no Select/Activate, error-handled
Sub UpdatePrices()
    On Error GoTo CleanFail
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Products")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim rng As Range
    Set rng = ws.Range("C2:C" & lastRow)

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim data As Variant
    data = rng.Value          ' read entire range into an array
    Dim i As Long
    For i = 1 To UBound(data, 1)
        data(i, 1) = data(i, 1) * 1.05   ' 5% increase in memory
    Next i
    rng.Value = data          ' write back in one shot

CleanExit:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Exit Sub
CleanFail:
    MsgBox "UpdatePrices failed: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

Avoid Select and Activate

The macro recorder produces code littered with .Select and .Activate — Range("A1").Select followed by Selection.Value = 1. This pattern is slow, fragile, and hard to read, because it depends on hidden global state (whatever is currently selected) and forces Excel to visibly move the cursor. Instead, reference objects directly: ws.Range("A1").Value = 1. Set object variables once (Set ws = ThisWorkbook.Worksheets("Data")) and work through them. This makes code faster, immune to the user clicking elsewhere mid-run, and far easier to reason about because every operation states exactly which object it targets.

🏏

Cricket analogy: Select/Activate is walking to each fielder to hand them the ball; direct references are the captain signalling instructions from the crease without moving anyone.

For bulk data work, the biggest speed win is reading a Range into a Variant array, processing it in memory, and writing the whole array back in one assignment. Touching cells one at a time crosses the VBA-to-Excel boundary on every access, which is orders of magnitude slower.

Error Handling and Cleanup

Robust VBA uses structured error handling rather than sprinkling On Error Resume Next everywhere. The reliable pattern is On Error GoTo a labelled handler, with a single exit label that restores application state (ScreenUpdating, Calculation, EnableEvents) whether the routine succeeds or fails. Reserve On Error Resume Next for narrow, deliberate cases — such as testing whether an object exists — and always re-enable error trapping immediately afterward. Leaving Resume Next active hides real failures and produces the most frustrating class of VBA bugs: code that appears to succeed while silently doing nothing.

🏏

Cricket analogy: Structured handling is having a nightwatchman and a plan for a collapse; On Error Resume Next everywhere is ignoring the scoreboard while wickets tumble unnoticed.

Never leave On Error Resume Next active across a whole procedure. It silences every runtime error, so bugs manifest as 'nothing happened' with no message. Scope it to the single line you are protecting and follow it immediately with On Error GoTo 0 or your handler.

Put Option Explicit atop every module to force declarations and catch typo bugs at compile time.
Declare specific types (Long, Currency, Worksheet) instead of untyped Variants for clarity and speed.
Avoid Select/Activate; reference objects directly through Set object variables.
For bulk work, read a Range into a Variant array, process in memory, and write back in one assignment.
Use structured On Error GoTo handlers with a single cleanup exit that restores application state.
Reserve On Error Resume Next for narrow, deliberate checks and re-enable trapping immediately.
Toggle ScreenUpdating, Calculation, and EnableEvents off during long runs and restore them in cleanup.