Excel Hacks Ebook [Electronic resources] نسخه متنی

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.

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.