• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to abruptly stop a running VBA code

R

Raja Kamarthi

New Member
Team,


Is there a way to stop a running VBA code?


Have a code which loops based on a condition and the condition has gone wrong and inturn executing the code to run for ever.


Any quick thoughts?


Raja
 
Hi Raja ,


The standard way to interrupt execution of a program is CTRL BREAK.


However , that is a minor point ; a more important point is :


never use an equal to condition to stop a loop ; always use >= or <= , depending on which is applicable ; a check for equality may not result in a TRUE value depending on several factors , some of which do not have to do with logic , but with the way the computer does calculations and / or stores numbers.


Checking for >= or <= is always preferable to checking strictly for equality , unless you can ensure that the result will always happen , at some stage.


Of course , in your case , the problem is that you are not incrementing the loop variable. Instead of :

[pre]
Code: 
[A1] = i + 1
use

i = i + 1
[A1] = i
[/pre]
Narayan
 
Many Thanks, Deb


I tried stopping the code but failed and suddenly all my open excel files got closed.


My hardwork put on a report had gone waste since I didnt save.


Need to start again from scratch :(


Raja
 
HI


Of course you can program in an "exit" with error trapping:


Sub KeepDoingSomething()


Application.EnableCancelKey = xlErrorHandler

On Error GoTo ErrHandler


Dim i As Integer

i = 0

Do Until i = 10

[A1] = i + 1

Debug.Print i

Loop


Application.EnableCancelKey = xlInterrupt

Exit Sub


ErrHandler:

If Err.Number = 18 Then

lContinue = MsgBox(prompt:=Format(x / y, "0.0%") & _

" complete" & vbCrLf & _

"Do you want to Continue (YES)?" & vbCrLf & _

"Do you want to QUIT? [Click NO]", _

Buttons:=vbYesNo)

If lContinue = vbYes Then

Resume

Else

Application.EnableCancelKey = xlInterrupt

MsgBox ("Program ended at your request")

Exit Sub

End If

End If


Application.EnableCancelKey = xlInterrupt

End Sub


Which monitors Esc or Ctrl+Break during the running of your code.


G
 
Hi, Raja Kamarthi!


A good practice to avoid unwanted eternal loops when developing VBA code is to use the DoEvents statement within the loop, so as to enable single Ctrl-Break key pressings to be handled by Excel and make it enters in debugging mode.


Despite the right correction made by NARAYANK991, in your original code (if in the corrected it wouldn't have sense), try this:

-----

[pre]
Code: 
Sub KeepDoingSomething()
Dim i As Integer
i = 0
Do Until i = 10
[A1] = i + 1
Debug.Print i
DoEvents
Loop
End Sub
[/pre]
-----


And then run it and press Ctrl-Break, it should stop and enter in debugging mode instantly.


Regards!
 
You must log in or register to reply here.
Back
Top