In this blog post we discuss the DoEvents method in VBA.
Arrays and Collections are mainly resident in memory but don’t drain on CPU power after they have been set. Loops however are resident in the CPU and occupy it as much as required, even at the expense of other loops and operations. This can lead to problems for other processes that fight for limited CPU resources and if the operating system does not implicitly implement multitasking, loops can cause a system to appear to hang until they finish.
This is also a problem within your own application. You may have created a progress bar of some sort but that bar never updates; your loop is so resource intensive it doesn’t allow your application to do anything until it finishes. You can however willingly relinquish the CPU by inserting the DoEvents command.
DoEvents pause the current loop and allow other functions that have requested CPU time to execute; this includes your progress bar. Your loop will get back control of the CPU once all other CPU bound tasks have at least performed some of their actions (e.g. they may also implement DoEvents whilst they are executing a loop which gives you loop time a little later).
We will illustrate this by using a CPU intensive loop without DoEvents CPUTask1(), and a CPU intensive loop with DoEvents CPUTask2(). Execute each task in turn and try to navigate around Access (restore or switch to Access, open a menu or move a window).
Sub CPUTask1() Dim t As Double, zzz As Single Debug.Print "CPUTask1 Start Now() = " & Now() For t = 1 To 100000000 zzz = zzz + (t / 2) If (t Mod 10000000) = 0 Then Debug.Print t Next Debug.Print "CPUTask1 End Now() = " & Now() End Sub Sub CPUTask2() Dim t As Double, zzz As Single Debug.Print "CPUTask2 Start Now() = " & Now() For t = 1 To 100000000 zzz = zzz + (t / 2) If (t Mod 10000000) = 0 Then DoEvents Debug.Print t End If Next Debug.Print "CPUTask2 End Now() = " & Now() End Sub
CPUTask1 Start Now() = 25/12/2012 14:51:03
CPUTask1 End Now() = 25/12/2012 14:51:14CPUTask2
CPUTask2 Start Now() = 25/12/2012 14:52:07
CPUTask2 End Now() = 25/12/2012 14:52:18
During CPUTask1’s execution it will not be possible to move anything and any updates to the Access application are queued until the function is complete. You may even find that other applications do not function at all or only a little whilst the function is executing
During CPUTest2’s execution the DoEvents statement is fired about every second and allows the Access application enough time to perform some functions, like repainting a window or opening a menu. You may find that other applications may act the same whilst they also queue up their window requests and wait for your function to call DoEvents. But if you are using Windows 7 or other multitasking operating systems, other programs should not be affected.
The point to note here is that loops can and do use up CPU resources and just as one has to be vigilant with releasing memory one also has to be vigilant not to monopolise said CPU resources.
To summarise: If you want to perform an operation (updating a progress bar) whilst looping through some structure (a recordset), you need to use DoEvents.