Microsoft Office Tutorials and References
In Depth Information
Exporting Charts as GIF Files
VBA Speed Tips
VBA is fast, but it’s often not fast enough. Following are a few tips to maximize the
speed of your macros.
Turning off screen updating
You’ve probably noticed that when you execute a macro, you can watch everything
that occurs in the macro. Sometimes this is instructive, but after you get the macro
working properly, it can be annoying and slow things considerably. Fortunately, there’s
a way to disable the normal screen updating that occurs when you execute a macro.
Insert the following statement to turn screen updating off:
Application.ScreenUpdating = False
If, at any point during the macro, you want the user to see the results of the macro,
use the following statement to turn screen updating back on:
Application.ScreenUpdating = True
Preventing alert messages
One of the benefits of using a macro is that you can perform a series of actions
automatically. You can start a macro and then get a cup of coffee while Excel does
its thing. Some operations cause Excel to display messages that must be attended
to, however. For example, if your macro deletes a sheet, you see a warning message.
These types of messages mean that you can’t execute your macro unattended.
To avoid these alert messages, insert the following VBA statement:
Application.DisplayAlerts = False
When the procedure ends, the DisplayAlerts property is automatically reset to
True (its normal state).
Simplifying object references
As you probably have discovered, references to objects can get very lengthy —
especially if your code refers to an object that’s not on the active sheet or in the
active workbook. For example, a fully qualified reference to a Series object may
look like this:
If your macro uses this range frequently, you may want to create an object variable by
using the Set command. For example, to assign this Series object to an object
variable named ChtSer , use the following statement:
Set ChtSer = Workbooks(“MyBook”).Worksheets(“Sheet1”). _