About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store



Past Tip of the Day


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

By Bill Jelen on 13-Jul-2002 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.