|[ Team LiB ]|
Recipe 7.4 Multitask Your VBA Code
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?
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
To test the effects of DoEvents, try these steps:
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:
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 ]|