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.
Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!
"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."
Van T. Dinh, Microsoft MVP