Sunday, April 08, 2012 7:01 AM
I usually use VBA to work with numerical methods and that means sometimes long calculations in their execution.
Sometimes I want to stop the execution of code and simply use "esc" and it stops, but sometimes Excel put itself into "unresponsive mode" and there is no way to stop the execution without forcing the closure of Excel ("esc" or Ctrl+break don´t work) or wait until it finishes.
does anyone know any effective way to stop the execution of a macro always without closing Excel?
Note: The problem occurs inExcel 2010 but also in Excel 2003.
Thank you for your answers.
Sunday, April 08, 2012 9:00 AM
Insert a line
into your loop (I assume your calcuation involves loops). This will give Windows a chance to process events such as pressing Ctrl+Break). Calling DoEvents will have a slight impact on performance, but hopefully not too much.
Regards, Hans Vogelaar
Sunday, April 08, 2012 10:02 AM
Yes, you assume correctly thinking my calculations involve loops, it is usual working with numerical methods, and sometimes loops with millions of iterations, so I would preffer not to impact the algorithm efficiency using events but after googling a lot, I haven't found and answer beyond the "esc" or "Ctrl + break" (yours is a possibility but I guess it will damage the efficiency), a simple key combination or other simple way to stop the macro execution without restarting Excel, I'm starting to think simple it doesn't exist in Excel VBA.
Sunday, April 08, 2012 11:58 AM
If you want your code to run as efficiently as possible, you must ignore events such as mouse clicks and keystrokes.
If you want your macro to react to mouse clicks and keystrokes, it must spend a little bit of time to "listen" for those events (that's what DoEvents does).
You can't have both - the requirements are mutually exclusive.
Regards, Hans Vogelaar
Sunday, April 08, 2012 4:11 PMModerator
Make use of EnableCancelKey, eg
Sub test() ' size the VBE so you can see A1 Dim i As Long, j As Long MsgBox "Press Esc to break the indefinate loop" Application.EnableCancelKey = xlErrorHandler On Error GoTo errH While i > -1 i = i + 1 If i = 1000000 Then j = j + 1 i = 0 Range("A1") = j End If Wend done: Application.EnableCancelKey = xlInterrupt ' ensure this is reset MsgBox "done" Exit Sub errH: If Err.Number = 18 Then ' user pressed Esc If MsgBox("Press Yes to Continue or No to abort", vbYesNo) = vbYes Then Resume End If Else ' some other error MsgBox Err.Description End If Resume done End Sub
In passing, if using DoEvents in a long loop don't call it in every loop or things may slow things down significantly. Maybe use inner and outer loops and call DoEvents in the outer, eg every 1000 interations of the inner.
Sunday, April 08, 2012 6:42 PM
Thank you both for your answers:
I have performed serveral test and I'm going to share them with every body reading this forum.
I have used doevents in a long bucle, without using doevents the bucle executed around 42 million times in 10 seconds, if I use doevents I can stop the macro without any problem but the performance is seriously reduced to 1.6 million times in one minute (this is a great impact in long bucles).
The applicaction.EnableCancelkey way, works fine to stop the macro but only until Excel went to "unresponsive mode" then no way to stop the macro until it finished but in practice it doesn´t affect the performance of the macro.
the conclusion for me is I will try not to forget to save the Excel sheet before running the macro but the unique method I know always work (doevents) is very inefficient in long programs, so if the cost is so high it is better to close Excel directly, as told before I´ve been googling a lot and I have'nt seen any other way, so maybe it doesn´t exist.
Any other idea?
Kind regards again.
Sunday, April 08, 2012 7:50 PMModerator
In some scenarios running a loop can make Excel appear unresponsive, at least according to the caption. It may or may not mean anything serious.
As I mentioned calling DoEvents in every loop can slow things down significantly - don't do that! Depending on your code construct inner and outer loops such that DoEvents is called roughly every 0.1 to 0.3 seconds, that's enough.
If you are running from a form you could have a cancel button to set a global flag, eg
Public gbCancelMyMacro As Boolean ' in a normal module
In your code regularly check the flag (checking a boolean is trivial) and if true bail out (perhaps after asking user). If screenupdating is disabled be sure to re-enable it before showing the message, and disable again if user wants to continue. Don't forget of course to cancel the flag.
- Marked As Answer by Yoyo JiangMicrosoft Contingent Staff, Moderator Friday, April 20, 2012 3:08 AM