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 <= iTimei = i + 1
LoopEnd SubThe 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 SubUsing 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 ExplicitPrivate 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 SleepUnitsslMilliseconds = 0
slTenths = 1
slSeconds = 2
slMinutes = 3
slHours = 4
slDays = 5
End EnumPrivate 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.slTenthsmyTime = TenthsToMilliseconds(TimeValue)
Case SleepUnits.slSecondsmyTime = SecondsToMilliseconds(TimeValue)
Case SleepUnits.slMinutesmyTime = MinutesToMilliseconds(TimeValue)
Case SleepUnits.slHoursmyTime = HoursToMilliseconds(TimeValue)
Case SleepUnits.slDaysmyTime = DaysToMilliseconds(TimeValue)
Case ElsemyTime = TimeValue
End SelectIf myTime > 0 Then
Sleep myTime
End IfEnd SubPrivate Function TenthsToMilliseconds(inValue As Long) As Long
' 100 milliseconds per tenth of one secondIf inValue > MAX_TENTHS Then
TenthsToMilliseconds = -1
ElseTenthsToMilliseconds = inValue * 100
End IfEnd FunctionPrivate Function SecondsToMilliseconds(inValue As Long) As Long
' 1000 milliseconds per secondIf inValue > MAX_SECONDS Then
SecondsToMilliseconds = -1
ElseSecondsToMilliseconds = inValue * 1000
End IfEnd FunctionPrivate Function MinutesToMilliseconds(inValue As Long) As Long
' 1000 milliseconds per second * 60 seconds per minute = 60000 ' milliseconds per minuteIf inValue > MAX_DAYS Then
MinutesToMilliseconds = -1
ElseMinutesToMilliseconds = inValue * 60000
End IfEnd FunctionPrivate Function HoursToMilliseconds(inValue As Long) As Long
' 1000 milliseconds per second * 60 seconds per minute * 60 minutes ' per hour = 3600000 milliseconds per hourIf inValue > MAX_HOURS Then
HoursToMilliseconds = -1
ElseHoursToMilliseconds = inValue * 3600000
End IfEnd FunctionPrivate 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 dayIf inValue > MAX_DAYS Then
DaysToMilliseconds = -1
ElseDaysToMilliseconds = inValue * 86400000
End If
End Function