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.
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 = " &amp;amp;amp; _
Format(Now() - start_time, "hh:mm:ss")
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.
'specify the start time for the countdown timer
start_time = Now() + TimeValue("00:01:00") '1 minute
Application.Wait DateAdd("s", 1, Now)
result = Format(start_time - Now(), "hh:mm:ss")
If result = 0 Then
.Value = "00:00:00"
.Value = result
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”.
'this is my night job
Private Sub myNightJob()
Debug.Print "night job started at " &amp;amp;amp; Now()
'do some steps
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”.
Dim cell As Range
Dim counter As Integer
counter = counter + 1
Application.Wait DateAdd("s", 1, Now). 'Wait
.Value = counter
If counter = 10 Then End 'End macro when counter reached 10
'draw cell shading
For Each cell In Range("B4:F4,F5:F12,B12:E12,B5:B11").Cells
cell.Interior.ColorIndex = counter + 2
Here are two sample screens of how the range looks like during the execution of the macro:
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.