excelvbaloopsinterrupt

Interrupt/Abort a VBA-Loop

In VBA on Excel, I have a loop over several thousands of cells, which takes some minutes.

Is it possible to abort a long term loop (if so, how) / can I build a button or something like that to interrupt this loop manually?

Building a button and overlaying it with a macro is not a problem, only the code itself.

Solution

  • 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).