Why Reset ScreenUpdating to True? Here’s Why!
Occasionally a parroted claim will pop up in the Excel cybersphere that resetting ScreenUpdating from False back to True in a VBA procedure is pointless and unnecessary. Supporters of this claim mistakenly believe that VBA “resets itself” at the end of a macro or procedure, and/or not resetting ScreenUpdating to True has no bad effects.
Do not believe these claims — they are misinformed and dead wrong. Whenever you set ScreenUpdating to False in your code, make certain to reset it to True. Here’s why:
First, no matter what the help files may say, or what you may have heard, ScreenUpdating does not always reset itself, including where UserForms and User Defined Functions are concerned. Part of the confusion about this topic comes from versions 2000 and before, when it was not necessary to set ScreenUpdating back to True. No longer the case starting with version 2002. Code writers who did not reset ScreenUpdating to True before 2002 had to go back to their macros and do so.
Second, resetting ScreenUpdating to True is a wise practice to cover your bases. If for example the macro errors out or is interrupted before it completes, ScreenUpdating would still be off while Excel is running. Future versions of Excel will invariably support new, or stop supporting existing, VBA methods and properties, so it’s an unnecessary risk to not reset ScreenUpdating.
Third, there might be times when you are running code in sequence and you want to see what you did while you are doing it. See the following three macros. Macros Two and Three are called by Macros One and Two, but you cannot see the results of your code until the last macro is completed, unless you had set the ScreenUpdating back to the way it was in each macro. It bears mentioning in this case that the message boxes give that wild effect if you drag them while ScreenUpdating is False, very sloppy, all the more reason to set things back to normal as you go.
Sub One()
Application.ScreenUpdating = True
MsgBox "Screen Updating is on."
Range("A1:A3").ClearContents
Range("A1").Value = "One"
Application.ScreenUpdating = False
MsgBox "Screen Updating is off now !!"
Two
End Sub
Sub Two()
Range("A2").Value = "Two"
MsgBox "ScreenUpdating is " & Application.ScreenUpdating
Three
End Sub
Sub Three()
Range("A3").Value = "Three"
MsgBox "ScreenUpdating is " & Application.ScreenUpdating
End Sub
Fourth, good code writing in my opinion involves good citizenship and an awareness of perception. If you change the setting of something, then when you are through, set it back to the way it was before. Whatever I set, I reset. Whatever I define, I undefine. You never know when the next version from Microsoft will make you wish you had the good sense to keep things in order from the start. It’s sloppy coding to not re-set what you changed. Fair or not, just as people are judged by how they write and speak, programmers are judged by the quality of their code, and seen to look lazy and VBA-ignorant when not re-setting the application.
There are too many bad possible outcomes that you would not know about until they happen. Platforms; IT settings; UDFs; fatal errors occurring from unforeseen reasons (but they’d seem obvious after they happen when first unforeseen); the list goes on. Not setting ScreenUpdating to True is a bad risk. I cannot tell you how many times in my work I’ve come across a potential bad outcome were the True setting not reset.
Whether the end of a macro comes from the expected process of events or through error handling, please always ensure you have reset ScreenUpdating to True if you had previously set to False.
Hi Tom,
Thanks for this Blog. I had struggled to find any info on this Theme, until someone gave me this Link.
Alan
P.s.
You may be interested in passing on a small discussion on your Blog here:
http://www.excelforum.com/showthread.php?t=1148945&page=2&p=4464665&highlight=#post4464665
Hi Alan, thank you!
Hi Tom. Old post, but useful. Thanks. I found the post while trying to solve a problem with ScreenUpdating, and haven’t found a solution yet. I’d be interested to hear if you have any ideas.
I’m trying to be a good citizen by restoring ScreenUpdating, not necessarily to True, but to the way it was before, which might be False. I’ve seen people suggest saving the value of ScreenUpdating at the start of a macro, so you can restore it at the end. But in my experience this doesn’t work, because Excel always seems to set ScreenUpdating to True before running a macro (or maybe it does it when a macro finishes running).
The reason why this is a problem is because I’m writing an Excel VBA add-in, with application-level event handlers, like app_WorkbookOpen. So every time ANY workbook is opened, this event handler macro gets run, and ScreenUpdating gets set to True. Now suppose a user has this add-in installed, and runs a macro of their own with these statements:
Application.ScreenUpdating = False
Workbooks.Open Filename:=myFileName
DoEvents
MsgBox Application.ScreenUpdating
The DoEvents causes app_WorkbookOpen to run, and ScreenUpdating gets set to True (as reported by the MsgBox). This interferes with the macro, if it was designed to continue with ScreenUpdating False.
I haven’t found any solution to this, despite extensive googling. I haven’t even found any mention of it, which seems strange. Am I missing something?
Hi Richard – –
I wonder, without testing with an add-in that does what you posted, could not the desired screen updating setting be triggered at the Open event or the Workbook Activate event?
My other thought is, continuing on with your example of a user running their own add-in, it might well be that the actions being executed by the add-in were written by the user with the importance that screen updating is set to False for a reason. If I follow your example scenario, there is a conflict of preferences between your add-in that sets the screen updating to whatever it was before, and that user’s add-in which, for their purposes, sets the screen updating to a specified False and then back to True. You raise a good point about who trumps who in cases like this. To me, what the end use client wants, after I’ve explained all ramifications with his/her add-in code, is what I do. Now, if “my way” provides the end result of what the client wants, which presumably is why they hire me or they hire you in the first place, but meaning their screen updating alteration is superseded, then somehow an agreement needs to be reached. This point you raise is a good example of the importance, and in my opinion a lacking skill in our new Twitterized world, of ample and thorough communication with the client and/or end user, so everyone understands what the result will be, what effects it will have in other areas, and why.
Thanks a lot for your comment.
Thanks for replying, Tom. I don’t think you understood my point, but I’m sure that’s because I didn’t explain it well. Never mind. Perhaps it’s just not possible to achieve what I want (which is for my add-in not to interfere with other people’s macros).
Thanks for the follow-up, Richard.
Thanks Tom I am a religious “return it to True” coder and all the people I teach I push the “if you turn it off turn if back on again” line.
Has anything changed regarding setting Object variables to Nothing at the end of the code?
I have read that it is no longer needed to avoid memory leaks. Again I do this religiously but wondered if anything had changed?
Thanks.
Hi Neale – –
I am the same as you with setting things back to the way they were.
I’m not aware of anything in VBA that’s changed about this (or anything else for that matter), but I could have missed this development if it has become the case. I’ll ask Microsoft about it this week and let you know what they say. Meanwhile, if you can recall where you read this, I’d be interested to see it, especially if it came from Microsoft.