Need to pause a VBA script in Excel before executing the next code?
Read this article until the end to explore the best methods to add a pause in VBA in Excel.
When working with VBA scripts in Excel, there may be situations where you need to introduce pauses or delays in your code. Pausing the execution of a script can be useful in various scenarios, such as allowing time for data processing, coordinating actions with external events, or creating a more controlled flow of operations.
While Excel VBA does not have a built-in feature for pausing scripts, you can utilize methods like
Application.Wait or the Windows API’s
Sleep function to introduce delays.
This Excel tutorial will guide you through the process of implementing pauses in your VBA scripts, enabling you to optimize the execution flow in Excel.
Reasons to Learn How to Pause VBA in Excel
Find below the situations when you’ll need to use the VBA pause tactic:
- Pausing a script allows time for complex calculations or data operations to complete, ensuring accurate and reliable results.
- Delaying scripts enables you to coordinate with external events, such as database updates or web service responses, ensuring seamless integration and real-time data handling.
- A pause in VBA gives you the time to review information or make decisions before proceeding.
- Delaying scripts between batch operations allows for the efficient processing of large datasets or performing actions on multiple objects while controlling resource consumption
- Introducing delays in VBA scripts helps create a more controlled sequence of actions, allowing scripts to execute step-by-step or at specific intervals, enhancing overall script logic and precision.
- When you’re going to execute complex and long lists of VBA codes, pausing the code for a while helps you to avoid overheating the laptop, desktop, or server.
- Delaying VBA scripts allows you to schedule specific actions or events at predetermined times or intervals, automating tasks and increasing productivity
Also read: The Complete Guide to Power Query
VBA Pause Using Application.Wait Function
Suppose, you don’t need to do anything in Excel when it executes a batch of VBA codes, pausing for a few minutes, and repeating. In this tutorial, my task is to automate the process of copying values from the cell range
C2:C7 and pause the script for 10 seconds.
Then, Excel will multiply the values in
C2:C7 by the values in
D2:D7 and put the result in the cell range
E2:E7. Between these two sets of tasks, I’ll let VBA wait 10 seconds using the Application.Wait function. Here are the steps you can try on your end:
- Create a dataset as shown in the above image.
- Hit Alt + F11 to bring up the Excel VBA Editor.
- There, click Insert and choose Module to add a blank one into the VBA Editor backstage.
- In this blank Module, copy and paste the following VBA script:
Sub CopyAndMultiplyDataWithPause() Dim sourceRange As Range Dim destinationRange As Range Dim multiplyRange As Range Dim resultRange As Range Dim i As Integer ' Set the source, destination, multiply, and result ranges Set sourceRange = Range("A2:A7") Set destinationRange = Range("C2:C7") Set multiplyRange = Range("D2:D7") Set resultRange = Range("E2:E7") ' Copy data from source range to destination range destinationRange.Value = sourceRange.Value ' Pause for 10 seconds Application.Wait Now + TimeValue("00:00:10") ' Multiply values in destination range by corresponding values in multiply range For i = 1 To destinationRange.Cells.Count resultRange.Cells(i).Value = destinationRange.Cells(i).Value * multiplyRange.Cells(i).Value Next i MsgBox "Data copied, paused, and multiplied successfully." End Sub
- Hit Save on the VBA Editor toolbar and close the dialog box.
- Now, press Alt + F8 together to open the Macro window and select the CopyAndMultiplyDataWithPause macro.
- Click the Run button to execute the code.
Now, Excel will perform the copy task immediately and wait for 10 seconds before performing the multiplication task and putting the results under column E. During this 10 seconds pause, you won’t be able to access the Excel application.
In the above script, I placed the VBA pause code, that is,
Application.Wait Now + TimeValue("00:00:10") in between two batches of VBA scripts. You can also copy the code element as is and paste it into your own VBA project in Excel between two tasks. If you need multiple pauses, insert the code element as many times as you want.
Pause VBA Using the Sleep Function
Sleep (milliseconds) function of VBA also lets you you pause VBA for a specific time before executing codes downstream. Find below the code and steps to implement it:
- Follow steps 1 through 7 as explained in the earlier section.
- In Step 4, use the same code as above with the following changes:
- After copy-pasting the previous code, click the dropdown as indicated and select Declarations and paste the following declaration:
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
- Also, replace
Application.Wait Now + TimeValue("00:00:10")with
- Run the VBA script.
- You’ll see Excel copying data, pausing, and then performing the multiplication. Finally, it shows a message box as well.
Pause Using a Loop Function in VBA
The above two methods aren’t suitable if you need to modify the sheet or input data in the Excel dataset during the pause time. To keep using the Excelin between the pause time, you can use a Loop function to pause VBA in Excel. Find below the steps as well as the Loop function.
In the present data, I’ve deleted the values under the Factor column. I’ll enter the values when the Excel VBA is waiting 10 seconds before executing the next script.
- Open Excel VBA Editor (Alt + F11)and create a new Module by clicking the Insert menu.
- Copy and paste this VBA script into the blank Module:
Sub CopyMultiplyWithPause() Dim sourceRange As Range Dim destinationRange As Range Dim multiplyRange As Range Dim resultRange As Range Dim duration As Long Dim startTime As Double Dim currentTime As Double Dim i As Long ' Set the desired pause duration in seconds duration = 10 ' Set the source, destination, multiply, and result ranges Set sourceRange = Range("A2:A7") Set destinationRange = Range("C2:C7") Set multiplyRange = Range("D2:D7") Set resultRange = Range("E2:E7") ' Copy data from source range to destination range destinationRange.Value = sourceRange.Value ' Get the start time startTime = Now ' Pause the script until the specified duration has passed Do ' Get the current time currentTime = Now ' Exit the loop if the specified duration has passed If currentTime >= startTime + (duration / 24 / 60 / 60) Then Exit Do ' Allow other processes to execute during the pause DoEvents Loop ' Multiply values in destination range by corresponding values in multiply range For i = 1 To destinationRange.Cells.Count resultRange.Cells(i).Value = destinationRange.Cells(i).Value * multiplyRange.Cells(i).Value Next i ' Continue with the rest of your code or perform subsequent actions MsgBox "Data copied, paused, and multiplied successfully." End Sub
- Click Save and close the VBA Editor.
F8and choose the CopyMultiplyWithPause macro in the Macro dialog box.
- Click Run to execute the VBA code.
- When the VBA stops for 10 seconds, I enter the multiplication factor values.
- After the 10 seconds pause, the VBA script resumes and performs the final multiplication task.
If you just need to extract the Loop function used above and insert it in between two scripts of your own Excel VBA project, use the following code:
' Set the desired pause duration in seconds duration = 10 startTime = Now ' Pause the script until the specified duration has passed Do ' Get the current time currentTime = Now ' Exit the loop if the specified duration has passed If currentTime >= startTime + (duration / 24 / 60 / 60) Then Exit Do ' Allow other processes to execute during the pause DoEvents Loop
These are all the popular ways to pause or delay a VBA script on your Excel workbook.
If you don’t need to interact with Excel when the VBA code is delayed, then you can use the Application.Wait and Sleep function-based methods. These methods mainly help you distribute the PC resources efficiently when running a large VBA script.
Otherwise, if you continue with a large VBA code, your PC might hang up and you won’t be able to use other apps like the web browser or calculator.
Contrarily, if you need to enter data in the Excel worksheet when the VBA script is paused, you should try the Loop function-based method. It can pause a code from execution until you restructure or input data in Excel and then continue to finish the code. The method is more suitable when you need to automate many other tasks in Excel.
Do you know any other pause or delay methods in VBA? Let me know in the comments!