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.

Arkwright

Possible Duplicate: #3018786 – Lanate

Does it have to loop through each cell manually? Perhaps you could rework the process to make it faster, i.e. using Excel's FillDown function. When I hear "loop over several thousands of cells", I see an opportunity to optimize. – Extensor

@PowerUser: Sry, this isn#t possible in this context, because I have to check the cells manually instead ;-) – Arkwright

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

Yttriferous

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.

Squeamish

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

Louvain

Answer 4

No, a button won't stop the running macro. It's a developer thing and control shouldn't be in user's hands by default. You can try the below combination of keys to stop the macro.

• Ctrl + Pause/Break
• Ctrl + ScrLk
• Esc + Esc (Press twice consecutively)

Sometimes, the right set of keys (Pause, Break or ScrLk) are not available on the keyboard (mostly happens with laptop users) and pressing Esc 2, 3 or multiple times doesn't halt the macro too.

I got stuck too and eventually found the solution in accessibility feature of Windows after which I tried all the researched options and above 3 of them worked for me in 3 different scenarios.

Step #01: If your keyboard does not have a specific key, please do not worry and open the 'OnScreen Keyboard' from Windows Utilities by pressing Win + U.

Step #02: Now, try any of the above option and of them will definitely work depending on your system architecture i.e. OS and Office version

You will be put into break mode using the above key combinations as the macro suspends execution immediately finishing the current task. For eg., if it is pulling the data from the web then it will halt immediately before executing any next command but after pulling the data, following which one can press F5 or F8 to continue the debugging.

Kanter

Answer 5

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

Maggiore