PDA

View Full Version : A few questions about DoEvents



stranno
12-30-2014, 05:11 AM
Hi,

I have a few questions about the use of DoEvents.

What is the shelf life of the DoEvents function. After all, when applied in a loop, it will be repeated time after time. Does this mean that the DoEvents function needs to be repeated all the time? Will DoEvents slow down the performance (of a loop). What is the best place to insert Doevents? Before or after a critical code line (code that should be executed under all circumstances). Since DoEvents cannot be canceled by a counter action, may i assume that it's scope will be lost after the next line?

Since i use Excel 2010 some of my macro's cannot be interupted anymore by ctrl-breakanymore. A white screen follows (Excel doesn't respond). This behaviour bothers me enormously. Can this be countered by using DoEvents functions in critical loops? Is this a common annoyance under fellow vba programmers? This problem did not exist in Excel 2003. How come?

thanks in advance for your interest.


Best regards and a happy new year,

Stranno

Kenneth Hobs
12-30-2014, 06:44 AM
Depends on what you are doing I guess.

Press F1 with cursor in or next to commands like that to get help in the VBE.

A bit more info and the definition can be found at: http://support.microsoft.com/kb/118468/EN-US

This explains more and gives an API alternative: http://www.vbforfree.com/using-doevents-in-visual-basic-the-right-way/

stranno
12-30-2014, 01:27 PM
Thanks for the tip Kenneth. The GetInputState is indeed a good alternative for DoEvents.
But do you know why it's sometimes impossible to interrupt a running macro with ctrl-break?
In Excel 2003 ctrl-break worked fine.

Kenneth Hobs
12-30-2014, 01:31 PM
If you keep Alt+ESC held down, that usually works I hear.

Some find this to work but not always I suspect.


Application.EnableCancelKey = xlInterrupt

Paul_Hossler
12-30-2014, 04:25 PM
The online help for DoEvents has a good explanation, but DoEvents basically tells VBA to hold on and let Windows catch up

Usually (in my experience at least) if the macro in NOT in a tight loop then the operating system gets enough chances without a DoEvents

I usually only call DoEvents inside a loop using Mod

The online help example uses Mod 1000, so I do something like this.



Option Explicit


Sub Demo1()
Dim i As Long, j As Long

For i = 1 To 10000000
'something
j = i
If i Mod 1000 = 0 Then DoEvents
Next i
End Sub


Sub Demo2()
Dim rRow As Range

For Each rRow In ActiveSheet.Rows
'something
rRow.Cells(1, 1).Value = rRow.Row
If rRow.Row Mod 1000 = 0 Then DoEvents
Next i
End Sub

SamT
12-30-2014, 06:57 PM
DoEvents Function
Yields execution so that the operating system can process other events.
Syntax
DoEvents( )
Remarks
The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.
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.
DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.
Caution Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.

stranno
12-31-2014, 11:01 AM
"Please take the time to read the Forum FAQ (http://www.vbaexpress.com/forum/faq.php)
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT()-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)"

Well Paul as i said before, from the time that office 2010 runs on my computer i am not always able to interupt a running macro. I noticed that this annoying inconvenience could be solved by adding the DoEvents function is certain loops. But then this function would be repeated time after time. Therefore i wondered whether this solution would slow down the execution of these loops. But in this forum i hear no complains about this Office 2010 issue. The message "Excel doesn't respond" followed by a "white screen" doesn't seem to bother anybody. That's nice.

GTO
12-31-2014, 12:48 PM
"Please take the time to read the Forum FAQ (http://www.vbaexpress.com/forum/faq.php)
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT()-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)"

Well Paul as i said before,

Hi Stranno:hi:

Just humbly checking; you did catch that the above is a quote and not Paul asking you, right?


from the time that office 2010 runs on my computer i am not always able to interupt a running macro. I noticed that this annoying inconvenience could be solved by adding the DoEvents function is certain loops. But then this function would be repeated time after time. Therefore i wondered whether this solution would slow down the execution of these loops. But in this forum i hear no complains about this Office 2010 issue. The message "Excel doesn't respond" followed by a "white screen" doesn't seem to bother anybody. That's nice.

:rotlaugh:Too funny! Of course for those who have experienced similar, Excel's white screen of "I drank so much I am passed out" is extremely irritating.:motz2: At least for me, I've sat there (not what one would refer to as patiently per-se) until giving up and killing Excel on a number of occasions. It does seem though that when I wait long enough, it most of the time catches up (sleeps it off?) and finishes executing.

I am not sure if it is just Excel 2010, WIN7, or the combo, as I got both at once.


Mark

Paul_Hossler
12-31-2014, 01:51 PM
The message "Excel doesn't respond" followed by a "white screen" doesn't seem to bother anybody. That's nice.


You must have nicer users than I do.

stranno
12-31-2014, 07:02 PM
As humble as your avatar GTO ;-). To tell you the truth, i didn't get that last remark at all, but i' am probably too polite to ignore it. Happy to hear that you deal with the same issue ;-) I don't feel lonely anymore.

SamT
12-31-2014, 07:17 PM
I am not sure if it is just Excel 2010, WIN7, or the combo, as I got both at once. Excel 2002, too. I can't remember back as far as 97 but probably.

Infinite loops will do it every time.

Excel practical joke

Sub Selection_Change(Target as Range)
If Not Intersect(Target, Cells(Rows.Count - 42, 25)) Is Nothing then
Range("A1").Select
Do
Loop
End If
End Sub

stranno
12-31-2014, 07:30 PM
yes Paul, some of my users are realy nice, but for the most part they're as stupid as the back of a cow (Dutch saying). Oeps, what did i say? I should show some more respect. just forget it. I think i've drunk to much. Happy new year over there.

stranno
12-31-2014, 07:38 PM
I don't know SamT. I liked Excel 2003 better than excel 2007 or Excel 2010. At least with regard to vba or the execution of vba. But Maybe the newer os (8.1 in my case) is to blame for it.

snb
01-03-2015, 05:10 AM
Stranno, can you post a sample of a piece of code you are not able to stop using ctrl-Break ?
I am not able to write such a code in Excel 2010: ctrl-break always stops any VBA execution.

But I have to confess I never write any code you will have to interrupt by the ctrl-break combination.

If you accidentally cancelled ctrl-break you can restore it using:


Sub M_snb()
Application.EnableCancelKey = 1
Application.OnKey "^{BREAK}"
End Sub

stranno
01-03-2015, 01:11 PM
Not that i am aware of, but i can give it a try. Thanks Snb.

The problem is that i often don't know which part of de code causes a so called white screen (Excel doesn't respond). There is usually no error. Therefor i guess the problem arises in a loop. But i don't know which because i am not able to stop the running code other than via ctrl alt del. This problem takes place in several of my programmes, where they run fine in Excel 2003. There is another thing i noticed. Compared with Excel 2003, Excel 2010 seems slower. Don't you think?

snb
01-03-2015, 04:28 PM
Can you please answer my first question ?

stranno
01-03-2015, 05:24 PM
Like i said Snb. I can't put my finger on it. But as soon as it happens again i will post the whole workbook. What amazes me the most is that it only seems to happen to me (and gto for crying out loud, so i am in good company). But my initial question was wether more people struggle with this problem in general. You clearly do not. Maybe i should doubt myself.

snb
01-04-2015, 03:41 AM
Are your refreshing anything from external sources (internet) while you have no internet connection or while the external source isn't available ?

stranno
01-04-2015, 05:25 AM
No that's not the case. But don't you guys experience that things go slower under Excel 2010. Not always, but starting up a Excel workbook for example, which contains code that runs automatically (WorkBook_Open) doesn't seem to go as smooth as it did under Excel 2003. And believe me, i use a lot of error handling code because some my programs are being used in professional enviroments, so they must run problem-free. But again, it's hard to say where things go wrong. Meanwhile i will continue my search. Posting to this forum is a part of this investigation. I chose this title for my thread because i thought that it might be a good idea to apply some DoEvent functions on particular spots in order to be able to interupt a "jammed" macro.

Paul_Hossler
01-04-2015, 08:15 AM
in order to be able to interupt a "jammed" macro.


There's always the possibility that it's not the macro VBA that seems to lock the computer

For example, if you're calling a built in Excel procedure or doing something 'with' the VBA instead of 'in' the VBA you could possibly have problems

Did you put break points or 'Stop'-s in the macro(s) to see if you can narrow down the code that appears to hand?

If you single step trough the macro, does that show where the hold up is?

SamT
01-04-2015, 08:32 AM
When you Ctrl+Alt+Del, how much CPU and memory is Excel using, what other program is having a higher than normal CPU Usage?

Do you have any recursives in the code?

snb
01-04-2015, 10:32 AM
Did you consider saving the file as an xlsb file ?

Did you consider producing a progression file ?


open "G:\OF\progress.txt" for append as #1
Print #1, "thismodulename; line 234"
Close

stranno
01-04-2015, 03:10 PM
Thanks for all the tips guys. But what i still don't understand is the fact that all these programs worked fine under Excel 2003. So my first hunch was that it would concern a problem in Excel 2010. Now i know that this is not the case. When i know more i will let you know. Snb, what is the purpose of a progression file?

snb
01-04-2015, 03:30 PM
The purpose is to pinpoint the point at which the macro fails.
Even if you have to use ctrl-alt-del the file indicates the last point before you had to break off Excel.

jonh
01-05-2015, 02:41 AM
For me it depends how long the code has been running as to whether or not it locks up (5-10 secs or so.)
The code below just runs a long loop. doevt checks how long the code has been running and decides whether to execute doevents to prevent the lockup.
On my work laptop it runs in about 34 seconds
If I take out the doevt sub it runs in ~3. So yeah, DoEvents is SLOOOOW.
If I leave out doevt and change 100,000,000 to 1,000,000,000 excel locks up and ctrl+break doesn't work but the code does complete in about 44 secs.


Sub test()
Dim t As Date
startat = Now
t = startat
Do
doevt t, 3
x = x + 1
If x > 100000000 Then
Debug.Print "done " & CInt((Now - startat) * 100000)
Exit Sub
End If
Loop
End Sub

Sub doevt(ByRef t As Date, sec As Byte)
If CInt((Now - t) * 100000) >= sec Then
t = Now
Debug.Print Now
DoEvents
End If
End Sub

stranno
01-05-2015, 10:37 AM
Interesting stuff. I'll check it out. Snb can you explain how this progression file works. Where would you insert this code? It doesn't detect errors by itself. I assume that it collaborates with an "on error goto" statement?

SamT
01-05-2015, 01:38 PM
Put it in the code everywhere you suspect the hangup might be. I would add a TimeStamp ( & TimeSerial(Now())) so I might see where some time was spent inside a some sub or function if it didn't quite hang up.

snb
01-05-2015, 01:52 PM
E.G.


Sub M_snb()
Open "G:\OF\progress.txt" For Append As #1
Print #1, "M_snb"

M_2emacro

M_3emacro

' more code

M_4emacro

' more code
Close

MsgBox CreateObject("scripting.filesystemobject").opentextfile("G:\OF\progress.txt").readall
End Sub

Sub M_2emacro()
Print #1, "M_2eMacro"

' more coe
End Sub

Sub M_3emacro()
Print #1, "M_3eMacro"

' more code
End Sub

Sub M_4emacro()
Print #1, "M_4eMacro"

' more code
End Sub

If the error occurs in M_3emacro, the last line in the logfile will be "M_3emacro'; so you wil know in which part/macro the error occurs.

stranno
01-05-2015, 02:07 PM
Thanks a lot guys. Can't believe that this idea didn't cross my mind. I realy appreciate your helpfulness. It's a lot of extra work, but i think i will use this analysis tool more often.

GTO
01-06-2015, 04:44 AM
Like i said Snb. I can't put my finger on it. But as soon as it happens again i will post the whole workbook. What amazes me the most is that it only seems to happen to me (and gto for crying out loud, so i am in good company). But my initial question was wether more people struggle with this problem in general. You clearly do not. Maybe i should doubt myself.

I doubt that we are on our own here. To me and a bit of wild guessing, it seems to be a multi faceted issue.

At work, the first I notice is Network issues:

Most recently, I have and am experiencing that I can close a workbook just fine (on/in a network environment), but killing an instance of the application results in an awfully long hang (not permanently hung if-you-will, but an awfully long time with the instance non-responsive. Still checking into this, but yeh, I feel your pain.).

Mark

stranno
01-06-2015, 11:16 AM
That sounds familiar to me GTO. It's a whole series of small and less minor problems. And indeed in a network enviroment it's even worse. Also starting up an instance of Excel can take 30 sec or more. I guess we have to live with it :-(

But one of my questions hasn't been answered yet. What is the shelflife of the DoEvents function? When does it lose it's function?

SamT
01-06-2015, 12:00 PM
But one of my questions hasn't been answered yet. [1:] What is the shelflife of the DoEvents function? [2:] When does it lose it's function? 1: Until the Zombie apocalypse. :hide:
2: When the stars fall from the sky. :guitar:

Please rephrase the question using only terms and meanings in common usage in discussions about computer programming.

Paul_Hossler
01-06-2015, 12:28 PM
It's a whole series of small and less minor problems. And indeed in a network enviroment it's even worse. Also starting up an instance of Excel can take 30 sec or more.


1. Are you running a standalone PC and not a virtual machine?

2. To (possibly) eliminate network aspects, try running the macro after disconnecting from the network (I remove the Cat5) or turn off wireless. Just the XLSM and Excel

3. I've noticed that the IT guys have a script that will map a bunch of network and other shares to everyone. However, I have no rights to some of the shares, but Windows still tries to access them. Un-map all drives except for C: and try the macro.

4. An alternative might be to use the Sleep command

http://www.fmsinc.com/microsoftaccess/modules/examples/AvoidDoEvents.asp


I've used Sleep many times, but never as a DoEvents replacement

stranno
01-06-2015, 02:18 PM
SamT, i think your answer hits edge nor shore ;-). Look, english is not my native language. I didn't use the word "shelf life" to bother you. Here in the Netherlands it's a common understanding. It seemed to me a plausible expression in this case. Apparently not. Would i have to use the word scope here? I don't know. Didn't you realy understand what i meant.

stranno
01-06-2015, 02:34 PM
That could also be the case Paul. At my work they use speacial software called ..... manager. It blocks the possibilty to open more than x instance(es) of an application. But on the other hand, it was no obstruction for Excel 2003.

SamT
01-06-2015, 03:53 PM
Stranno, "Shelf life" is not even in the right ocean.

"Shelf Life" Defined: http://www.merriam-webster.com/dictionary/shelf%20life

Please rephrase the question using only terms and meanings in common usage in discussions about computer programming.

Can you also tell me the Dutch meaning of "Shelf life?" This can help me understand the question.

stranno
01-07-2015, 01:16 AM
Samt, consider it in metaphorical sense. Try to see it as a period of time a product (the DoEvent function) keeps it worth. Or the time before something loses its value. Something like that. Use your imagination.

snb
01-07-2015, 01:24 AM
Here in the Netherlands it's a common understanding.

It really isn't.

stranno
01-07-2015, 05:31 AM
Snb, wat zou in dit verband - en in overdrachtelijke zin - het begrip "houdbaarheid" nog meer kunnen betekenen? Shelf life wordt vertaald met houdbaarheid of duurzaamheid.
Weet jij trouwens het antwoord op die vraag?

Snb, what else would in this context - and in a metaphorical sense - the term "sustainability" mean?
But can you answer the question?

snb
01-07-2015, 05:44 AM
I suppose you mean 'life cycle' ?

DoEvents is meant to stop VBA continuing as long as an asynchronous process hasn't been terminated.

So 'DoEvents' has no life cycle, the do ... loop has.
But the relevance of your question and a possible answer escapes me.
The introduction of 'DoEvents' doesn't solve malfunctioning VBA code.

Paul_Hossler
01-07-2015, 08:35 AM
That could also be the case Paul. At my work they use special software called ..... manager. It blocks the possibility to open more than x instances of an application. But on the other hand, it was no obstruction for Excel 2003


Understand. We've used license management software to control usage also.

But what I was primarily suggesting was that it might be useful to take possible network effects off the table by trying just a standalone PC that doesn't have ANY interfaces to a network.

As I said, 'DoEvents' is designed to give Windows a chance to catch up and process any pending messages in the queue, either from other programs or hardware, such as the keyboard sending Ctrl-Break.

I'm not sure if it does ALL pendng messages or if there's a duration or counter or priority that determines.

I'm not sure that jonh's code is a valid test since I don't know if calling a function generates an interrupt to allow windows to do things

I used the Windows PerformanceTIme API and ran 3 versions using the tightest loop I could come up with



Option Explicit
'Retrieves the current value of the performance counter, which is a high resolution (<1us) time stamp
' that can be used for time-interval measurements
Private Declare Function QueryPerformanceCounter Lib "kernel32.dll" (lpPerformanceCount As Currency) As Long


'Retrieves the frequency of the performance counter. The frequency of the performance counter is fixed
' at system boot and is consistent across all processors. Therefore, the frequency need only be queried
' upon application initialization, and the result can be cached.
Private Declare Function QueryPerformanceFrequency Lib "kernel32.dll" (lpFrequency As Currency) As Long

'Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number
' with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides
'a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Private cTimerStart As Currency
Private cTimerStop As Currency
Private cTimerDuration As Currency
Private cFreq As Currency


Sub PH_test()
Dim X As Currency

'get timer frequency
QueryPerformanceFrequency cFreq

'get timer at start
QueryPerformanceCounter cTimerStart

'do the loop
For X = 1 To 100000000 ' 100,000,000

'Case 1 -- no DoEvents
'
'Case 2 -- Every time
'DoEvents

'Case 3 -- every 10,000 times, or 10,000 time total
'If X Mod 10000 = 0 Then DoEvents

Next X

'get timer at end
QueryPerformanceCounter cTimerStop

'calculate time
cTimerDuration = (cTimerStop - cTimerStart) / cFreq

Call MsgBox("Timer Duration : " & Format(cTimerDuration, "#,##0.000000") & " seconds", vbInformation + vbOKOnly, "Timing Test")

End Sub




Results were for 100 million iterations

Case 1 - No DoEvents - 0.429300 seconds
Case 2 - DoEvents every time - 820.324400 seconds
Case 3 -- DoEvents once every 10,000 iterations - 3.556200 seconds

Walk on case - change loop to 10 billion instead of 100 million with no DoEvents and it white screens and takes about 42 seconds

jonh
01-07-2015, 09:02 AM
My function contains a doevents. Doevents works from anywhere. My timings seem a bit strange though. :/

snb
01-07-2015, 09:46 AM
Sub M_snb()
For j = 1 To [1E9]

Next
End Sub

Paul_Hossler
01-07-2015, 10:54 AM
My function contains a doevents. Doevents works from anywhere. My timings seem a bit strange though. :/


Yes, but there were other things happening in your example (calling a function, writing data, etc.) so I tried to eliminate as much extra processing as I could to try to have a level playing field

Even so, the operating environment and what else Windows is doing is still a wildcard :-(