DoEvents howto

[storm8]

Hello,

I am really struggling with DoEvents. I have very long loops on tables that should first of all be databases and therefore i need to use DoEvents...
however I cannot really understand how exactly it behaves and my research did not fully explained what I need.

1. As I understood, DoEvents tells computer to wait untill current task is performed. Why is this not automatic or why whouldn't I put doevents after "each" line?

2. So where should they be placed? and how often? or after what kind of commands? Id post my example, but its far too long for someone to go trough it...

thanks for any suggestions or useful links!

 

[John_w]

1. As I understood, DoEvents tells computer to wait untill current task is performed.

No. From the Help:

"Yields execution so that the operating system can process other events.

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."

2. So where should they be placed? and how often? or after what kind of commands?

Whenever you want the Excel GUI to be responsive to user key presses and mouse clicks when your VBA procedure is running CPU-intensive code, for example inside loops which take a longish time.

Compare these two command button click handlers. Each runs for about 8 seconds on my computer. The first one locks up Excel while it runs and key presses are buffered. The second one calls DoEvents inside the loop which means that key presses (e.g. entering data in cells) and mouse clicks (selecting cells and sheets) are processed immediately.

Private Sub CommandButton1_Click()
    Dim i As Long, r As Single
    For i = 1 To 100000000
        r = Rnd
    Next
    MsgBox "Finished"
End Sub

Private Sub CommandButton2_Click()
    Dim i As Long, r As Single
    For i = 1 To 1000000
        r = Rnd
        DoEvents
    Next
    MsgBox "Finished"
End Sub

 

[AD_Taylor]

As a follow up to this then can DoEvents cause your code to run incorrectly?

For example if you have a long loop, you put DoEvents on and then while the code is running modify something the code needs to work/change something etc?

 

[xenou]

It's an interesting question. I don't know the answer theoretically. However, I tried the following code in a sheet module, running the code (which reads a number from A1 and copies it to B1. Attempting to change the value in A1 results in immediate cessation of code (without a warning or a message). So based on these very limited results it seems you could damage your program (i.e., stop it) by using this command carelessly.

Sub Foo()
Dim i As Long, j As Long, k As Long
For i = 1 To 100
    For j = 1 To 25000000
        k = 1
    Next j
    Cells(1, 2).Value = Cells(1, 1).Value + 0
    DoEvents
Next i
End Sub

My use of DoEvents has been quite limited: 1) to enable me to stop a long loop - so I can get a Control + Pause/Break in, or simply to be able to move the windows around so I can check my email while the code is running. 2) To get status bar messages to update rather than having Excel go into "freeze mode" - usually, this is when I am reading or writing text files with tens of thousands of lines and I put a running total of the lines processed in the status bar so the user knows that "somethings happening".

In neither case have I ever tried to do anything with Excel as such while such loops are processing.

I tend to be wary of it. It would be interesting to hear of successful (or unsuccessful) applications of it in Excel VBA apps.

 

[storm8]

I see,

so does DoEvets affect speed/validity of code processing? Sometimes I can see that errors in code occurs when the code is very long eg: part of the code is not processed which later results in error/code runs faster than calculations are done... Does doEvens solve these problems?

 

[RoryA]

You don't get code errors just because code is very long.

 

[storm8]

well, in fact I think I do.

I have a set of criteria in one row (there are about 14000 rows of cruiteria) based on which other sheet cells are filtered and then some values are applied to filtered cells.

but sometimes I get error that no cells were found. when i debug the marco or cancel it and run again the error does not occur anymore, but it runs long, the error sometimes occur later again at completely different row.

i have fixed this by count of visible rows, but still - and error that should have not occured has occured.

Although I dont get such errors often, I seen various cases of code errors at places im 99% sure the code was right but not executed correctly.

 

[RoryA]

And I suspect that 1% is actually what is happening but, as you haven't given any thing to go on, there's nothing more I can say than that.

 

[xenou]

In any case, DoEvents will not fix any errors or change the way your code executes as such.

 

[storm8]

And I suspect that 1% is actually what is happening but, as you haven't given any thing to go on, there's nothing more I can say than that.

yeah, I expected this sort of answer and most likely deserved it too. the code is too long to be placed here, but at least a scope:

                On Error Resume Next
                wsTL.ShowAllData
                On Error GoTo 0

                For cl = Cid_t_rules_portal To Cid_t_rules_visibleUrl
                    If Len(wsRC.Cells(rw + 1, cl).Value) > 0 Then
                        crit1 = wsRC.Cells(rw + 1, cl).Value
                        cname = wsRC.Cells(1, cl).Value
                        sb4 = cname & ": " & crit1
                        Application.StatusBar = sb1 & sb3 & sb4
                        
                        wsTL.ListObjects("t_TrafficLeads").Range.AutoFilter Field:=colid("t_TrafficLeads", cname), _
                        Criteria1:=crit1
                    End If
                Next cl

this filter appropriate column (cl) in wsTL column by column, the loop is inside another loop (for each row) and there are about 14.000 rows. the code itself is designed to run cl loop only for cells where there cannot be 0 rows as a result of filter. yet sometimes either wstl.showalldata does not execute properly, or the cl loop filters more results than it should. as mentioned this happens only if the macro runs unattended, if I run it again after the error or step trough the problematic line it performs as expected