We can pause and resume macro in Excel VBA for some practical reasons. The user might want to pause the macro to change the data. Also, it gives the user time to have a better understanding of the process. Additionally, the user might run into an error or an infinite loop. In that case, they will have to pause the code. And to complete the macro, the user also needs to resume the code. We will demonstrate this process of pausing and resuming macro using different methods.
This video shows how you can pause and resume macros with MsgBox. The MsgBox pauses the program and asks whether the user wants to run the full code.
How to Launch VBA Editor in Excel
To launch the VBA Editor in Excel, follow these steps:
- Open Microsoft Excel.
- Then, click on the Developer tab in the ribbon and Click on the Visual Basic icon in the Code group.
- This will open the VBA Editor window, in which the Project Explorer will be on the left-hand side and the Code Editor in the main area. Now, select Module from the Insert.
- A new Module will be created where you can Write or Edit your VBA code in the Code Editor.
Excel VBA to Pause and Resume Macro: 5 Suitable Ways
There are many ways to pause and resume macro in Excel VBA. We can use keyboard commands like Ctrl + Pause Break, Point Break, and functions like Wait, Sleep, and the MsgBox feature. So, let’s see these methods.
1. Use of Ctrl + Pause Break Command to Pause and Resume Macro in Excel VBA
This method is mainly used when we run into an infinite loop or if there is a problem with the code. Then, we have to pause the code, fix the issue, and then resume the code again.
In this procedure, we want to create Sl. No. up to row 50000. The code will take 3 to 4 seconds to run. Within that time, we will pause the code and then resume it.
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 SubThis 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.
Now 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.
Then scroll down the Sl. No. column, and you can see all cells from B5 to B50000 are filled with proper Sl. No.
Read More: How to Remove Macros from Excel
2. Using Break Point to Pause and Resume Macro in Excel VBA
We can also 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. We will add the Break Point after the execution of Column 1.
You can use the following code for this procedure. Then, 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 SubWe want the code to write “Pass” in Column 1 and then pause. You can 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 it has paused.
Now, 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”.
Read More: How to Save Macros in Excel Permanently
3. Inserting Sleep Method to Pause and Resume Macro in Excel VBA
We can use the VBA Sleep function to pause and resume the macro for some time at a certain stage. We are going to use the method for the same output as previously shown. Furthermore, we will 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 Sleep Function is in milliseconds. So, this line will take 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.
After the sleep time, the code will execute the second loop, and Column 2 will also be filled with “Fail”.
Read More: How to Edit Macros in Excel
4. Use Wait Function to Execute Code After a Certain Period
In this procedure, we will use the VBA Wait method to pause and resume the macro in Excel VBA. Like the previous method, we will use two For Next loops to write “Pass” and “Fail” in two adjacent columns.
You can use the following code for this procedure. Copy the below code 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
In the above code, the Application.Wait (Now + TimeValue(“0:00:04”)) line calls the Wait method to pause the code for 4 seconds.
After running the code, we can see the first loop is executed and Column 1 is filled with “Pass“. Then the code will wait for 4 seconds before executing the next loop.
When the waiting period is over, the code will execute the second loop and will write “Fail” from C5 to C20, as the below image shows.
5. Using MsgBox to Pause and Resume Macro in Excel VBA
We can 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
You can 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 to ask your permission whether you want to continue or not.
Click on the Yes button.
And, 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
We can 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. So, 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.
Conclusion
So, we have shown you 5 methods of how to use Excel VBA Pause and Resume Macro. We hope you find the content of this article useful. If there are further queries or suggestions, feel free to mention them in the comment section.
