[Excel VBA] The Basics of Speeding Up Macros: Application.ScreenUpdating = False

Introduction

When executing VBA macros, especially loop processes that manipulate many cells, you may have experienced slow performance or a flickering screen.

The cause is that Excel dutifully redraws the screen for every single operation performed by VBA.

This article explains Application.ScreenUpdating, the most basic and effective technique to dramatically improve VBA macro processing speed by temporarily turning off this screen rendering.

Why Does Stopping Screen Updates Make It Faster?

When VBA executes code like Cells(1, 1).Value = "A", Excel immediately attempts to reflect (redraw) that result on the screen. In a loop that repeats this 10,000 times, 10,000 screen updates occur.

Screen rendering is a very resource-intensive process for a computer. Consequently, the majority of the processing time is spent waiting for the “screen update,” causing the overall performance of the macro to drop significantly.

Application.ScreenUpdating = False is a command that instructs Excel to “stop drawing until the macro is completely finished, and then draw the result only once at the end.” This eliminates wasted waiting time, allowing VBA to maximize its processing capability.

Completed VBA Code (Recommended Pattern with Error Handling)

If you set ScreenUpdating to False and the macro gets interrupted by an error, there is a risk that the screen will remain frozen. To prevent this, it is a golden rule to include error handling that ensures the setting is returned to True even if an error occurs.

Sub FastProcessingWithScreenUpdating()

    '--- 1. Turn off screen updating at the beginning ---
    Application.ScreenUpdating = False
    
    ' Preparation to ensure screen updating is turned back on even if an error occurs
    On Error GoTo ErrorHandler

    '--- 2. Main Processing ---
    ' (Example: Heavy process inputting values into 10,000 cells)
    Dim i As Long
    For i = 1 To 10000
        ThisWorkbook.Worksheets("Sheet1").Cells(i, 1).Value = "Test " & i
    Next i
    
'--- 3. Always turn screen updating back on at the end ---
ExitRoutine:
    Application.ScreenUpdating = True
    MsgBox "Processing complete."
    Exit Sub ' Prevent code from falling into the error handler
    
ErrorHandler:
    ' Jump here when an error occurs
    MsgBox "An error occurred: " & Err.Description, vbCritical
    ' Go to ExitRoutine to turn screen updating back on
    Resume ExitRoutine
    
End Sub

Performance Comparison

To experience the effect of ScreenUpdating, run and compare the speeds of the following two codes.

'--- Before: Screen updating remains ON (Slow) ---
Sub SlowLoop()
    Dim i As Long
    For i = 1 To 20000: Cells(i, 1).Value = i: Next
End Sub

'--- After: Screen updating OFF (Fast) ---
Sub FastLoop()
    Application.ScreenUpdating = False
    Dim i As Long
    For i = 1 To 20000: Cells(i, 1).Value = i: Next
    Application.ScreenUpdating = True
End Sub

Depending on the amount of data, you will find that the processing time changes by tens to hundreds of times.

Summary

The most important pattern to learn first for speeding up VBA macros is as follows:

1. Write Application.ScreenUpdating = False immediately after the macro starts.
2. (Recommended) Write error handling to ensure settings are reverted even if an error occurs.
3. Write Application.ScreenUpdating = True immediately before the macro ends to restore screen updating.

Most processes that involve screen changes, such as massive cell manipulations, adding/deleting sheets, or formatting, will speed up dramatically just by wrapping them with this setting. If you are struggling with VBA performance, review this setting first.