none
Interrupting VBA RRS feed

  • Question

  • Hello all,

    Often I develop complex, long runing VBA macros for Excel 2010 (32-bit). During development, I sometimes forget to increment a loop counter (gasp!) or do a simliar thing that causes Excel to go into an infinite loop.  In these siuations, I need to break the running VBA code, but neither ESC nor CTRL-BREAK does anything.  The only alternative is to restart Excel 2010, but this causes all unsaved work of the macro to be lost (in addition to any unsaved code). Is there an alternative to CTRL-BREAK to interrupt VBA? ESC worked for me on Excel 2003, not not here. I'm on Windows 7 x-64.

    I am also wondering if autosave can fire while Excel is in such a state.

    Wednesday, February 23, 2011 8:29 PM

Answers

  • I thought it was only me that did this!

    I usually find that repeated Esc Clicks or holding Esc for a long time sometimes works, if the keyboard command can find a crack in the process,

    Otherwise I open the task manager Applications Tab and End the VBA task.

    Often this doesn't work either and I end up loosing the unsaved work.

    I am still trying to remember to do a manual save at frequent intervals.

    • Proposed as answer by Bessie Zhao Wednesday, March 2, 2011 8:24 AM
    • Marked as answer by Bessie Zhao Thursday, March 3, 2011 10:14 AM
    Monday, February 28, 2011 1:41 PM

All replies

  • You can check in the immediate window and see if the EnableCancelKey has been disabled.

    ?Application.EnableCancelKey
    
    

    if it is 0

    set it to xlInterupt

    and save the workbook

    Wednesday, February 23, 2011 9:52 PM
  • @William, thanks, but that not the problem.
    Sunday, February 27, 2011 10:26 PM
  • As an experiment, try inserting a:

    DoEvents

    statement in your loop.  It may allow Excel enough time to honor your BREAK.


    gsnu201011
    • Proposed as answer by MichaelPollard Wednesday, December 21, 2011 4:40 PM
    Monday, February 28, 2011 12:22 AM
    Moderator
  • I thought it was only me that did this!

    I usually find that repeated Esc Clicks or holding Esc for a long time sometimes works, if the keyboard command can find a crack in the process,

    Otherwise I open the task manager Applications Tab and End the VBA task.

    Often this doesn't work either and I end up loosing the unsaved work.

    I am still trying to remember to do a manual save at frequent intervals.

    • Proposed as answer by Bessie Zhao Wednesday, March 2, 2011 8:24 AM
    • Marked as answer by Bessie Zhao Thursday, March 3, 2011 10:14 AM
    Monday, February 28, 2011 1:41 PM
  • Hello ChrisMM,

    By researching this topic for a while, the only way I could find is to press Ctl+Break. I mainly searched these two resources about this topic.

    WD97: How to Break Out of an Infinite Macro Loop:
    http://support.microsoft.com/kb/164234.

    How to stop a never ending loop:
    http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/279754d9-8743-46bd-8feb-fd4d3b34da75/.

    Hope they give you some useful information. Have a nice day.


    Bessie Zhao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 2, 2011 8:23 AM
  • I have the same issue, seems it is now NOT possible to interrupt VBA with ctrl/break.  i have try on many computers that have office 2010/window 7 and this just seems to be the case.

    Monday, April 23, 2012 7:09 PM
  • Ctrl break no longer works either.  The processors are not receiving anything from the keyboard once the the loop starts....

    Thursday, February 21, 2013 8:59 PM
  • CTRL-BREAK does work on Windows 7 and Windows 8 and on all versions of Excel including 2013. However, if you don't have "DoEvents" in your loop it will not work, since the break signal is never processed.

    For all loops, put a "DoEvents" in your loop. Also, save your work before running new code!

    BTW, for a computer without a BREAK key, press the start button, type "On" and choose "On-Screen Keyboard". When the keyboard appears you can click CTRL then click "PAUSE" (which is the same as BREAK).

    Saturday, August 17, 2013 9:39 PM
  • ctrl + Break does the trick for me.

    http://support.microsoft.com/kb/164234

    Friday, April 25, 2014 3:28 PM
  • This did not work for me, and I just want to add my chagrin to the party here.

    I have added DoEvents and found it to help the immediate window to continue to document what is going on in my code, should I for example, to have a debug.print command somewhere within a loop. But as for letting me interrupt VBA, it did not help at all.


    Bill

    Tuesday, April 29, 2014 8:42 PM
  • Hi!

    Just open on-screen keyboard and press ScrLk (scroll lock) holding your Ctrl key at the laptop keyboard.

    Friday, June 3, 2016 2:44 PM
  • Keep pressing continuously ESC key.

    The error message will begging to raise without end.

    Then go with your mouse to the Stop icon at the VBA IDE toolbar (the one with a square) and keep clicking it quickly with left mouse button.

    At some point the KeyPress event of that button will be processed and the loop will end. 

    Being a poor self learner and not a very good coder, I have need that several times and it always work (after earing 20 or more dings of error message).


    I think that's the only way to do it in Windows 10. By the way, most new laptops don't have Break key.... 

    • Edited by Jf Ruiz Thursday, June 8, 2017 5:57 PM
    Thursday, June 8, 2017 5:28 PM
  • Maybe you could try to use form .TimerInterval and Form_Timer event triggering only a portion of your code ? Below are two buttons (Start_Command and Stop_Command) and Form_Timer event containing a counter. Unfortunately this for Access and portions of the code can't be run faster then 1 ms one by one

    Private i As Long
    
    Private Sub Start_Command_Click()
    Me.TimerInterval = 1
    End Sub
    
    Private Sub Stop_Command_Click()
    Me.TimerInterval = 0
    End Sub
    
    Private Sub Form_Timer()
    
    'Static i As Long
    
    i = i + 1
    
    Debug.Print i
    
    End Sub
    • Edited by _DK Monday, February 5, 2018 6:39 PM
    Monday, February 5, 2018 6:35 PM
  • Hi all,

    thanks a lot for this thread!

    I had the same troubles with Excel 2010: Makros with long loops could not be breaked or stopped. The solution is (as a summary of above entries):

    Insert the next line at a place where it is called about once per second:

    DoEvents

    Then the key Ctrl-Break quits the macro immediately.

    After hitting key Ctrl-Scroll a message box pops up asking: Continue / Quit / Debug? (If you keyboard does not have the scroll key, you can use the on-screen keyboard to “hit” this key.

    ESC-key is ignored.

    Application.EnableCancelKey = xlInterrupt is the standard setting and should not be changed. If you would set it to xlErrorHandler you would need to insert an error handler using ON ERROR GOTO …

    If you call DoEvents very often, it slows down the macro. It is sufficient to call it about once per second.

    DoEvents solved another problem, too: During running long macros sometimes the screen update worked fine, sometimes the screen did not change and I did not know whether the macro just needed so much time or whether there was a programming error. With DoEvents the screen update works always. If you have a lot of data for screen output, this could slow down your macro. Then consider to turn it off by Application.ScreenUpdating=False. If you want to see your progress, write it to the status bar: Application.StatusBar = “any text”

    Monday, December 31, 2018 11:21 AM
  • Ctrl+Break should work.  If not, something is messed up on your machine.  Keep in mind, if you do this over and over, given enough time, those Ctrl+Break interruptions will build up, and randomly cause long-running VBA jobs to stop for no reason whatsoever.  At that point, you will definitely need to reboot your machine, to clear out all those built-up Ctrl_breaks.

    MY BOOK

    Wednesday, January 9, 2019 2:42 PM
  • Thank you.  I typed in "keyboard" and the on screen keyboard opened.  Clicked ScrLk and the computer Cntrl key and it finally broke the loop.
    Friday, August 23, 2019 2:51 AM