44

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

braX
  • 9,702
  • 5
  • 16
  • 29
Johnno Nolan
  • 27,849
  • 17
  • 105
  • 158

8 Answers8

62
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
Johnno Nolan
  • 27,849
  • 17
  • 105
  • 158
Otávio Décio
  • 70,467
  • 17
  • 156
  • 220
8

Another way without using kernel32:

Dim started As Single: started = Timer

Do: DoEvents: Loop Until Timer - started >= 1
DontFretBrett
  • 1,106
  • 3
  • 17
  • 30
6

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
The Data Brewer
  • 86
  • 1
  • 2
  • The issue with using this method is that DoEvents ends up using almost all of the CPU time. – phrebh Aug 17 '16 at 13:25
6

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
Leo
  • 35,628
  • 7
  • 72
  • 96
Zorba Eisenhower
  • 69
  • 1
  • 1
2

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()))
demongolem
  • 8,796
  • 36
  • 82
  • 101
user3298002
  • 59
  • 1
2

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

enter image description here

If so, you can declare it as private:

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

Tony L.
  • 13,638
  • 8
  • 61
  • 63
2

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.

Gaffi
  • 4,227
  • 6
  • 43
  • 72
  • @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? – Gaffi Jun 01 '12 at 18:13
1

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

sebastien leblanc
  • 615
  • 1
  • 12
  • 27
  • 1
    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. – Anonymous Type Jan 23 '12 at 03:32