Avoid Using DoEvents to Wait in Microsoft Access, VBA, and VB6

Code for waiting a specified amount of time, or waiting until a specific time to continue code, without using DoEvents

Provided by: Luke Chung, FMS President

In our Microsoft Access, VBA, and VB6 programs, we often need to pause processing for a certain period of time, or until a specific time, and then continue processing.

One common way to do this is to use the DoEvents function in a loop while waiting, like this:

Do
  DoEvents
Loop Until (Now > datTarget)

DoEvents releases time to other processes on the computer. However, this method consumes almost all the CPU time and slows down the PC considerably because there's no pause in the processing. As soon as the DoEvents command executes, it executes again and repeats as fast as the PC can run. It literally consumes all the available CPU cycles, only giving time to other processes during the tiny period DoEvents is executing. This is a significant problem when pausing for more than a few seconds to wait for the user to perform other tasks. Users will experience the entire PC slowing down.

You can see the impact of this by looking at the Performance tab of the Windows Task Manager:

CPU Spike During DoEvents in a DO..LOOP

The proper approach is to use the Windows Sleep command which lets you wait but releases CPU cycles to the computer. To use the Windows Sleep command, you must first add the following Declare statement to the Declarations section of your module:

' Copyright (c) FMS, Inc. www.fmsinc.com
' Licensed to owners of Total Visual SourceBook
'
' Class       : modDateTime
' Description : Code for working with date and times
'
' VBA/VB6 provides many useful functions for dealing with date and time values. 
' This module contains code that enhances these functions, with code for adding and subtracting 
' dates, handling weekdays, finding the next or previous day, checking for leap years, 
' determining ages, calculating differences between dates/times, and more.
' Source : Total Visual SourceBook

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

Now you can can call the Sleep command to pause, while keeping the system resources free. For instance, this code uses the Sleep command to wait the specified number of seconds before continuing processing:

Public Sub WaitSeconds(intSeconds As Integer)
  ' Comments: Waits for a specified number of seconds
  ' Params  : intSeconds      Number of seconds to wait
  ' Source  : Total Visual SourceBook

  On Error GoTo PROC_ERR

  Dim datTime As Date

  datTime = DateAdd("s", intSeconds, Now)

  Do
   ' Yield to other programs (better than using DoEvents which eats up all the CPU cycles)
    Sleep 100
    DoEvents
  Loop Until Now >= datTime

PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitSeconds"
  Resume PROC_EXIT
End Sub

This code uses the Sleep command to wait until the specific time to continue processing:

Public Sub WaitForTime(datDate As Date)
  ' Comments: Waits until the specified date and time
  ' Params  : datDate         Date/time to wait for
  ' Source  : Total Visual SourceBook

  On Error GoTo PROC_ERR

  Do
   ' Yield to other programs (better than using DoEvents which eats up all the CPU cycles)
    Sleep 100
    DoEvents
  Loop Until Now >= datDate

PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitForTime"
  Resume PROC_EXIT
End Sub

Below is an example of calling the code:

' Wait 5 seconds, then close exit Access
MsgBox "The application will exit in 5 seconds."
WaitSeconds (5)
Quit (acQuitSaveAll)

' Exit Access at 11:59 PM
WaitForTime (Date + #11:59:00 PM#)
Quit (acQuitSaveAll)

This code and much more is available in Total Visual SourceBook.

Related Information

• Age Calculation in Microsoft Access, VBA, and VB6
• Adding (and Subtracting) Weekdays in Microsoft Access, VBA, and VB6
• Total Visual SourceBook Procedures Using Sleep
• Microsoft Access Developer Help Center