Interrupt/Abort a VBA-Loop

Question

Hi Mates, I have a question on VBA on Excel:

Is it (if yes, how) possible, to abort a long term loop? I have a Loop over several thousands of cells, which takes some minutes. How can I do a button or something like this to interrupt this loop manually?

Building a button and overlay it with a makro is no problem ;-) Only the thing the makro has to do.

Greetz, poeschlorn

Answer 1

Application.EnableCancelKey could be the thing, you are looking for.
See the example code on the linked url, on passing the control to error handler when user presses escapes.

Answer 2

When Excel is busy executing your macro, it won't respond to a button.
You have three options here:

1. Use Ctrl+Break keys (as apposed to a button)
2. Make your macro much faster (maybe setting Application.ScreenUpdating to False will help)
3. Make your macro much slower by inserting a DoEvents in the inner loop. This way, Excel will resond to buttons in the meantime. The macro this button would trigger would just set a global variable to True (obviously, your inner loop should check this variable on each iteration, and exit if it's True).

Answer 3

I asked a similar question before and got a great answer.

http://stackoverflow.com/questions/1078798/how-to-allow-users-to-quit-out-of-long-running-vba-tasks