Is there an equivalent to Thread.Sleep() in VBA

Question

Is there an equivalent to Thread.Sleep() in Access VBA?

Stamey

This is an Access FAQ, and you'll find mvps.org/Access to be an important source for answers to FAQs. – Photocopier

Answer 1

Declare Sub Sleep Lib "kernel32" Alias "Sleep" _
(ByVal dwMilliseconds As Long)

Use the following syntax to call the Sleep function:

Sub Sleep()
Sleep 1000 'Implements a 1 second delay
End Sub 

Acerbate

Ahh just found that my self. Thanks! – Stamey

I should note that in Excel 2007, I'm able to call Sleep directly without the wrapper VBA sub. – Witchy

The Declare statement should be in a module to avoid "Declare statements are not allowed as Public members of object" error. – Jessie

Answer 2

Another way without using kernel32:

Dim started As Single: started = Timer

Do: DoEvents: Loop Until Timer - started >= 1

Waterage

Answer 3

All of the rest of the methods to make Excel wait result in Excel becoming completely unresponsive. The solution to make Excel wait while ensuring a responsive UI is to call this wait Sub with the number of seconds to wait.

    Sub Wait(seconds As Integer)
      Dim now As Long
      now = Timer()
      Do
          DoEvents
      Loop While (Timer < now + seconds)
    End Sub

Foretopmast

The issue with using this method is that DoEvents ends up using almost all of the CPU time. – Heroism

Answer 4

A couple of amendments are required to get the code to work. The code below is the corrected version.

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

Sub SleepVBA() 
Sleep 1000 'Implements a 1 second delay 
End Sub 

Corie

Answer 5

I use this in Excel and it works great:

Application.Wait DateAdd("s", 1, Now())

DateAdd() is a function that set a time, relative to Now() (in this case - you can use other values as your argument), "s" is the time measure (seconds in this case), and the increment is 1. So here, the function call is telling the application to wait 1 second.

See also for more detail about the use of the DateAdd function.

Roquefort

@MAW74656 Fair enough; I did say in Excel, and I have not tested in Access. You say 'as-is', do you know of a workaround? – Roquefort

Answer 6

If you use Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long), you may get this error in an object module.

If so, you can declare it as private:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Papist

Answer 7

It is possible to use the Excel Wait() procedure from Access VBA.

The first step is to ensure that the Excel library is referenced from your project.

When that's done the following code will work to wait for ten seconds :

Call Excel.Application.Wait(Time:=DateAdd("s",10,Now()))

Polished

Answer 8

Cross-Platform Solution

The accepted answer will not work in 64-bit VBA. Also, it won't work on Mac.

Thanks @Cristian Buse for pointing out Mac compatibility in your comment!

Achieving full Mac compatibility requires importing the platform-dependent library function for suspending thread execution, that is usleep on Mac and Sleep on Windows. Because usleep takes it's argument in microseconds and Sleep uses milliseconds, it is necessary to declare a custom Sub that deals with the conversion.

Importing the library functions and declaring the Sub can be done as presented in the following.

This solution emerged from a collaboration of myself and Cristian Buse on an adaption of his original solution, to avoid integer overflow and to allow Sleep times of more than &HFFFFFFFF microseconds (~71 minutes) on Mac:

#If Mac Then
    #If VBA7 Then
        Private Declare PtrSafe Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #Else
        Private Declare Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #End If
#Else
    #If VBA7 Then
        Private Declare PtrSafe Sub MSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    #Else
        Private Declare Sub MSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    #End If
#End If
'Sub providing a Sleep API consistent with Windows on Mac (argument in ms)
'Authors: Guido Witt-Dörring, https://mcmap.net/q/189128/-is-there-an-equivalent-to-thread-sleep-in-vba
'         Cristian Buse,      https://mcmap.net/q/189129/-sleep-equivalent-for-mac-in-excel-vba
Public Sub Sleep(ByVal dwMilliseconds As Long)
    #If Mac Then 'To avoid overflow issues for inputs > &HFFFFFFFF / 1000:
        Do While dwMilliseconds And &H80000000
            USleep &HFFFFFED8
            If dwMilliseconds < (&H418937 Or &H80000000) Then
                dwMilliseconds = &H7FBE76C9 + (dwMilliseconds - &H80000000)
            Else: dwMilliseconds = dwMilliseconds - &H418937: End If
        Loop
        Do While dwMilliseconds > &H418937
            USleep &HFFFFFED8: dwMilliseconds = dwMilliseconds - &H418937
        Loop
        If dwMilliseconds > &H20C49B Then
            USleep (dwMilliseconds * 500& Or &H80000000) * 2&
        Else: USleep dwMilliseconds * 1000&: End If
    #Else 'Windows
        MSleep dwMilliseconds
    #End If
End Sub

Now Sleep will be available on both, Windows and Mac and in 32- as well as 64-bit environments.

It can be called like this:

Sub ExampleSleepCall()
    Sleep 1000 'Suspends thread execution for 1 second
    'Calling application will freeze completely for that amount of time!
    'If this is undesired, look here: https://mcmap.net/q/187100/-how-to-pause-for-specific-amount-of-time-excel-vba
End Sub

Note that:

1. While Sleep does take its argument in milliseconds, its resolution is not actually 1 millisecond.
2. On Mac, the maximum usleep duration is limited by MAX_UINT = &HFFFFFFFF microseconds or about 4294.97 seconds (~71 minutes). This is why the custom Sleep sub for Mac will call usleep multiple times for input values dwMilliseconds > &H418937, to avoid integer overflow issues.
3. On Windows (and on Mac with the custom Sleep sub), the maximum Sleep duration is limited by MAX_UINT milliseconds, about 4294967.3 seconds. (~49.71 days)
4. On both Windows and Mac, calling Sleep with millisecond values greater than MAX_LONG = &H7FFFFFFF (= 2147483647) requires passing it negative VBA Long values, with the maximum Sleep duration achieved by calling it like this: Sleep -1 (=Sleep &HFFFFFFFF)

Note that this way of pausing the execution has severe drawbacks in VBA!

Most importantly, all Microsoft Office applications run VBA code in the same thread as the main user interface. This means, calling Sleep essentially freezes the entire application (It will show as "not responding" in Task-Manager!). There is no way of recovering from this state without waiting for the time to pass or force quitting the Application and restarting it. Excels Application.Wait suffers from the same issue. While the app will not show as not responding in Task Manager in this case, it will be just as unresponsive to the user.

A way to circumvent this problem is calling DoEvents in a loop, as other people have already pointed out. However, this comes with another issue. Because the application will try to execute VBA code as fast as possible, DoEvents is called at the maximum achievable rate essentially saturating the CPU completely on that single thread, leading to high, unnecessary CPU and power usage and potentially slowing down other more important tasks in the UI.

To learn about the best way to pause VBA execution, look at this answer.

Unashamed

For completeness, maybe update for MAC as well. Example here. BTW, I use the same technique :) – Beare

Answer 9

Adding

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

somehow created additional problems somewhere else in my code. I ended up using this function that I found on an other forum and tweeked a bit:

Function WaitTime(n As Double)
'Function that wait an amount of time n in seconds
TWait = Time
TWait = DateAdd("s", n, TWait)
Do Until TNow >= TWait
     TNow = Time
Loop
End Function

hope this helps :)

Suboxide

The only reason I can think of that adding the function header would cause "problems" is if you were already using the function name "Sleep" in a module or class in the current process. – Painty

Answer 10

If the code is executing in a Form, you can use the built-in Timer of the Form.

Start the timer of the Form with

TimerInterval = 1000 ' Delay in ms

Declare an event handler:

Private Sub Form_Timer()
    TimerInterval = 0 ' Stop the timer if don't want to repeat the event each second

    ' Execute your code here
End Sub

Vaas