Do Events

[michaeldh]

Mr Excel,

I understand what an Event is but would like to know what it means when you say "DoEvents" in Visual Basic. What is it actually telling the program to do and when should you use this?

Thanks.

Michael.

 

[hedrijw]

Basically DoEvents allows the Operating System to process events while a VBA program is running. Per Excel 97 Helo:

This example uses the DoEvents function to cause execution to yield to the operating system once every 1000 iterations of the loop. DoEvents returns the number of open Visual Basic forms, but only when the host application is Visual Basic.

' Create a variable to hold number of Visual Basic forms loaded
' and visible.
Dim I, OpenForms
For I = 1 To 150000 ' Start loop.
If I Mod 1000 = 0 Then ' If loop has repeated 1000 times.
OpenForms = DoEvents ' Yield to operating system.
End If
Next I ' Increment loop counter.This example uses the DoEvents function to cause execution to yield to the operating system once every 1000 iterations of the loop. DoEvents returns the number of open Visual Basic forms, but only when the host application is Visual Basic.

' Create a variable to hold number of Visual Basic forms loaded
' and visible.
Dim I, OpenForms
For I = 1 To 150000 ' Start loop.
If I Mod 1000 = 0 Then ' If loop has repeated 1000 times.
OpenForms = DoEvents ' Yield to operating system.
End If
Next I ' Increment loop counter.

 

[Calypso]

I use Doevents in VBA when I'm waiting for some condition to be met.

For example I may kick off a Perl script to run from my VBA app - but I want to stop the VBA from proceeding until Perl has finished doing her stuff.

If I was to write something like...

Do Until PerlHasFinished
Loop

...then while waiting, the VBA app hogs the system resources big time. If I put a Doevents command in the middle of my Do...Loop then according to the Excel Help...

"DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent."

With Doevents you hardly know that Excel is still running!

HTH