Reset ScreenUpdating for Forward Compatibility with Excel XP

July 13, 2002 - by Bill Jelen

My tip of the day is from a bit of my own experience. As you are learning to write code, you've probably learned that you can radically speed up your macro by turning off the screen updating while the macro runs. You would add this line of code to your macro:

Sub Test()
	Application.ScreenUpdating = False
	'...macro code here
	Application.ScreenUpdating = True
End Sub

In Excel 2000 and prior, it was not required to turn ScreenUpdating back to true. When the macro finished, Excel would always revert back to turning the screen updating back to true. When I was writing macros for that platform, I would often leave off the ScreenUpdating = True because I knew Excel would handle it for me, and it solved the hassle of having to worry about it this macro was called by somewhere else that did not want the screen updating turned back on or not.

Now that those macros are being used in Excel 2002 - we have a problem. Excel's 2002 policy is that the macro has to turn screen updating back on. On my machine, it is only a problem if I halt a macro during debug, but on other machines, the screen updating stays off, leaving to some very confusing situations. I like things to be predicatable, and this certainly isn't!

So, the tip of the day, whether you are writing macros in Excel 97, Excel 2000, or Excel 2002, always turn the screen updating back to true with:

Application.ScreenUpdating = True