ScreenUpdating

[Aphyx]

Hi folks,

I have a concern regarding Application.ScreenUpdating. Everything works as expected when using one single macro. The moment additional macros are triggered from my original macro, the ScreenUpdating seems to always switch to True no matter if I have declared False at the start of EVERY macro. Anyone know how to ensure no updating happens even if macro triggers additional macros to run?

Thanks in advance.

[romperstomper]

Do your other macros set it back to True at the end?

[Aphyx]

They do BUT the running of the second macro seems to start with updating = true. I could and am likly mistaken.

Lets assume for a moment that it is the setting back to true at the end of the secondary macro that is causing the issue, should I apply an updating check at point(s) when the macro returns to the originally called code?

I'm gonna have a look at that.

Thanks for the nudge

[Aphyx]

That worked. TYVM. I hava a Worksheet Calculate event that I have also set updaing to false then True at the end. I just needed to comment out the setting back to true.

NEW QUESTION - If I do not set updating back to True how can that negatively affect the spreadsheet? When I break the code to debug, updaing unlike EnableEvents seems to have no effect on the remaining functionality of the woorkbook if I do not set it back to True. Bottom line... Can updating be set to false and be left like that without ever setting it back to True?

[Aphyx]

Found the answer I was looking for. Sharing the link in case it helps others...

http://www.atlaspm.com/toms-tutorial...rue-heres-why/

HAPPY CODING EXCEL-ERATORS!!!

[romperstomper]

Couple of things:
1. When stepping through the code, the setting of Screenupdating is irrelevant - you will still see the code working.
2. It is not good practice to blindly set screenupdating back to true at the end of every sub. What you should do is store the current Screenupdating setting at the start of your code and reset that at the end of the routine if required - eg. if it was off at the start of the code, store that and do not turn it back on at the end.

[Aphyx]

1. When stepping through the code, the setting of Screenupdating is irrelevant - you will still see the code working.

Of course.

2. It is not good practice to blindly set screenupdating back to true at the end of every sub. What you should do is store the current Screenupdating setting at the start of your code and reset that at the end of the routine if required - eg. if it was off at the start of the code, store that and do not turn it back on at the end.

I fully agree and will begin to take heed, however, What is screen updating set to in Excel initially? Are there factors that determine Excel's default ScreenUpdating state that do not include changing it in VBA? My assumption is that screen is set to True by default. Therefore if I write VBA code, I'd need to change from the default of True to False if I didn't want screen flicker. This make your point mute if all is true.

It makes good sense to check state when moving from from sub to sub to avoid unnecessary resetting and to ensure the desired effect but even then does it really matter? How much memory and processor does it take to check state and set if needed as opposed to just setting blindly? (just playing devil's advocate)

I guess I'm still unsure of the negative side effects of setting it to false and leaving it as false.

[romperstomper]

What is screen updating set to in Excel initially?

It is set to True until it is changed in VBA.

I'd need to change from the default of True to False if I didn't want screen flicker. This make your point mute if all is true.

Yes you need to change to False to avoid flicker but that does not make my point moot (not mute). My point is about not setting it back to True at the end of each sub - you should set it back to whatever it was when your sub started.

It makes good sense to check state when moving from from sub to sub to avoid unnecessary resetting and to ensure the desired effect but even then does it really matter? How much memory and processor does it take to check state and set if needed as opposed to just setting blindly? (just playing devil's advocate)

This is the point I was making so not sure what the preamble was about? If you don't think the processor time is important, why turn it off at all? As soon as you set Screenupdating to True you tell Excel that it has to redraw the screen. If you haven't actually finished processing, this is inefficient. Worse, if you call a sub that turns screenupdating back on, and then resume processing in your main sub, all of that processing will be visible unless you turn screenupdating back off again. Much more efficient to have your called subs not simply turn it back on in the first place.

I guess I'm still unsure of the negative side effects of setting it to false and leaving it as false.

Most of the time, screenupdating will be set back to True when your sub exits, but if an error occurs, it may not be reset and then you won't be able to do anything in Excel until you set it back to true. It's all in Tom's article that you linked to.

[Aphyx]

I applaud your recommendation, romperstomper, as sound and true to coding best practice standards. I'm just not seeing the negative effects of NOT setting screen updating back to True or by setting it to False and leaving it as such. I'm like a curious cat. I wanna see it to fully understand it. I appreciate your help and I hope this discussion benefits others.

[romperstomper]

Because on occasion you may find yourself staring at an unchanging screen and wondering what is happening. If you're OK with that, then feel free. I don't often come across people who like to ignore best practice so it makes a refreshing change.

[Aphyx]

I've change everything now to something like this...

Please Login or Register  to view this content.

That should comply with your recommendations.
Thanks again.