Help Explain DoEvents

[Evagrius Ponticus]

Hi all,

I am triying to figure out how to use the "DoEvents" function. The Excel VBA help is insufficient for me. Can anyone please explain to me what it is and when is this function best used. I 've seen it in loops mostly but don't understand what it does. Thanks,

 

[iliace]

Use it when you have multiple nested loops in your VBA, outside of one or two of the inner loops. It lets your PC do stuff other than your VBA in-between long operations, basically.

 

[Evagrius Ponticus]

Can you be more specific iliace - if you have time - maybe a simple example?

 

[iliace]

This may not be the best examples (because there are more efficient ways of doing this), but it should suffice.

Let's say I'm trying to find which two numbers in a range add up to a certain sum. My range is A1:J25000, which is 250,000 cells. So, I'll have two loops to do this. When I find the first combination of two cells that add up to myValue, I need to display their addresses in a message box. This is the code:

Public Sub WhichTwoAddUp()
  Const myValue As Double = 1234.56
  Const myRange As String = "A1:J25000"
 
  Dim rng1 As Excel.Range
  Dim rng2 As Excel.Range
  Dim rngSearch As Excel.Range
 
  Set rngSearch = ActiveWorkbook.Worksheets("Sheet1").Range(myRange)
 
  For Each rng1 In rngSearch.Cells
    For Each rng2 In rngSearch.Cells
      If rng1.Value + rng2.Value = myValue Then
        Call MsgBox(rng1.Address & " + " & rng2.Address & " = " & myValue)
        Exit Sub
      End If
    Next rng2
  Next rng1
End Sub

If you run this macro, it will take FOREVER to execute. Moreover, if you run it from VBE, you won't be able to switch over to Excel. If you have Task Manager running, you'll see that it's using up all of your CPU (or 1/2 of the CPU if you have dual-core, etc).

Here's another version of this code, with addition of DoEvents:

Public Sub WhichTwoAddUp()
  Const myValue As Double = 1234.56
  Const myRange As String = "A1:J25000"
 
  Dim rng1 As Excel.Range
  Dim rng2 As Excel.Range
  Dim rngSearch As Excel.Range
 
  Set rngSearch = ActiveWorkbook.Worksheets("Sheet1").Range(myRange)
 
  For Each rng1 In rngSearch.Cells
    For Each rng2 In rngSearch.Cells
      If rng1.Value + rng2.Value = myValue Then
        Call MsgBox(rng1.Address & " + " & rng2.Address & " = " & myValue)
        Exit Sub
      End If
    Next rng2
    DoEvents
  Next rng1
End Sub

Now, after each inner loop, VBA will process system events - such as switching over to your Excel window, etc. It will also process anything that's stacked up waiting for free CPU time.

Does that help?

 

[parry]

Hi, the purpose of DoEvents is to allow the user to interrupt the running of code when needed. For an example, draw two buttons on a sheet.

Call one Start and the other stop. In the code for the start button enter this code...

' Create a variable to hold number of Visual Basic forms loaded
' and visible.
Dim I, OpenForms
For I = 1 To 150000000   ' Start loop.
    Application.StatusBar = "Processing " & I & " of 150,000,000"
    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.

In the code for the stop button enter this code

    End 'stop any running processes

When you click the start button you will see numbers incrementing in the status bar. Now click the sop button and you will see that the code in the start button was stopped. Now remove the DoEvents line from the code and see what happens. Now see that there appears to be no reaction to clicking the stop button - this is because without doevents code keeps going until its finished so making the stop button useless unless the start button code can be interrupted.

Hope this explains.

regards,
Graham

 

[Evagrius Ponticus]

Iliace, Perry, I am very grateful for your explanations. They make sense to me now in theory, I will take apart the codes to increase my understanding. Thanks for sharing your knowledge fellas.