Excel VBA to Pause and Resume Macro: 5 Examples

Method 1 – Use of Ctrl + Pause Break Command to Pause and Resume Macro in Excel VBA

We created this data table that will include the Sl. No. from B5 to B50000.

This code will give the Sl. No. from B5 to B50000.

Copy the code into a new module. Then click on the Run button.

Sub Pause_Resume_Ctrl_Break()
Dim i As Long
For i = 5 To 50000
Range("B" & i).Value = i
Next i
End Sub

This code will take 3–4 seconds to execute. So, before that time, use the keyboard command Ctrl + Pause Break to pause the code.

This code uses a For Next loop to write Sl. No. from B5 to B50000.

Scroll down to Column B of your worksheet. And you will find that, at some point, the code has been paused. In our case, the code paused after executing row 5459.

If you want to resume the code, go to your module and click on the Run button again.

Scroll down the Sl. No. column, and all cells from B5 to B50000 are filled with proper Sl. No.

Method 2 – Using Break Point to Pause and Resume Macro in Excel VBA

Use Break Point in any code to execute a certain portion of that code. For this, we have taken this dataset, which has two columns. We want to fill in Pass in Column 1 and Fail in Column 2. Add the Break Point after the execution of Column 1.

For this procedure, you can use the following code. Make sure you have added the Break Point and click on the Run button.

Sub Pause_Resume_with_Break_Point()
  Dim i, j As Integer
   For i = 5 To 20
   Range("B" & i).Value = "Pass"
   Next i
   For j = 5 To 20
   Range("C" & j).Value = "Fail"
   Next j
End Sub

We want the code to write “Pass” in Column 1 and then pause. Select the Break Point by clicking your mouse pointer at the leftmost part of the module, as shown in the image below.

This code has two For Next loops. The first one writes “Pass” in Column 1, and the second one writes “Fail” in Column 2.

After running the code, you can see that Column 1 has been filled, and Column 2 is empty. So, the code has executed the first loop and then paused.

Go to the Module and remove the Break Point. Then run the code again.

And you will see the code will execute the later loop, and Column 2 will be filled with the text “Fail”.

Method 3 – Inserting Sleep Method to Pause and Resume Macro in Excel VBA

Use the VBA Sleep function to pause and resume the macro for some time at a certain stage. Use the method for the same output as previously shown. Write Pass from B5 to B20, pause the code for some time, and then write “Fail” from C5 to C20.

Copy the following code into a new module, and then click the Run button.

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
Sub Pause_Resume_with_Sleep_Statement()
   For i = 5 To 20
   Range("B" & i).Value = "Pass"
   Next i
   Sleep 3000
   For j = 5 To 20
   Range("C" & j).Value = "Fail"
   Next j
End Sub

VBA Code Breakdown

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)

This is a VBA declaration statement that allows the code to use the Microsoft API function Sleep, which is located in the kernel32 library.

Sub Pause_Resume_with_Sleep_Statement()

Initiates the sub-procedure Pause_Resume_with_Sleep_Statement

For i = 5 To 20
   Range("B" & i).Value = "Pass"
   Next i

This For Next loop will write Pass from B5 to B20.

Sleep 3000

The unit of the Sleep Function is milliseconds. So, this line will put the code in Sleep mode for 3 seconds.

   For j = 5 To 20
   Range("C" & j).Value = "Fail"
   Next j
End Sub

Another For Next loop to write Fail from C5 to C20

The code will execute the first For Next loop and fill Column 1. Then the code will go into sleep mode for 3 seconds.

The code will execute the second loop, and Column 2 will also be filled with “Fail”.

Method 4 – Use Wait Function to Execute Code After a Certain Period

Use the VBA Wait method to pause and resume the macro in Excel VBA. Use two For Next loops to write “Pass” and “Fail” in two adjacent columns like the previous method.

Use the following code: Copy the code below into a new module, and then click on the Run button.

Sub Pause_Resume_with_Wait_Function()
Dim i, j As Integer
For i = 5 To 20
Range("B" & i).Value = "Pass"
Next i
Application.Wait (Now + TimeValue("0:00:04"))
For j = 5 To 20
Range("C" & j).Value = "Fail"
Next j
End Sub

The Application.Wait (Now + TimeValue(“0:00:04”)) line calls the Wait method to pause the code for 4 seconds.

See the first loop is executed and Column 1 is filled with “Pass“. The code will wait for 4 seconds before executing the next loop.

The code will execute the second loop and will write “Fail” from C5 to C20, as the below image shows.

Method 5 – Using MsgBox to Pause and Resume Macro in Excel VBA

Use the MsgBox to pause and resume macro in Excel VBA. We will insert a MsgBox to ask the user if they want to continue. The position of MsgBox in the code is important, as the MsgBox will automatically pause the macro.

VBA Code to Pause and Resume Macro with MsgBox

Use the following code for the procedure. Copy the code into a new module, and then click on the Run button.

Sub Pause_Resume_with_MsgBox()
    Dim result As VbMsgBoxResult
    For i = 5 To 20
    Range("B" & i).Value = "Pass"
    Next i
    result = MsgBox("Do you want to continue?", vbYesNo + vbQuestion, "Continue?")
    If result = vbNo Then
        Exit Sub
    End If
    For j = 5 To 20
    Range("C" & j).Value = "Fail"
    Next j
End Sub

VBA Code Breakdown

Sub Pause_Resume_with_MsgBox()
    Dim result As VbMsgBoxResult

Initiates the sub-procedure Pause_Resume_with_MsgBox and declares the result as VbMsgBoxResult.

For i = 5 To 20
    Range("B" & i).Value = "Pass"
    Next i

This For Next loop writes Pass from B5 to B20.

  result = MsgBox("Do you want to continue?", vbYesNo + vbQuestion, "Continue?")

This line initiates a MsgBox that has two buttons Yes and No.

 If result = vbNo Then
        Exit Sub
    End If
If you select No, the code will exit here.
    For j = 5 To 20
    Range("C" & j).Value = "Fail"
    Next j
End Sub

If you select Yes, the code will continue to the next loop and write Fail from C5 to C20.

After running the code, Column 1 will be filled with “Pass,” and a MsgBox will appear asking for your permission to continue.

Click the Yes button.

The code will move to the next loop, and the word “Fail” will fill in Column 2, as shown in the image below.

Excel VBA to Stop Execution

Use the Stop statement to stop an iteration of VBA code. This statement stops the code from progressing further.

We will show you this stop statement in a For loop.

In the following code, if we don’t use the Stop statement, the code will give us 10 MsgBoxes, each showing numbers from 1 to 10.

But we have included the Stop statement just after the MsgBox.The code will show only the first MsgBox and stop.

Open a new worksheet and run the following code.

Sub Stop_execution()
Dim i As Long
For i = 1 To 10
 MsgBox i
 Stop
Next i
End Sub

You can see a MsgBox has appeared with the number 1. The code cannot be executed further, so it will stop here.

Frequently Asked Questions

1. What Pauses Execution of VBA Code?

We can pause the VBA code for a specified period using two functions. Those functions are “Wait” and “Sleep.”

2. What is a Macro Pause?

The Macro Pause is used to stop the execution of a macro, suspending all Microsoft Excel activities until a certain time has elapsed.

3. How Do You Pause an Infinite Loop in VBA?

Option 1: Hold the Esc key down for more than a few seconds.

Option 2: Press CTRL + BREAK.

Option 3: CTRL + ALT + DEL to end process & have Auto recover when you re-open.

Things to Remember

While using Sleep and Wait Function, make sure the time is specified. Otherwise, the program will wait or sleep indefinitely.
Overuse of Sleep or Wait Functions can lead to slow and inefficient programs.
The Ctrl + Pause Break shortcut will only work when the code is running. And before pausing, the program will finish its current task.
You have to be careful when you set the Break Point. Otherwise, the program will give you the wrong output.

Download Practice Workbook

You can download and practice this workbook.

Pause and Resume Macro.xlsm