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:

1. Pausing a script allows time for complex calculations or data operations to complete, ensuring accurate and reliable results.
2. 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.
3. A pause in VBA gives you the time to review information or make decisions before proceeding.
4. Delaying scripts between batch operations allows for the efficient processing of large datasets or performing actions on multiple objects while controlling resource consumption
5. 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.
6. 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.
7. 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 A2:A7 to 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:

1. Create a dataset as shown in the above image.

2. Hit Alt + F11 to bring up the Excel VBA Editor.
3. There, click Insert and choose Module to add a blank one into the VBA Editor backstage.
4. 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

5. Hit Save on the VBA Editor toolbar and close the dialog box.

6. Now, press Alt + F8 together to open the Macro window and select the CopyAndMultiplyDataWithPause macro.
7. 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

The 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:

1. Follow steps 1 through 7 as explained in the earlier section.
2. 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)

3. Also, replace Application.Wait Now + TimeValue("00:00:10") with Sleep (10000).

4. Run the VBA script.

5. 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.

1. Open Excel VBA Editor (Alt + F11)and create a new Module by clicking the Insert menu.
2. 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

3. Click Save and close the VBA Editor.

4. Hit Alt + F8 and choose the CopyMultiplyWithPause macro in the Macro dialog box.
5. Click Run to execute the VBA code.

6. When the VBA stops for 10 seconds, I enter the multiplication factor values.
7. 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

Conclusions

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!