Excel 2019 Bible

Chapter 47: Seeing Some VBA Examples

47

VBA Speed Tips

VBA is fast, but it’s often not fast enough. This section presents programming examples
that you can use to help speed 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 view is instructive, but after you get the macro work-
ing properly, it can be annoying and slow things considerably.

Fortunately, you can disable the normal screen updating that occurs when you execute a
macro. Insert the following statement to turn off screen updating:

Application.ScreenUpdating = False

If at any point during the macro’s execution you want the user to see the results of the
macro, use the following statement to turn screen updating back on:

Application.ScreenUpdating = True

When the macro completes, Excel automatically turns screen updating back on.

Preventing alert messages

One benefit 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 addressed, however. For example, if your
macro deletes a sheet, you see the message that is shown in the dialog box in Figure 47.3.
These types of messages mean that you can’t execute your macro unattended.

FIGURE 47.3

You can instruct Excel not to display these types of alerts while a macro is running.

To avoid these alert messages (and automatically choose the default response), insert the
following VBA statement:

Application.DisplayAlerts = False

To turn alerts back on, use this statement:

Application.DisplayAlerts = True

As with screen updating, Excel turns alerts back on when the macro is done.