Turning off screen updating

When executing a macro, you can sit back and watch all the on-screen action that occurs in the macro. Although doing this can be instructive, after getting the macro working properly, it’s often annoying and can slow down the course of your macro considerably. Fortunately, you can disable the screen updating that normally occurs when you execute a macro. To turn off screen updating, use the following statement:
Application.ScreenUpdating = False
If you want the user to see what’s happening at any point during the macro, use the following statement to turn screen updating back on:
Application.ScreenUpdating = True
To demonstrate the difference in speed, execute this simple macro, which fills a range with numbers:

Sub FillRange()
Dim r as Long, c As Integer
Dim Number as Long
Number = 0
For r = 1 To 50
For c = 1 To 50
Number = Number + 1
Cells(r, c).Value = Number
Next c
Next r
End Sub

You see each value being entered into the cells. Now insert the following statement at the beginning of the procedure and execute it again:

Application.ScreenUpdating = False

The range is filled up much faster, and you don’t see the end result until the macro is finished running. When debugging code, sometimes program execution ends somewhere in the middle, without having turned Screen updating back on (and yes, this happens to me too). This sometimes causes Excel’s application window to become totally unresponsive. The way out of this frozen state is simple: Go back to the VBE and type the following statement in the Immediate window. Press the enter key to let the VBE execute this command.

Application.ScreenUpdating = True

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website