O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  



HACK
#81
Speed Up Code While Halting Screen Flicker
When you record macros from within Excel, the code it generates often produces screen flicker, which not only slows down your macro, but also makes the macro's activity look very disorganized. Fortunately, you can eliminate screen flicker while at the same time speeding up your code
[Discuss (0) | Link to this hack]

One drawback with recorded macros in Excel is that the code produced is often very inefficient. This can mean macros that should take a matter of seconds to complete often take a lot longer and look very unsightly. Also, when you write macros using the macro recorder, all keystrokes are recorded, whether they are meant to be or not. This means that if you make an error and then correct it, the keystrokes required to complete those actions also will be recorded in your macro code.

If you have played around a bit with macros or dabbled in VBA code, you might have heard of the Application.ScreenUpdating property. By setting ScreenUpdating to False at the start of a macro, you will not only stop the constant screen flicker associated with a recorded macro, but also speed up the macro's execution. The reason this method speeds up code is because Excel no longer needs to repaint the screen whenever it encounters commands such as Select, Activate, LargeScroll, SmallScroll, and many others.

To include Application.ScreenUpdating = False at the beginning of your existing macro, select Tools → Macro → Macros, select your macro, click the Edit button, and enter the following code:

'
' a Macro
' Macro recorded 1/12/2003 by OzGrid.com
'

'
Application.ScreenUpdating = False
'YOUR CODE
Application.ScreenUpdating = True
End Sub

Note how you set ScreenUpdating back to True on completion. Although Excel will set this back to True whenever focus is passed back to Excel (in other words, when your macro finishes), in most cases it pays to play it safe and include the code at the end.

In some cases, you might find that ScreenUpdating is set back to True before your recorded macro completes. This can happen with recorded macros that use the Select command frequently. If this does happen, you might need to repeat the line Application.ScreenUpdating = False in other parts of your macro.


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.