Error handling and the Application.ScreenUpdating = False speed trick

Concept

1. What happens without error handling

A missing sheet, a closed file or text where a number is expected stops the macro with a runtime error and a Debug button. For a non-technical user, that's confusing — and settings you changed (like ScreenUpdating) may stay off.

2. The standard pattern

Sub SafeMacro()
    On Error GoTo ErrHandler

    ' ... your code ...

CleanExit:
    ' restore settings here
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

On Error GoTo ErrHandler — if any line fails, jump to the label.
Exit Sub before the handler — so normal runs don't fall into it.
Resume CleanExit — go to the cleanup section, so settings are always restored.

3. On Error Resume Next — use carefully

It skips failing lines silently. Use it only around one line you expect might fail, then turn normal handling back on:

Dim sh As Worksheet
On Error Resume Next
Set sh = ThisWorkbook.Worksheets("Report")
On Error GoTo ErrHandler

If sh Is Nothing Then
    Set sh = ThisWorkbook.Worksheets.Add
    sh.Name = "Report"
End If

Leaving Resume Next on for the whole macro hides real bugs.

4. The speed settings

Setting	Why
`Application.ScreenUpdating = False`	Excel stops redrawing the screen for every change
`Application.Calculation = xlCalculationManual`	formulas don't recalculate after every cell write
`Application.EnableEvents = False`	stops event macros (like Worksheet_Change) firing for every change
`Application.DisplayAlerts = False`	no "do you want to replace?" pop-ups (turn back on!)

5. Full template — speed + safety

Option Explicit

Sub FastAndSafe()
    Dim calcMode As XlCalculation
    calcMode = Application.Calculation

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

    ' ===== your code here =====

CleanExit:
    Application.Calculation = calcMode
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox "Something went wrong:" & vbCrLf & Err.Description, vbExclamation
    Resume CleanExit
End Sub

Save this as your starting template for every serious macro.

6. The biggest speed boost: arrays

Reading and writing cells one by one is the slowest part of VBA. Read the whole range into memory once, work there, write back once:

Dim data As Variant, i As Long
data = ws.Range("A2:C" & lastRow).Value     ' 2D array, starts at (1, 1)

For i = 1 To UBound(data, 1)
    data(i, 3) = data(i, 1) * data(i, 2)    ' column 3 = col1 × col2
Next i

ws.Range("A2:C" & lastRow).Value = data     ' one write

On 50,000 rows, this can turn minutes into a second or two.

7. Validate before you start

Many errors are predictable. Check and exit with a friendly message:

If lastRow < 2 Then
    MsgBox "No data found on the Data sheet.", vbInformation
    GoTo CleanExit
End If

Common mistakes

Turning off ScreenUpdating or Events and not restoring them when an error happens (Excel then seems broken). Using On Error Resume Next for the whole macro. Forgetting Exit Sub before the error handler.

What you'll learn