Macros Causes Excel To Not Respond

[WarpGammon Director]

I have had this problem in a number of contexts. I write a VBA
program, and while it is running, Excel will go into "not responding"
status. I cannot get the Excel screen to come up either. In order to
get it to "respond" I have to hit Cntl/Break. Sometimes even that
doesn't work. This is obviously only a problem with marcros that take
a while to execute.

The immediate case is a macro that takes about 20 minutes to execute.
I use the status bar to tell me what % compete it is, but if I navigate
onto a different application, I cannot get Excel to come up on the
screen.

Just for grins, I put a statement in the macro which, on each cycle of
the main loop, sets the value of a certain cell to a constant. I
thought that might make the macro give attention to the display.
Didn't work.

I searched the archives of this group and didn't find anything that
actually answered the question. I'm hoping someone can help. Thanks
in advance.

 

[Guest]

Whenever this kind of thing happens to me, it usually turns out that my VBA
code had fallen into an infinite loop. Have you debugged your code for this?

 

[Hank Youngerman]

It is definitely not a question of an infinite loop. If I allow the
program to run long enough, it will finish. (About 20 - 30 minutes for
this particular program.) And if I keep the focus on the Excel
worksheet, the status bar will change the % completed until it gets to
100%. And, I have had similar problems with other macros that were
likewise not stuck in infinite loops.

 

[Dave Peterson]

Maybe adding DoEvents to the code would help.

But I've had a couple of routines that I found just worked much better if I
didn't touch the mouse/keyboard. Go get that morning coffee???

 

[Hank Youngerman]

That worked perfectly. Thanks!!!!

Maybe adding DoEvents to the code would help.

But I've had a couple of routines that I found just worked much better if I
didn't touch the mouse/keyboard. Go get that morning coffee???