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!

Advertisements
This entry was posted in Excel, VBA. Bookmark the permalink.

2 Responses to Screen updating in Excel

  1. I use this in almost all my code. It just looks so much more professional. Great post!

    • Hi Alesandra,

      Thank you for you comment. I have been writing some time in Excel and wanted to share codes that I wrote. I time allows more will follow!

      Regards – Johannes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s