Pausing or Delaying VBA Using Wait, Sleep or A Loop

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

You may want some way of pausing or delaying VBA code execution and you can do this with two functions called Wait and Sleep. You can also do this using a loop, and we will look at that approach too, but first we’ll look at the functions available to Excel.

Why would you pause the code? Maybe you need to wait for another task to finish, for instance if you made a call to a Windows API/shell function. Or you may want to wait for the user to update data in the sheet, or you just want to run a macro at a set time.

Application.Wait

The .Wait method is available within Excel as a VBA function, as opposed to Sleep (see below). You can use it to specify that a macro is paused for a specific period of time.

This example makes the macro pause for approximately 10 seconds:

Application.Wait (Now + TimeValue("0:00:10"))

Or you can pause execution until a specific time e.g. this will pause a macro until 11am:

Application.Wait "11:00:00"

Wait does not accept delays of less than 1 second.

Sleep

Sleep is a Windows API function, that is, it is not part of VBA it is part of the Windows operating system. But we can access it by using a special declaration statement in our VBA.

This declaration statement serves two purposes. Firstly, it tells Excel where to find the function, secondly it allows us to use the 32bit version of the function in 32bit Excel, and the 64bit version of the function in 64bit Excel.

The Declare statement looks like this

#If VBA7 Then ' Excel 2010 or later

    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)

#Else ' Excel 2007 or earlier

    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)

#End If

Note : I've tested this code in Excel 2010 and 2013 only.

You can read more about these type of Declare statements and 32bit/64bit Office on Microsoft's MSDN site

Sleep allows us to pause a macro for X milliseconds, which is a better resolution than Wait which has a minimum delay of 1 second. So to pause a macro for 5 seconds using Sleep we write this

Sleep 5000

Loops

The big drawback of using Wait or Sleep, is that Excel locks you out until the wait/sleep period has finished. You can use CTRL+BREAK to interrupt the macro, but Excel won’t accept input from the keyboard or mouse whilst paused using Wait or Sleep.

Events are suspended and anything you have scheduled using Application.OnTime is also delayed until the pause has finished. If you didn’t know what was happening, it looks like Excel has hung whilst Sleep or Wait are in effect. Background processes like printing and recalculation do carry on though.

To overcome this drawback you can use a loop to pause VBA execution, and also allow other things to happen whilst waiting. Macro execution isn’t actually paused, it’s just not doing anything other than running some loop commands.

A loop has an added advantage as Mac users can use them, whereas Wait and Sleep are not available on a Mac.

A simple loop would look something like this

Sub WasteTime(Finish As Long)

    Dim NowTick As Long
    Dim EndTick As Long

    EndTick = GetTickCount + (Finish * 1000)
    
    Do

        NowTick = GetTickCount
        DoEvents

    Loop Until NowTick >= EndTick

End Sub

We write a sub called WasteTime, which when called from another sub or function has a value passed into it which is the number of seconds that we want it to do nothing, like so:

WasteTime(10)

The key here is the DoEvents method. DoEvents tells Excel to check if there is anything else the system wants to do like accept input from the keyboard or mouse. In this example, my macro is executing but allows the user to type into the worksheet. After approximately 10 seconds a message is then displayed.

The GetTickCount function is another Windows API function that we access by using another Declare statement. It returns the number of milliseconds since the computer started up.

The parameter, Finish that we pass into the WasteTime sub, is the number of seconds we want to delay code execution. To convert this to milliseconds, we multiply Finish by 1000.

Code Samples

Full working code examples are available to download here as a .xlsm.

Timer Resolution

In my examples I have used the word approximately when describing the delays I’m trying to achieve. I say approximately because the actual duration of the pause in the execution of the code depends on the resolution of the timer on your computer.

I could give you a very complicated description why this is so, or you could Google it, but let’s just say, if you want to delay something by 10s, then you will delay it for around 10s. It’ll only be out by a few milliseconds, which is perfectly fine for the type of things I am doing. I just wouldn’t use it to time Usain Bolt.

Spread the Word

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

Disclaimer – Please test the code yourself, it may not work in your environment. All code provided as is without any warranty