Clever Geek Handbook

What is the Application.ScreenUpdating lifetime value in Excel 2010?

As far as I know Application.ScreenUpdating = true value persists until Application.ScreenUpdating = false is set. But what is the "life" of this value? It is during the procedure in which it is called, begins and ends, and the worksheet is open or?

Created a class to store the ScreenUpdating value that suits my needs:

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

The class handles this case ok.

The opposite is needed several times:

  • Init class
  • set ScreenUpdating = True

  • Reset to False at the end of the procedure or in case of error.

    I'm in trouble here; the class correctly sets ScreenUpdating to false, but when the class gets the "actual" ScreenUpdating value, this is always true. There are no other procedures or additions to change the value.

Prepared a test sheet to show the points above. Select ScreenUpdating from the drop-down list above the suCaller button.

  • Select False and click the button.
  • The value before setting False is True, as expected.
  • The test data is populated from another procedure showing the new ScreenUpdating value (False).
  • After filling the data, the value is reset to True.
  • Press the button again and the Actual value will be True as expected .
  • Do this many times and the values ​​should be fine.

Now select "True" and press the button.

  • The value before setting True is True as expected (this is the value above)
  • Test data is populated again showing ScreenUpdating new value (True)
  • After filling the data, the value is reset to False.
  • Click the button again and the Actual value will be True, not False .

The class simply negates the new value to determine the restore value, so the result confuses me for life or how the ScreenUpdating value should be set.

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

Class Module: ApplicationScreenUpdate

Test module: test

TIA Testing , Oscar.

+3


source to share


2 answers


This is behavior by design as far as I know (but I don't have a source for quotes for this).

While an experienced developer expects the customization to remain, an inexperienced one will probably not understand what they did and think excel was broken if they tried to manually put data into a sheet after their macro finished.



Something you could try is to disable app events as sometimes they can reset the value, also you can try writing the value after you set the update to false, but I suspect this value will show up after the macro did.

0


source


Found that the lifetime for the ScreenUpdating or DisplayAlerts parameter is the top procedure in which the value was changed. At the end of the procedure; even if the value was set to false, the value for these ALLWAYS properties is reset to TRUE.

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

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



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

After running each method, see the values ​​in the worksheet.

There is no way to confirm which application properties change the value of others. But, as James wrote, some change their meaning "by design"

0


source






More articles:


All Articles