VBA DoEvents function 'magic'

**danleonida** · 11-09-2012, 06:52 PM

...for anyone having problems ending long loops. One way of doing is to use the DoEvents VBA function only I found that there is a great deal of confusion about it on the Internet. I figured it out myself and, I thought, others might benefit from my experience.

As such, I wrote a small Excel file with three identical tabs. They each contain a counter with a 'start', 'stop' and 'reset' button. They are all independently controlled while the user still has control - although limited somewhat - of the spreadsheet.

Have a look and let me know what you think: 2012.11.09...MS Excel VBA DoEvents function-demo.xls

I was even able to bring up a live VBA editor, rename the tabs of the running counters, etc!!! I'm quite impressed with the DoEvents function.

**danleonida** · 11-10-2012, 08:05 PM

protonLeah,

I noticed you deleted your own post requesting that I change the title of this thread. I changed it to "VBA DoEvents help available..." followed by a new paragraph which is still present in the opening post. The title has been reverted back to the original.

Am I to understand that you downloaded the file at the end of the link and liked it?

I also noted that the tread has been moved to "Forum > Off Topic > Tips and Tutorials". I was looking for something like it before the original post and failed. Sorry!

**danleonida** · 11-14-2012, 07:45 PM

I've added some bells/whistles to the file as well as simplifying it a bit!

I thought you might enjoy:

2012.11.14...MS Excel VBA DoEvents function-demo II.xls

**danleonida** · 11-15-2012, 03:50 PM

I've added to the file a circle area calculator to see how my favorite VBA function interacts with the spreadsheet. Here's the file:

2012.11.15...MS Excel VBA DoEvents function-demo III.xls

It has a small message box explaining what to do to highlight what I believe is an 'undocumented feature' of the DoEvents function.

I suspected the few global variables I was using were behind the misbehavior, so I eliminated and replaced them with named cells in the spreadsheet. It made no difference! The resulting file runs slower but still exhibits the 'undocumented feature'. Here's the file with no globals:

2012.11.15...MS Excel VBA DoEvents function-demo IV.xls

I think the function DoEvents is too useful not to be well studied!

**danleonida** · 11-15-2012, 05:48 PM

Found a small bug in my macros and fixed it. If one were to click 'start' several times, the macro/spreadsheet would crash!

Here's the fixed up version:

2012.11.15...MS Excel VBA DoEvents function-demo V.xls

It seems that if one needs to calculate the area of a circle, Excel will be glad to oblige but IT WILL NOT RETURN CONTROL to the DoEvents statement for some reason! Go figure!

**Cheeky Charlie** · 02-12-2013, 06:10 AM

I'm really interested in this, but I cna't download the files from the website. Can you make (just the last?) version available through this website?

**danleonida** · 02-12-2013, 01:32 PM

I tried the link in the post right above yours and had no problems.

You have to click the smaller of the two 'download' buttons.

I have attached the file to this post, but I don't know id it will work since I've never used the feature before. I also included a simple and useful (to yours truly) timer that also puts the DoEvents function to good use.

If you still have problems, drop me a line and I'll send them by email.

Good luck! and...

Be good and do disturb!
Not disturbed enough yet!

danleonida-at-yahoo-dot-com
P.S.