What's the lifetime of Application.ScreenUpdating value in Excel 2010?

Question

As far as I know Application.ScreenUpdating = true value is maintained until Application.ScreenUpdating = false is set. But what's the 'lifetime' of this value? Is it while the procedure where it's called starts and finish, while Worksheet is opened, or?

Made a class to keep ScreenUpdating value consistent to my needs:

• Init class
• set ScreenUpdating = False
• At the end of procedure or in case of error restore the value to True.

The class handles this case ok.

Some times need the opposite:

• Init class

• set ScreenUpdating = True

• At the end of procedure or in case of error restore the value to False.

  I'm having trouble here; the class sets properly the value ScreenUpdating = false, but when the class gets the 'actual' value of ScreenUpdating it is always true. There are no other procedures or addins that could be changing the value.

Have prepared a test sheet to show the points above. Select the value for ScreenUpdating from the dropdown above the "suCaller" button.

• Select False and press the button.
• Value before setting False is True as expected.
• Test data is filled from another procedure showing ScreenUpdating new value (False).
• After data fill, the value is reset to True.
• Press the button again and 'Actual' value is True as expected.
• Do it many times and values should be fine.

Now Select True and press the button.

• Value before setting True is True as expected (which is the value from above)
• Test data is filled again showing ScreenUpdating new value (True)
• After data fill, the value is reset to False.
• Press the button again and 'Actual' value is True not False.

The class just negate the new value to define the restore value, so the result confuses me on the lifetime or how ScreenUpdating value should be set.

Am I doing something wrong in the class or missing some basic theory?

Class Module: ApplicationScreenUpdate

Test Module: Test

Test Sheet TIA, Oscar.

Answer 1

Have found that the lifetime for ScreenUpdating or DisplayAlerts value, is the top procedure where the value was modified. At the end of the procedure; even if the value was set to false, the value for this properties ALWAYS is restored to TRUE.

On the other hand, EnableEvents or Calculation retains its value at the end of the procedure where the value was modified, been TRUE or FALSE.

Open this workbook and run the methods in the following order:

• TestFalse
• TestCurrentValues
• TestValues
• TestCurrentValues
• RestoreValues
• TestCurrentValues

After running each method, see values in sheet.

Cannot confirm wich application properties modify the value of other ones. But as James wrote, some change its own value 'by design'

Answer 2

So far as I'm aware this is behaviour is by design (but I don't have a quotable source for that).

While an experienced developer would expect the setting to remain, an inexperienced one would probably not realise what they'd done and would think that excel was broken if they tried to manually put data into the sheet after their macro completed.

Something you could try is to disable the application events as sometimes they can reset the value, also you may want to try writing a value after you set the updating to false but I suspect that value will appear after the macro is done.