Screen updating in Excel

The property ScreenUpdating of the object Application tells if the screen is being updated or not. Normaly you have a kind of source-code like

Sub Foo()
    Application.ScreenUpdating = False
'   do something here ...
    Application.ScreenUpdating = True
End Sub

Now this in general might work. However, when we enter this sub when ScreenUpdating is False – after this sub ScreenUpdating is True! We may solve this using the following code:

Sub Foo()
    Dim SaveScreenUpdating
    SaveScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False
'   do something here ...
    Application.ScreenUpdating = SaveScreenUpdatingEnd Sub

We need 4 extra lines to control the property ScreenUpdating… We may also consider to use the following class

Class ScreenUpdating

Dim TheScreenUpdating As Boolean

Sub Enable()
    Let Application.ScreenUpdating = True
End Sub

Sub Disable()
    Let Application.ScreenUpdating = False
End Sub

Private Sub Class_Initialize()
    Let TheScreenUpdating = Application.ScreenUpdating
End Sub

Private Sub Class_Terminate()
    Let Application.ScreenUpdating = TheScreenUpdating
End Sub

and we use the Class ScreenUpdating to write a sub like

Sub Foo()
    Dim ScreenUpdating as New ScreenUpdating
    ScreenUpdating.Disable
'   do something here ...
End Sub

We only need 2 lines and we don’t need to worry to restore the original value of ScreenUpdating; the usage of the Class ScreenUpdating ensures that ScreenUpdating is restored!

We may improve the sub using a catch-the-error, like

Sub Foo()
    On Error Goto Error
    Dim ScreenUpdating as New ScreenUpdating
    ScreenUpdating.Disable
'   do something here ...
Error:
End Sub

Once this sub is terminated, the original value ScreenUpdating of is restored!

Hope you like this post!