Ending a macro within a called subroutine

Question

I have a macro (CMOV) which calls another subroutine (CMOV2) that checks for a condition which, if met, displays a vbokaycancel message box which i set equal to a variable called irep,

I want it if someone hits cancel (irep=2) for it to call off the whole macro. That is not only exit CMOV2 but also exit CMOV.

Currently, I have

If jackal(1) <> vbNullString Then
    irep = MsgBox("Warning: You have a selection with two swingarms" _
          & " that are on the same radius and cannot swing past one another " _
          & Chr$(13) & " Choose Okay if you still wish to proceed otherwise " _
          & " choose Cancel to revise your order" & Chr$(13) & "         " _
          & jackal(1) & Chr$(13) & "      " & jackal(2), vbOKCancel)
    **If irep = 2 Then
    Exit Sub**
    Else: End If
    Else: End If
End Sub

at the end of the subroutine. The issue is that even though this exits the CMOV2, CMOV continues to run. Is there a way to end this sub, and the one which called it?

Tried creating a variable called plzexit in CMOV2 and then created an if statement in CMOV that exits the sub depending on the value of plzexit, but it looks like the variable dies upon exiting the CMOV2 subroutine. — PocketLoan, Sep 13 '13 at 18:08
Change CMOV2 to a function which returns a boolean. If the user cancels in CMOV2 then return False, else return True. In CMOV you can exit if you get back a "False" from CMOV2. — Tim Williams, Sep 13 '13 at 18:47

Cor_Blimey · Accepted Answer · 2013-09-13T18:58:03.497

11

End

Note that End completely stops code execution (within the current call stack so it doesn't effect other projects like Addins etc (a good thing)) but it will close any open file handles (a good thing). On the other hand, , static and module level variables (if you use them) will lose their values and Class terminate methods won't be run so if you have more of an 'app' than a macro this may not be desired.

It sounds like for your purposes this is ok and is probably the simplest way to go about it.

A silly example:

Sub foo()
    Dim i As Long
    For i = 0 To 10
        If i = 2 Then
            Debug.Print "hooray"
            End
        Else
            Debug.Print "hip"
        End If
    Next i
End Sub

edited Sep 13 '13 at 18:58

answered Sep 13 '13 at 18:51

Cor_Blimey

3,260
1
14
20

1

`End` is a wrong way to end like you mentioned. They should ban the word :) – Siddharth Rout Sep 13 '13 at 19:07
@SiddharthRout in many cases yes, but for simple 'request input from user, do something, then finish' macros (not programmes or apps or etc but macros, as in scripts) then it is perfectly alright for most situations. – Cor_Blimey Sep 13 '13 at 19:10
1

`End` is like switching the mains off directly and not properly shutting down your pc. :) What if later the user adds more code which handles objects and totally forgets about `End` ;) I am not saying that your answer is incorrect. I am saying that one should forget that there is `End` available to us in VB :) – Siddharth Rout Sep 13 '13 at 19:13
true, it will certainly 'kill' the rest of your project. Whenever I want to stop execution of the current 'running macro' like this I usually use a function and return `False`, or raise an error which I test for in the calling procedure and exit. @RichardPullman if you read this then you should perhaps consider a little more typing now to save a headache in the future and go with Siddharth's answer (and accept it as the answer accordingly) :) – Cor_Blimey Sep 13 '13 at 19:19

score 5 · Answer 2 · answered Sep 13 '13 at 18:45

5

Declare a Boolean variable at the top and set it to True if the user presses cancel. Here is an example.

Dim ExitAll As Boolean

Sub CMOV()
    '
    '~~> Rest of the code
    '

    ExitAll = False

    CMOV2

    If ExitAll = True Then Exit Sub

    MsgBox "Hello World"

    '
    '~~> Rest of the code
    '
End Sub

Sub CMOV2()
    '
    '~~> Rest of the code
    '
    If jackal(1) <> vbNullString Then
        irep = MsgBox("Some Message", vbOKCancel)
        If irep = 2 Then
            ExitAll = True
            Exit Sub
        End If
    End If
    '
    '~~> Rest of the code
    '
End Sub

answered Sep 13 '13 at 18:45

Siddharth Rout

147,039
17
206
250

I tried this but since the cmov and cmov2 subs are in different modules I get an error that the exitall variable isn't defined within the cmov2() sub – PocketLoan Sep 13 '13 at 19:04
1

@RichardPullman you can access it through qualifying the sub to disambiguate with the module name + what Siddharth says. – Cor_Blimey Sep 13 '13 at 19:05
1

@SiddharthRout +1 for the 'proper' way to go about it ;). Though I reckon that if he genuinely wants to completely 'restart' his macro so next time it runs it is as if it were being run for the first time then `End` is probably sufficient in his case. – Cor_Blimey Sep 13 '13 at 19:07
1

@RichardPullman: Then Define ExitAll As Boolean as PUBLIC in a module :) – Siddharth Rout Sep 13 '13 at 19:10

Ending a macro within a called subroutine

2 Answers2