Note that the declaration of the Sleep API function has to be placed above all other routines in the module.
[Begin Code Segment]
Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
Public Sub Pause( _
ByVal Seconds As Single, _
Optional ByVal PreventVBEvents As Boolean)
' Pauses for the number of seconds specified. Seconds can be specified down to
' 1/100 of a second. The Windows Sleep routine is called during each cycle to
' give other applications time because, while DoEvents does the same, it does
' not wait and hence the VB loop code consumes more CPU cycles.
Const MaxSystemSleepInterval = 25 ' milliseconds
Const MinSystemSleepInterval = 1 ' milliseconds
Dim ResumeTime As Double
Dim Factor As Long
Dim SleepDuration As Double
Factor = CLng(24) * 60 * 60
ResumeTime = Int(Now) + (Timer + Seconds) / Factor
Do
SleepDuration = (ResumeTime - (Int(Now) + Timer / Factor)) * Factor * 1000
If SleepDuration > MaxSystemSleepInterval Then SleepDuration = MaxSystemSleepInterval
If SleepDuration < MinSystemSleepInterval Then SleepDuration = MinSystemSleepInterval
Sleep SleepDuration
If Not PreventVBEvents Then DoEvents
Loop Until Int(Now) + Timer / Factor >= ResumeTime
End Sub
[End Code Segment]
The DoEvents call is used to give the managed environment such as Excel or Word opportunities to handle events and do other work. But DoEvents only works within the managed environment and can still consume a considerable amount of resources without some other throttling mechanism. By also using the Windows Sleep API call the Windows operating system is given an opportunity to let other processes run. And, since the code is doing nothing but waiting, it is the appropriate thing to do.
Often the task involves waiting for an asynchronous task to complete such as a web query. To use the above routine while waiting for such a task to compete, two time durations are needed: the total amount of time to wait until it can be assumed that a failure has occurred in the other task, and the amount of time to wait between checks that the other task has completed. Determining how long to wait until giving up requires consideration of the longest possible time that the task could reasonably take and how long the user is willing to wait for that task to complete - wait too long and the user gets frustrated, don't wait long enough and the risk increases of falsely assuming an error occurred when it didn't. This duration is the more difficult to determine of the two. The second time, the duration between checks for completion, is easier to determine. This duration should be long enough to not consume unnecessary CPU cycles doing the check, but short enough to respond quickly when the status of the asynchronous task changes. A duration of between a quarter of a second and one second is usually reasonable. The sample code below illustrates how to wait for an asynchronous task to complete that usually finishes in less than 10 seconds.
Dim TimeoutTime As Date
TimeoutTime = Now() + TimeSerial(0, 0, 30) ' Allow 30 seconds for the asynchronous task to complete before assuming it failed
Do While Now() < TimeoutTime And Not IsTaskComplete
Pause 0.5 ' Pause half a second to allow the ashyncronous task (and the rest of the environment) to do work
Loop
The above example uses a function named IsTaskComplete to determine if the asynchronous task completed. The function can do anything such as checking if a cell changed, checking if a control's property is set, or checking if a file exists.
