ScreenUpdating Question

[Rudi]

Hi,

What is best practice in the case of using Application.ScreenUpdating?
I use it in two different ways and have seen mixed results regarding screen flicker during code runs.

Method 1:

Code: Select all

Sub Master()
   Application.ScreenUpdating = False
   Macro1
   Macro2
   Macro3
   Application.ScreenUpdating = True
End Sub

Sub Macro1()
   ...
End Sub

Sub Macro2()
   ...
End Sub

Sub Macro3()
   ...
End Sub

Method 2

Code: Select all

Sub Master()
   Macro1
   Macro2
   Macro3
End Sub

Sub Macro1()
   Application.ScreenUpdating = False
   ...
   Application.ScreenUpdating = True
End Sub

Sub Macro2()
   Application.ScreenUpdating = False
   ...
   Application.ScreenUpdating = True
End Sub

Sub Macro3()
   Application.ScreenUpdating = False
   ...
   Application.ScreenUpdating = True
End Sub

BTW:
I recall also in pre- ribbon versions of Excel that if ScreenUpdating was switched off and a MsgBox (or GetOpenFileName dialog, for example) was displayed, that if you dragged it, it would leave a trail of unrefreshed duplicates behind it (similar to the bouncing winner msg after playing the old Solitaire game). However in the ribbon versions of Excel, this seems to have changed? With ScreenUpdating off, the duplication trails are not an issue anymore. Dragging dialogs now leaves a clean screen. Is this an improvement on Microsoft's side or just an OS improvement?

TX

[StuartR]

Best is for each Macro to check (and remember) the status of Application.ScreenUpdating and reset to the same state when it exits

Code: Select all

Sub Macro1()
   Dim blScrUpd as Boolean

   blScrUpd = Application.ScreenUpdating
   if blScrUpd then Application.ScreenUpdating = Falce
   ...
   Application.ScreenUpdating = blScrUpd
End Sub

You can use exactly the same syntax in the master Sub that calls the other three

[HansV]

I often use this technique; in fact, since Application.ScreenUpdating = False will be silently ignored if it is already False, I omit the test If blScrUpd = False Then.

The same method can be used for Application.EnableEvents (which should be turned off in event procedures if there is a risk of causing a cascade of events) and for Application.Calculation (if you want to turn off automatic calculation to speed up code execution):

Code: Select all

Sub Something()
    Dim blnScreenUpdating As Boolean
    Dim blnEnableEvents As Boolean
    Dim xlCalculationMode As xlCalculation

    On Error GoTo ErrHandler

    ' Save the current settings and turn the settings off
    blnScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False

    blnEnableEvents = Application.EnableEvents
    Application.EnableEvents = False

    xlCalculationMode = Application.Calculation
    Application.Calculation = xlCalculationManual

    ' lots of code here
    ...

ExitHandler:
    ' Always restore the original settings, even if an error occurred
    Application.Calculation = xlCalculationMode
    Application.EnableEvents = blnEnableEvents
    Application.ScreenUpdating = blnScreenUpdating
    Exit Sub

ErrHandler:
    ' Display error message
    MsgBox Err.Description, vbExclamation
    ' Perform cleanup actions
    Resume ExitHandler
End Sub

[Rudi]

TX for the examples regarding the storage of these settings in variables and restoring them to their previous state, however, can I confirm that the better way of handling ScreenUpdating across multiple called macros is to switch it on and off in each Sub instead of once in the Master macro (as Stuart stated above)?

[HansV]

The best way is to make ALL macros behave the same way:

- Store the current setting of ScreenUpdating in a local variable.
- Turn ScreenUpdating off.
- Run some code.
- Restore the original setting of ScreenUpdating.

[Rudi]

TX Stuart, Hans...

[bjsatola]

I usually use a class file for this. As far as "usefulness" it really depends on what you are doing.

I've attached the class in case anyone else has a need (just change *.txt to *.cls before importing into your VBAProject).

It should be fairly self explanatory, but maybe not since I wrote it :p
Cheers,
>>B

[HansV]

Thanks!

[Rudi]

I usually use a class file for this. As far as "usefulness" it really depends on what you are doing.

That certainly adds a bit of tech. to my question.
That's way deeper than I'll ever use, but thanks for the upload. I'm sure others can benefit from your generocity.

[rory]

It's also worth noting that turning screenupdating off and on should only be used when necessary. If you set Screenupdating to True you cause a screen redraw even if nothing has changed. I've often seen people complain about flicker when they use code like this:

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If somecondition then
 do_some_stuff
End If
Application.ScreenUpdating = True
End Sub

because what they should be using is:

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If somecondition then
Application.ScreenUpdating = False
 do_some_stuff
Application.ScreenUpdating = True
End If
End Sub

[Rudi]

Hi Rory,

TX for your insert...

I understand the concept of ScreenUpdating, and I also agree on using it wisely, however, I don't think I follow the example you posted.
How can the positional change of ScreenUpdating = True (in your example) have any different effect in the rinning of that macro?

I can understand in the code below the position of ScreenUpdating = True will make a difference, but your example I do not understand.

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If somecondition then
do_some_stuff
End If
'more code
...
...
Application.ScreenUpdating = True
End Sub

'=================== as opposed to =========================

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If somecondition then
Application.ScreenUpdating = False
do_some_stuff
Application.ScreenUpdating = True
End If
'more code
...
...
End Sub

[HansV]

In the Worksheet_Change, Worksheet_SelectionChange and Worksheet_BeforeDblClick events, you will often want to execute code only if a specific range of cells is affected. So you only need to turn ScreenUpdating off and on again if the Target of the event overlaps that range.

In more general code, where you may execute some lines conditionally and others unconditionally, it is easier to simply turn off screenupdating at the beginning and to turn it on (or to restore the original setting, as discussed above) at the end.

[Rudi]

Ah ha.... TX for that eloquent insight Hans.

Gotcha Rory...Cheers!

[rory]

If you try the code you should see a slight flicker every time you select any cell. :)