Putting your Macro to Sleep

Sometimes it is necessary to suspend the operation of your macro for a certain length of time. There is no native function to do this in VBA so we are left to our own devices.

The Empty Loop

Some of you may have tried the empty loop trick mimic a sleep function. This involves some variation of the examples shown in the macro below. Using this method is not very precise as the time may vary greatly between executions depending on what else your computer is doing. The other problem is that it does not really put your program to sleep, as it is executing the code in the loop to suspend the operation.

Public Sub MySleepMacro()

Dim i As Long

Dim iTime As Long

'

' The max value for Long data type is 2147483647.

' Since the loops will add one to the time value

' at the completion of the loop our max iTime

' can only be one less than the Long maximum

' without raising an overflow error.

'

iTime = 2147483646

'

' this will sleep for about 30 to 60 seconds

'

For i = 1 To iTime

Next

'

' this will sleep for about 3 to 5 minutes

'

i = 0

Do While i <= iTime

i = i + 1

Loop

End Sub

The Windows API

The Windows operating system provides some API (Application Programming Interface) functions that you can reference in your VBA macros. One of these API functions is the Sleep subroutine. To use this sleep function you must declare it using the syntax below.

Once declared you can use it in your macro. Notice that the time units for the Sleep routine are in milliseconds. That means your time values of seconds, minutes, etc. need to be converted (1 second = 1000 milliseconds).

Public Sub MySleepMacro()

' sleep for 5 seconds

Sleep 5000

' sleep for one minute

Sleep 60000

End Sub

Using the Sleep API routine allows you to precisely specify the length of time to suspend the operation of your program, and you won’t waste all of that time calibrating your empty loop counter. It also does put your application to “sleep”, no wasted processing resources iterating through those empty loops.

You could go one step further and create a Sleep Module or Class Module that would do the time conversions for you as well. The sample code for such a module is at the end of this document. The drawback here is that you use up a few milliseconds executing the extra code before your macro actually goes to sleep, but you don’t usually need to be that precise. The example macro using this code would then look something like this:

Public Sub MySleepMacro()

‘ sleep for 5 seconds

PutMeToSleep 5, slSeconds

‘ sleep for one minute

PutMeToSleep 1, slMinutes

End Sub

To find out more about the available Windows API functions and how to use them do a Google search on “Windows API”.