How to stop VBA code running?

Question

Say I have a button embedded into my spreadsheet that launches some VBA function.

Private Sub CommandButton1_Click()
    SomeVBASub
End Sub

Private Sub SomeVBASub
    DoStuff
    DoAnotherStuff
    AndFinallyDothis
End Sub

I'd like to have an opportunity to have some sort of a "cancel" button that would stop SomeVBASub execution at an arbitrary moment, and I'm not into involving Ctrl+Break here, 'cause I'd like to do it silently.

I guess this should be quite common issue, any ideas?

Thanks.

Jamie Treworgy · Accepted Answer · 2010-10-20 16:10:44Z

22

Add another button called "CancelButton" that sets a flag, and then check for that flag.

If you have long loops in the "stuff" then check for it there too and exit if it's set. Use DoEvents inside long loops to ensure that the UI works.

Bool Cancel
Private Sub CancelButton_OnClick()
    Cancel=True
End Sub
...
Private Sub SomeVBASub
    Cancel=False
    DoStuff
    If Cancel Then Exit Sub
    DoAnotherStuff
    If Cancel Then Exit Sub
    AndFinallyDothis
End Sub

answered Oct 20, 2010 at 16:10

Jamie Treworgy

24.4k9 gold badges81 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kirill Leontev Over a year ago

I had an idea to implement this sort of logic but it quite embarasses me to put DoEvents-Check flag chain in EVERY possible procedure's exit point (literally, after nearly each string).

Jamie Treworgy Over a year ago

The method @Faheemitian describes below avoids that, but that works pretty much the same as Ctrl+Break. It depends on how much control you want over the user interface (e.g. a button) and where you can allow your code to exit. If UI isn't important and the code exits can be arbitrary (e.g. no issues with dependencies, rollbacks, etc) then that would be easiest. Usually adding traps for a cancel flag isn't too burdensome since most of the execution time would be spent in loops, but perhaps that is not the case in your situation.

Jamie Treworgy Over a year ago

Let me also add that if you're concerned about the procedure not stopping immediately when they click "cancel" (hence the need for you to add so many checks), what I usually do is disable the Cancel button and change the text to "Wait..." or something in the Click procedure, so the user is aware that the application is actually considering their request.

Faheem · Accepted Answer · 2010-10-20 16:17:07Z

9

How about Application.EnableCancelKey - Use the Esc button

On Error GoTo handleCancel
Application.EnableCancelKey = xlErrorHandler
MsgBox "This may take a long time: press ESC to cancel"
For x = 1 To 1000000    ' Do something 1,000,000 times (long!)
    ' do something here
Next x

handleCancel:
If Err = 18 Then
    MsgBox "You cancelled"
End If

Snippet from http://msdn.microsoft.com/en-us/library/aa214566(office.11).aspx

answered Oct 20, 2010 at 16:17

Faheem

3,57922 silver badges28 bronze badges

Comments

simpLE MAn · Accepted Answer · 2014-03-25 19:33:51Z

2

Or, if you want to avoid the use of a global variable you could use the rarely used .Tag property of the userform:

Private Sub CommandButton1_Click()
    Me.CommandButton1.Enabled = False 'Disabling button so user cannot push it
                                      'multiple times
    Me.CommandButton1.caption = "Wait..." 'Jamie's suggestion
    Me.Tag = "Cancel"
End Sub

Private Sub SomeVBASub
    If LCase(UserForm1.Tag) = "cancel" Then
        GoTo StopProcess
    Else
        'DoStuff
    End If

Exit Sub
StopProcess:
    'Here you can do some steps to be able to cancel process adequately
    'i.e. setting collections to "Nothing" deleting some files...
End Sub

edited Mar 25, 2014 at 19:33

answered Mar 25, 2014 at 19:02

simpLE MAn

1,62214 silver badges23 bronze badges

Comments

Beth · Accepted Answer · 2010-10-20 16:13:55Z

0

what jamietre said, but

Private Sub SomeVBASub
    Cancel=False
    DoStuff
    If not Cancel Then DoAnotherStuff
    If not Cancel Then AndFinallyDothis
End Sub

answered Oct 20, 2010 at 16:13

Beth

9,6171 gold badge26 silver badges44 bronze badges

Comments

Kenson Gurney · Accepted Answer · 2014-07-16 14:26:31Z

I do this a lot. A lot. :-)

I have got used to using "DoEvents" more often, but still tend to set things running without really double checking a sure stop method.

Then, today, having done it again, I thought, "Well just wait for the end in 3 hours", and started paddling around in the ribbon. Earlier, I had noticed in the "View" section of the Ribbon a "Macros" pull down, and thought I have a look to see if I could see my interminable Macro running....

I now realise you can also get this up using Alt-F8.

Then I thought, well what if I "Step into" a different Macro, would that rescue me? It did :-) It also works if you step into your running Macro (but you still lose where you're upto), unless you are a very lazy programmer like me and declare lots of "Global" variables, in which case the Global data is retained :-)

K

Paul Clint · Accepted Answer · 2015-04-01 10:51:00Z

0

~ For those using custom input box

Private Sub CommandButton1_Click()

DoCmd.Close acForm, Me.Name
End

End Sub

answered Apr 1, 2015 at 10:51

Paul Clint

1011 gold badge2 silver badges10 bronze badges

Comments

pgSystemTester · Accepted Answer · 2019-06-19 01:33:02Z

This is an old post, but given the title of this question, the END option should be described in more detail. This can be used to stop ALL PROCEDURES (not just the subroutine running). It can also be used within a function to stop other Subroutines (which I find useful for some add-ins I work with).

As Microsoft states:

Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement, and to clear variables*. I noticed that the END method is not described in much detail. This can be used to stop ALL PROCEDURES (not just the subroutine running).

Here is an illustrative example:

Sub RunSomeMacros()

    Call FirstPart
    Call SecondPart

    'the below code will not be executed if user clicks yes during SecondPart.
    Call ThirdPart
    MsgBox "All of the macros have been run."

End Sub

Private Sub FirstPart()
    MsgBox "This is the first macro"

End Sub

Private Sub SecondPart()
    Dim answer As Long
    answer = MsgBox("Do you want to stop the macros?", vbYesNo)

    If answer = vbYes Then
        'Stops All macros!
        End
    End If

    MsgBox "You clicked ""NO"" so the macros are still rolling..."
End Sub

Private Sub ThirdPart()
    MsgBox "Final Macro was run."
End Sub

Collectives™ on Stack Overflow

How to stop VBA code running?

7 Answers 7

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related