Recipe 7.4 Multitask Your VBA Code

7.4.1 Problem

If your VBA code includes a loop that runs for more than just a second or two, Access seems to come to a halt. You can't move the windows on the screen, and mouse-clicks inside Access are disregarded until your code has finished running. Why is this happening? Is there something you can do to relinquish some control?

7.4.2 Solution

You may have noticed that it's possible to tie up Access with a simple bit of VBA code. Though 32-bit Windows is multithreaded, this helps only if the applications running under it are also multithreaded. It appears that the executing VBA code ties up Access's processing, so the multithreaded nature of Windows doesn't help. If your code contains loops that run for a while, you should make a conscious effort to give Windows time to catch up and do its own work. VBA includes the DoEvents statement, which effectively yields time to Windows so that Access can perform whatever other tasks it must. Effective use of DoEvents can make the difference between an Access application that hogs Access's ability to multitask and one that allows Access to run smoothly while your VBA code is executing.

To see the problem in action, load and run the form frmDoEvents (in 07-04.MDB). Figure 7-5 shows the form in use. The form includes three command buttons, each of which causes the label with the caption "Watch Me Grow!" to change its width from 500 to 3500 twips (in Figure 7-5, you can see only a portion of the label), in a loop like this:

    Me.lblGrow1.Width = 500
    For intI = 0 To 3000
        Me.lblGrow1.Width = Me.lblGrow1.Width + 1
        ' Without this call to Repaint, you'll
        ' never see any changes on the screen.
        Me.Repaint
    Next intI

Figure 7-5. The sample DoEvents Test form, frmDoEvents, in action

To test the effects of DoEvents, try these steps:

Press the "Run Code Without DoEvents" button. The code attached to this button will change the width of the label inside a loop without yielding time to Access. While the code is running, try to click on another button on the form or to move or size the active window. You will find that any of these tasks is impossible while the label is expanding. Once the label has finished growing, Access will display any actions you attempted to make during the process.
Try the same loop with DoEvents inserted. Click the second button, labeled "Run Code With DoEvents 1". This time, as the code executes, you will be able to move or size the active window. In addition, you can click on any of the form's buttons while the code is running. The next step tests this capability.
While the label is growing, click on the "Run Code With DoEvents 1" button many times in quick succession. Every time you click the button, Access starts up another instance of the Click event procedure, and each instance continues to make the label grow. This is called recursion, in which multiple calls are made into the same routine, each starting before the last instance has completed. Each time you call the Click event, you use a bit of Access's stack space (a memory area set aside for each procedure's entry information and local variables). It's possible that, with many invocations, you will use up that memory. Using versions of Access later than Access 95, we've never made this happen. Using Access 2, it was easy to do. The next step offers a solution to this recursion problem.
Click the third button, labeled "Run Code with DoEvents 2". While the label is expanding, try clicking on the button again. You'll see that this time your clicks won't have any effect. The code attached to this button checks to see if it's already running and, if so, exits the code. This method solves the problem of recursive calls to DoEvents.

7.4.3 Discussion

The code attached to the first button does its work without any concern for Windows or other running applications. When you press it, it executes this code:

Private Sub cmdNoDoevents_Click( )
    Dim intI As Integer

    Me.lblGrow1.Width = 500
    For intI = 0 To 3000
        Me.lblGrow1.Width = Me.lblGrow1.Width + 1
        ' Without this call to Repaint, you'll
        ' never see any changes on the screen.
        Me.Repaint
    Next intI
End Sub

Because the code never gives Windows time to "catch up," you must include the call to Me.Repaint to make sure the form repaints itself after each change. To see how this works, comment out that line and press the first button again. You'll see that the screen won't repaint until the entire operation is done.

The code attached to the second button does the same work, but it calls DoEvents within the loop. With that statement added, you no longer need the call to Me.Repaint, because DoEvents allows Windows to take care of the pending repaints. It also allows you to use the mouse and other applications while this loop is running. The code attached to the second button looks like this:

Private Sub TestDoEvents( )
    Dim intI As Integer

    Me.lblGrow1.Width = 500
    For intI = 0 To 3000
        Me.lblGrow1.Width = Me.lblGrow1.Width + 1
        DoEvents
    Next intI
End Sub

Private Sub cmdDoEvents1_Click( )
    TestDoEvents
End Sub

The problem with this code, as mentioned in Step 2, is that nothing keeps you from initiating it again while it's running; if you press the same button while the code is in the middle of the loop, Access will start up the same procedure again. Every time Access starts running a VBA routine, it stores information about the routine and its local variables in a reserved area of memory, called its "stack". The size of this area is fixed and limits the number of procedures that can run concurrently. If you press that button over and over again in quick succession, it's possible that you'll overrun Access's stack space.

It's doubtful that you'll ever be able to reproduce this problem with this tiny example. Though the stack space was limited to 40 KB in Access 2, it was increased to a much larger size in Access 95 and later versions. You'd have to press that button very fast for a very long time to fill up that much stack space. However, in more complex situations, if you were passing a large amount of data to a procedure in its parameter list, this could still be a problem.

The third button on the form demonstrates the solution to this problem. It ensures that its code isn't already running before it starts the loop. If it's already in progress, the code just exits. The code attached to the third button looks like this:

Private Sub cmdDoEvents2_Click( )
    Static blnInHere As Boolean

    If blnInHere Then Exit Sub
    blnInHere = True
    TestDoEvents
    blnInHere = False
End Sub

It uses a static variable, blnInHere, to keep track of whether the routine is already running. If blnInHere is currently True, it exits. If not, it sets the variable to True and then calls cmdDoEvents1_Click (the previous code fragment). Once cmdDoEvents1_Click returns, cmdDoEvents2_Click sets blnInHere back to False, clearing the way for another invocation.

DoEvents is one of the most misunderstood elements of VBA. No matter what programmers would like DoEvents to do, under versions of Access later than Access 95 it does nothing more than yield time to Access so it can process all the messages in its message queue. It has no effect on the Access database engine itself and can't be used to slow things down or help timing issues (other than those involving Windows messages). When used in VBA code, DoEvents releases control to the operating environment, which doesn't return control until it has processed the events in its queue and handled all the keys in the SendKeys queue. Access will ignore DoEvents in:

A user-defined procedure that calculates a field in a query, form, or report
A user-defined procedure that creates a list to fill a combo or list box

As you can see from the second button on the sample form, recursively calling DoEvents can lead to trouble. You should take steps, as in the example of the third button, to make sure that this won't occur in your applications.

[ Team LiB ]