Interrupt/Abort a VBA-Loop

Question

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.

Answer 1

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 2

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 3

In addition to other answers,

Lets say you want to break the code and still perform certain actions in your code, You can do so by capturing the interrupt and handling it

You can use Err.Number to capture the error number

Specifically, Err.Number = 18 refers to user breaks

Sub handleError()
On Error GoTo MyErrorHandler
 Application.EnableCancelKey = xlErrorHandler

 'Just an example to show something is running
 For i = 0 To 900000
 Range("A1") = i
 Next i

MyErrorHandler:
If Err.Number = 18 Then '18 =User interrupt
     MsgBox " You clicked Ctrl + Break "
     Exit Sub

End If
End Sub

Answer 4

• Add a call to DoEvents inside the loop.

• Add a public boolean module level flag variable

• Add a button to the UI

• In the event handler for the button set the flag true

• in the loop check the variable, if it is true abort the loop.

  I've used this in an Excel userform but not with buttons on a worksheet.