Putting your Macro to Sleep

Published on March 26, 2013

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
    ' this will sleep for about 3 to 5 minutes
    i = 0
    Do While i <= iTime
        i = i + 1
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
        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
        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
        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
        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
        DaysToMilliseconds = inValue * 86400000
    End If
End Function

Like this article? Share it!

Leave a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Privacy Policy