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

问题:

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

回答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 

回答2:

Another way without using kernel32:

Dim started As Single: started = Timer

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

回答3:

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 

回答4:

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

回答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.

回答6:

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()))

回答7:

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)

回答8:

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 :)