Excel VBA Pause (Break / Stop) & Resume a Macro

This tutorial will demonstrate how pause (break) and resume a macro in Excel.

Being able to pause a macro while testing VBA code that has been written is a useful way to work out where any ‘bugs’ may be in our code.   There are a number of ways that we can break the running of the macro, and then resume it from the point where the macro was interrupted.

The Control and Break keyboard combination

If we hold down the control key, and then press the Pause/Break key when VBA code is running, the code will immediately stop with a debug message warning us that the running of the macro has been interrupted.

 

We can then press the Continue button in the dialog box that pops up to continue running the macro.  Alternatively, if we then press the Debug button in the dialog box, the macro will highlight the position where it stopped running.  By resting the mouse over any existing variables, we can then see what is stored in the variables.  This can be very useful in debugging our code.

We can then click the Run button in the ribbon (or press F5 on the keyboard) to resume running the macro.

 

Adding Break Points to the Macro

Before starting the macro, we can insert break points into the macro in order to stop the macro as specific lines of code.

We can run the code by clicking on the Run button in the Ribbon, or by pressing F5 on the keyboard.  The macro will stop at the break point.

 

Press the Run button again (the caption will now say Continue) to resume the macro or press F5.

There may be other times that the running of a VBA macro may need to be paused.  This can also be done using the Wait and Sleep methods.   These methods are used more in delaying the actual progress of the macro rather than being used to debug the actual code.

For example, this line of code will delay the macro from running until 5 more seconds have passed.

Application.Wait (Now + TimeValue("0:00:05"))