How to Pause a Macro: The VBA Wait Method
The VBA Wait method is used to pause the execution of a macro, suspending all Microsoft Excel activities until a certain time is elapsed.
Contents
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.
2 thoughts on “How to Pause a Macro: The VBA Wait Method”