Stopping a Procedure

Do you know how to stop a Visual Basic procedure? If you are thinking of pressing the Esc key, you are correct. If you run a procedure and then suddenly press Esc, Visual Basic will halt execution of your program and display the message shown in Figure 13-1. However, in addition to the mighty and very reliable, in most circumstances, Escape key, VBA offers other methods of stopping your procedure and entering into a so-called break mode:

■ Pressing Ctrl+Break

■ Setting one or more breakpoints

■ Inserting the Stop statement

■ Adding a watch expression

A break occurs when execution of your VBA procedure is temporarily suspended. Visual Basic remembers the values of all variables and the statement from which the execution of the procedure should resume when the user decides to continue by clicking the Run Sub/UserForm on the toolbar (or the Run menu option with the same name) or by clicking the Continue button in the dialog box (Figure 13-1).

Tip 13-1: Preventing User Intervention

You can prevent the user from halting your procedure by including the following statement in the procedure code:

Application.EnableCancelKey = xlDisabled

When the user presses Esc or Ctrl+Break while the procedure is running, nothing happens. The Application object's EnableCancelKey property disables these keys.

The error dialog box shown in Figure 13-1 informs you that the procedure was halted. The following buttons are available:

Cods execution has been interrupted

Figure 13-1:

This message appears when you press Esc or Ctrl+Break while your VBA procedure is running.


Click this button to resume code execution. This button will be grayed out if an error was encountered.

Click this button if you do not want to troubleshoot the procedure at this time. VBA will stop code execution.


Click this button to enter break mode. The Code window will appear, and VBA will highlight the line at which the procedure execution was suspended. You can examine, debug, rest, or step through the code.


Click this button to view the online help that explains the cause of this error message.

0 0

Post a comment