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.
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
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”.
Example Sleep Code Module
Option Explicit
Private Const MAX_TENTHS As Long = 21474836
Private Const MAX_SECONDS As Long = 2147483
Private Const MAX_MINUTES As Long = 35791
Private Const MAX_HOURS As Long = 596
Private Const MAX_DAYS As Long = 24
Public Enum SleepUnits
slMilliseconds = 0
slTenths = 1
slSeconds = 2
slMinutes = 3
slHours = 4
slDays = 5
End Enum
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Sub PutMeToSleep(TimeValue As Long, Optional TimeUnits As SleepUnits = slMilliseconds)
Dim myTime As Long
If TimeValue <= 0 Then
Exit Sub
End If
Select Case TimeUnits
Case SleepUnits.slTenths
myTime = TenthsToMilliseconds(TimeValue)
Case SleepUnits.slSeconds
myTime = SecondsToMilliseconds(TimeValue)
Case SleepUnits.slMinutes
myTime = MinutesToMilliseconds(TimeValue)
Case SleepUnits.slHours
myTime = HoursToMilliseconds(TimeValue)
Case SleepUnits.slDays
myTime = DaysToMilliseconds(TimeValue)
Case Else
myTime = TimeValue
End Select
If myTime > 0 Then
Sleep myTime
End If
End Sub
Private Function TenthsToMilliseconds(inValue As Long) As Long
' 100 milliseconds per tenth of one second
If inValue > MAX_TENTHS Then
TenthsToMilliseconds = -1
Else
TenthsToMilliseconds = inValue * 100
End If
End Function
Private Function SecondsToMilliseconds(inValue As Long) As Long
' 1000 milliseconds per second
If inValue > MAX_SECONDS Then
SecondsToMilliseconds = -1
Else
SecondsToMilliseconds = inValue * 1000
End If
End Function
Private Function MinutesToMilliseconds(inValue As Long) As Long
' 1000 milliseconds per second * 60 seconds per minute = 60000
' milliseconds per minute
If inValue > MAX_DAYS Then
MinutesToMilliseconds = -1
Else
MinutesToMilliseconds = inValue * 60000
End If
End Function
Private Function HoursToMilliseconds(inValue As Long) As Long
' 1000 milliseconds per second * 60 seconds per minute * 60 minutes
' per hour = 3600000 milliseconds per hour
If inValue > MAX_HOURS Then
HoursToMilliseconds = -1
Else
HoursToMilliseconds = inValue * 3600000
End If
End Function
Private Function DaysToMilliseconds(inValue As Long) As Long
' 1000 milliseconds per second * 60 seconds per minute * 60 minutes
' per hour * 24 hours per day = 86400000 milliseconds per day
If inValue > MAX_DAYS Then
DaysToMilliseconds = -1
Else
DaysToMilliseconds = inValue * 86400000
End If
End Function