Interrupt/Abort a VBA-Loop
In VBA on Exce开发者_如何学运维l, 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.
When Excel is busy executing your macro, it won't respond to a button.
You have three options here:
- Use
Ctrl+Break
keys (as apposed to a button) - Make your macro much faster (maybe setting
Application.ScreenUpdating
toFalse
will help) - 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 toTrue
(obviously, your inner loop should check this variable on each iteration, and exit if it'sTrue
).
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.
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
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.
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.
精彩评论