[vb6] Pause() function for Wait/Sleep

The best way to pause code execution is to provide a mechanism that gives the parent application such as Excel or Word opportunities to handle events as well as other operating system tasks. The routine below provides both and allows a pause of as little as a hundredth of a second.

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.