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)

However, this method consumes the CPU time and slows down the PC considerably. This is a significant problem when pausing for more than a couple of seconds, when users will notice the slowdown.

You can see the impact 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
'
' Visual Basic 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
• Microsoft Access Date and Time Calculation Module

Overview of Total Visual SourceBook

The source code in Total Visual SourceBook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Code from Total Visual SourceBook (Example of Date/Time Calculations)