Interrupt / Interrupt VBA-Loop

Question

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

Is it possible to interrupt a long cycle (if yes, how) / can I create a button or something like this to interrupt this cycle manually?

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

Answer 1

When Excel is busy running your macro, it will not respond to the button.
You have three options:

• Use the Ctrl+Break keys (as shown on the button)
• Make the macro faster (maybe setting Application.ScreenUpdating to False will help)
• Make your macro much slower by inserting DoEvents in the inner loop. This, Excel will play buttons in the meantime. The macro that runs this button simply sets the global variable to True (obviously, your inner loop should check this variable at each iteration and exit if it's True ).

Answer 2

Application.EnableCancelKey may be what you are looking for.
See the sample code at the associated URL when passing the control to the error handler when clicked by the user.

Answer 3

In addition to the other answers,

Suppose you want to break the code and perform certain actions in your code, you can do this by capturing an interrupt and processing it

You can use Err.Number to fix the error number

In particular, Err.Number = 18 refers to user interrupts

 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 DoEvents call inside the loop.
• Add a public logic module level flag variable
• Add button to user interface
• In the event handler for the button, set the flag true

• check the variable in the loop; if true, cancel the loop.

  I used this in a custom Excel form, but not with buttons on a worksheet.

Answer 5

No, the button will not stop the macro. By default, the developer and control should not be in the user's hands. You can try the following key combination to stop the macro.

• Ctrl + Pause / Break
• Ctrl + ScrLk
• Esc + Esc (double tap in sequence)

Sometimes the correct set of keys ( Pause , Break or ScrLk) is not available on the keyboard (most often this happens with laptop users) and pressing Esc 2, 3 or several times does not stop the macro either.

I, too, got stuck and eventually found a solution in the Windows accessibility function, after which I tried all the investigated options, and more than 3 of them worked for me in three different scenarios.

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

Step # 02 . Now try any of the above options, and they will definitely work depending on your system architecture, i.e. OS and Office versions

You will be put into break mode using the above key combinations, as the macro pauses execution, immediately ending the current task. For example, if he pulls data from the Internet, he stops immediately before executing any next command, but after pulling out the data, after which you can press F5 or F8 to continue debugging.