VBA Wait Time – Pause Code Execution for Few Seconds

How to Make VBA to Wait for Few Seconds?

Sometimes You have to make code execution to halt (vba wait time) for few minutes.

This happens when VBA code has dependency on external applications like IE page load, Outlook mail download or Database query retrieval etc.

So, the VBA coed has to wait for some definite time without proceeding execution to next line.

Here are few sample on how to do it.

VBA Time Wait or Sleep Time

A time wait during VBA code runtime can be achieved using these 2 techniques.

1. Application.Wait
2. Sleep

The below 2 examples show who the code execution is made to wait for 10 seconds.

Actually this option waits for code execution till the time passed as parameter. It is almost like we are scheduling the code to resume execution at specific time.

Example-1:

Sub VBA_Wait_And_Proceed()
    
    'Time Tag - before Wait time starts
    Debug.Print "Time before wait:" & Now
    
    'Wait till Timenow + 10 Seconds
    Application.Wait DateAdd("s", 10, Now)
    Debug.Print "Time After 1st Wait:" & Now
    
    'Same purpose but slightly varry in time parameter
    Application.Wait Now + TimeValue("0:00:10")
    Debug.Print "Time After 2st Wait:" & Now
    
End Sub

VBA Sleep Time:

This option is straight forward.

We use the Windows API function Sleep & pass the milliseconds the code has to wait.

Syntax: Sleep (Milliseconds)

Example-2:

'Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) - 32 bit Installation
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) '-64 Bit installation
Sub VBA_Sleep()

    'Time Tag - before Wait time starts
    Debug.Print "Time before wait:" & Now
    
    'Wait till Timenow + 10 Seconds
    Sleep (10000)
    Debug.Print "Time After 1st Wait:" & Now
End Sub

If You are using this code in a older machine that has 32 bit installation of Ms office, then use the declaration in the first line rather than using the second line.

Refer this page on how to convert 32 bit coding to 64 bit.

How to Use VBA Time Wait in a Loop?

Now, we know the syntax for Time wait.

Lets see with an example on how to implement it in a loop that waits for external dependency.

Sub Time_Wait_Loop()
    'Time Tag - before Wait time starts
    Debug.Print "Time before wait:" & Now
    
    'Using Time wait in a loop
    Do While True
        Application.Wait DateAdd("s", 1, Now)
        DoEvents
        
        '---Insert code here that check for IE Page Load, File Download, Query Upadte etc.,
        Debug.Print "Time Inside Loop:" & Now
        i = i + 1
        If i > 10 Then Exit Do
    Loop
    
    'Time Tag - After Time Wait
    Debug.Print "Time After Loop:" & Now
    
End Sub

The if condition inside the loop can be modified to include a check for external dependency.

For example, if the loop is waiting for a IE Page load, the loop can be made to exit, once the page load is completed.

External Reference: There is a detailed discussion in this board about the vba time wait available options.

Sometime the first option may not be available in few MS Office apps. For example older versions of Outlook does not have this option.

In such scenarios, the second option can be used. Since it is a Windows API code, it can be used in any MS Office Apps.