Speed: ScreenUpdating, calculation and array transfers

Turn off the right things while working, move data in blocks instead of cells, and stop using Select and Activate.

Turning off the overhead

Option Explicit

' A single helper you call from every macro, with guaranteed restore.
Public Sub FastMode(ByRef saved As Variant)
    With Application
        saved = Array(.ScreenUpdating, .DisplayStatusBar, .EnableEvents, .Calculation)
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
End Sub

Public Sub RestoreMode(ByVal saved As Variant)
    With Application
        .EnableEvents = saved(2)
        .Calculation = saved(3)
        .DisplayStatusBar = saved(1)
        .ScreenUpdating = saved(0)
    End With
    ' force one recalculation after manual mode
    If Application.Calculation = xlCalculationAutomatic Then Application.Calculate
End Sub

Public Sub Rebuild()
    Dim saved As Variant
    FastMode saved
    On Error GoTo CleanFail

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("A1").Value = ws.Name
    Next ws

    Debug.Print "done"

CleanExit:
    RestoreMode saved
    Exit Sub
CleanFail:
    Debug.Print "failed: " & Err.Description
    Resume CleanExit
End Sub

• Application.EnableEvents = False stops a change event firing for every cell you touch. If the macro crashes with events off, those handlers stay dead until the next run restores them, which is why the restore belongs in the cleanup path and not at the end of the happy path.
• Manual calculation is the single biggest win on a workbook with many formulas. Restore the previous setting rather than forcing xlCalculationAutomatic, in case the user had it set differently.
• ScreenUpdating = False helps less than people expect but costs nothing. The real cost is usually COM calls, not painting.
• Application.StatusBar is the right place for progress: it is free and the user can see it.

Move data in blocks

' SLOW: one COM call per cell. On 100,000 rows this takes minutes.
Sub Slow()
    Dim r As Long
    For r = 2 To 100000
        Sheet1.Cells(r, 4).Value = Sheet1.Cells(r, 2).Value * Sheet1.Cells(r, 3).Value
    Next r
End Sub

' FAST: two reads and one write, whatever the row count.
Sub Fast()
    Dim ws As Worksheet
    Set ws = Sheet1

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    Dim data As Variant
    data = ws.Range("B2:C" & lastRow).Value2          ' one read, 2 columns

    Dim out() As Variant
    ReDim out(1 To UBound(data, 1), 1 To 1)

    Dim i As Long
    For i = 1 To UBound(data, 1)
        out(i, 1) = data(i, 1) * data(i, 2)           ' pure in-memory work
    Next i

    ws.Range("D2").Resize(UBound(out, 1), 1).Value2 = out   ' one write
End Sub

' Value2 is faster than Value and returns the underlying number, not the
' formatted string. Use Value only when you actually need the formatted text.

' Select and Activate are the other big cost. They are almost never needed.
Sub Badly()
    Range("A1").Select
    Selection.Value = 1
End Sub

Sub Well()
    Sheet1.Range("A1").Value = 1        ' qualify the sheet: never rely on ActiveSheet
End Sub

Every property access on a Range crosses the COM boundary between your process and Excel. A block transfer makes one crossing; a per-cell loop makes one per cell, which is why the difference is measured in orders of magnitude rather than percent.

Measuring before optimising

#If VBA7 Then
    Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
    Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If

Sub MeasureIt()
    Dim t0 As Long
    t0 = GetTickCount
    Fast
    Debug.Print "Fast: " & (GetTickCount - t0) & " ms"

    t0 = GetTickCount
    ' Slow
    Debug.Print "Slow: " & (GetTickCount - t0) & " ms"
End Sub

' Timer is simpler and has a resolution of about 16 ms, which is fine for
' anything that takes more than a fraction of a second.
Sub SimpleTiming()
    Dim t As Single
    t = Timer
    Fast
    Debug.Print Format$(Timer - t, "0.000") & " s"
End Sub

' The Windows timer wraps after about 49 days of uptime. For a long-running
' macro, subtract the start from the end and handle the negative case.
Function ElapsedMs(ByVal startTicks As Long) As Long
    Dim nowTicks As Long
    nowTicks = GetTickCount
    If nowTicks < startTicks Then
        ElapsedMs = (2147483647 - startTicks) + nowTicks
    Else
        ElapsedMs = nowTicks - startTicks
    End If
End Function

FAQ

Related

Arrays, collections and dictionaries Error handling and the classic pitfalls

Last refreshed 2026-09-18.