The VBA Wait method is used to pause the execution of a macro, suspending all Microsoft Excel activities until a certain time is elapsed.

Syntax

Application.Wait(time)

The function requires one mandatory parameter input of time, which is the time you want the macro to resume, in Excel date format.

There are three common approaches to define the time parameter for the Application.Wait method. All the three methods are useful

Approach 1: Now + TimeValue

Application.Wait Now + TimeValue("0:00:05) 'Wait for 1 second

Approach 2 : DateAdd

Application.Wait DateAdd("s", 5, Now) 'Wait for 1 second

Approach 3 : Wait until a certain time

Application.Wait "14:00:00" 'Wait until 2:30PM today

Approach 1 is more intuitive. But approach 2 may look cleaner when you simply want to wait for a few seconds. Approach 3 is for specifying the exact time today.

Below are more examples of how time can be specified for the Application.Wait method:

Examples	Effect
Application.Wait Now + TimeValue("0:00:10")	Wait for 10 seconds
Application.Wait Now + TimeValue("0:01:00")	Wait for 1 minute
Application.Wait Now + TimeValue("1:00:00")	Wait for 1 hour
Application.Wait Now + TimeValue("0:01:30")	Wait for 1 min 30 seconds
Application.Wait DateAdd("s", 10, Now)	Wait for 10 seconds
Application.Wait DateAdd("n", 1, Now)	Wait for 1 minute
Application.Wait DateAdd("h", 1, Now)	Wait for 1 hour
Application.Wait DateAdd("s", 90, Now)	Wait for 90 seconds, i.e. 1 min 30 seconds
Application.Wait DateAdd("h", 2, DateSerial(Year(Now), Month(Now), Day(Now) + 1))	Wait until tomorrow 2 AM
Application.Wait "09:15:00"	Wait until 9:15AM
Application.Wait "15:30:00"	Wait until 3:30PM today

Minimum Wait interval

The minimum time interval the Application.Wait method allows your macro to pause is 1 second.

Sensitivity to the Esc Key

After executing the Application.Wait(time) statement, you can press the Esc key on the keyboard to abort the pausing process early. Your macro will continue to run from the next line of code immediately after the Wait statement.

You can test with the macro “WaitEarlyExit” below. The pause lasts for 40 seconds tentatively. But you can press Esc key to abort out of it. The macro will put the actual amount of time you paused into cell A1.

Sub WaitEarlyExit()
Dim start_time
start_time = Now()	'start time of procedure
Application.Wait DateAdd("s", 40, Now)
'place the total wait time into cell A1
Range("A1").Value = "Actual wait time = " & _
    Format(Now() - start_time, "hh:mm:ss") 
End Sub

Background processes

Although Application.Wait pauses all Microsoft Excel activities, some background processes may not be suspended. For example, this might include the print process, recalculation of worksheets, etc.

Example 1: Countdown Timer with Application.Wait

We can make our own countdown timer with Application.Wait. The macro “timer” allows you to define the start time for the timer (1 minute in the example). The macro will use cell A1 as the “display” of the countdown.

Sub timer()
Dim start_time
Dim result
'specify the start time for the countdown timer
start_time = Now() + TimeValue("00:01:00") '1 minute
With Range("A1")
    Do
        Application.Wait DateAdd("s", 1, Now)
        result = Format(start_time - Now(), "hh:mm:ss")
        If result = 0 Then
            .Value = "00:00:00"
            End
        Else
            .Value = result
        End If
    Loop
End With
End Sub

Example 2: Auto kick-off of night process with Application.Wait

You can use Application.Wait to design a process to kick-off a procedure (a macro) at night (e.g. 10:30pm). Below, the macro called “myNightJob” is the actual macro you want to run at 10:30pm tonight. But before you leave your office, you run the macro “kickoff”.

Sub kickoff()
    Application.Wait "22:30:00"
    myNightJob
End Sub

'this is my night job
Private Sub myNightJob()
    Debug.Print "night job started at " & Now()
    'do some steps
End Sub

If you want to run the macro after midnight, e.g. at 2am, you can use the following statement, which waits until “2 hours after midnight”.

Application.Wait DateAdd("h", 2, DateSerial(Year(Now), Month(Now), Day(Now) + 1))

Example 3: Creating animation effects with Application.Wait

You can use Application.Wait to create some animation effects in your worksheet. The mechanism is to refresh some graphical effect at every time interval (e.g. every second).

The macro “AnimationEffect” place a number from 1 to 10 into range C5:E10, and then color the cells around it.

Sub AnimationEffect()
Dim cell As Range
Dim counter As Integer
Do
    counter = counter + 1
    Application.Wait DateAdd("s", 1, Now). 'Wait
    With Range("C5:E11")
        .Value = counter
        If counter = 10 Then End 'End macro when counter reached 10
    End With
    'draw cell shading
    For Each cell In Range("B4:F4,F5:F12,B12:E12,B5:B11").Cells
        cell.Interior.ColorIndex = counter + 2
    Next
Loop
End Sub

Here are two sample screens of how the range looks like during the execution of the macro:

What’s more?

After kicking off the macro with Application.Wait, you should leave Excel untouched, because (1) Excel will look frozen anyway, and (2) The Esc key can abort the wait.

Application.OnTime is an alternative which works similarly but the way it works is different. Please check out my other article on the topic of VBA OnTime.