Forcing a macro cancellation

Question

Good morning,

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.

                                      

Answer 1

Insert a line

        DoEvents

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

Answer 2

Hello Hans:

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.

Kind regards.

 

Answer 3

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

Answer 4

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.

Peter Thornton

Peter Thornton

Answer 5

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.

 

Answer 6

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.

Peter Thornton