How to Cancel a Sub and pass it on to the Calling Sub

[kdsdata]

Introduction
There are a number of (good) examples of how to cancel via forms, controls and msgbox(s). However, there is often a need to execute a cancel that carries up through calling subs. Here is an example of 2 subs that do just that. The example is writen in a module in 2003 Excel VBA.

Details
First a global variable bolCancel is declared in the Declaration Section. Then the main sub is executed, and the bolCancel is initialized with a "no" cancel condition.

Declaration Section

Option Explicit
 
'declare bolCancel as a global variable
Public bolCancel As Boolean
 

The main sub topSub is intended to show that it executes some code. A msgbox is used to simulate some code for the basis of the decision to cancel or to proceed execution of code.

topSub

Public Sub topSub()
Dim choice As Integer
 
    'initialize Cancel, do this first
    bolCancel = False
 
    'could execute some code here
 
    'to test without code
    'msgbox is used to simulate Cancel/No-cancel
    choice = MsgBox("1. topSub," & _
        vbCr & "Click OK to test NO cancel in this topSub," & _
        vbCr & "Click Cancel to test YES cancel", vbOKCancel)
 
    'chech the cancel condition by some code above
    Select Case choice  'choice could be created by any other code
        Case 1  'ok (return code by msgbox)
            'call some other sub
            MsgBox "2. topSub," _
                & vbCr & "this topSub has NO cancel condition," & _
                vbCr & "and will call SomeOtherSub now", vbOKOnly
            someOtherSub
        Case 2  'Cancel (return code by msgbox)
            'this topSub code has a cancel condition
            MsgBox "3. topSub," & _
                vbCr & "this topSub has a cancel condition," & _
                vbCr & "and will cancel/exit now", vbOKOnly
            Exit Sub
    End Select
 
    'check if Sub Call returned a Cancel
    Select Case bolCancel
        Case False  'No Cancel
            'call some other sub
            MsgBox "4. topSub," & _
                vbCr & "SomeOtherSub did NOT return Cancel," & _
                vbCr & "and is executed normally through here", vbOKOnly
            'execution continues after "end select"
        Case True  'Yes Cancel
            'call some other sub
            MsgBox "5. topSub," & _
                vbCr & "SomeOtherSub did return Cancel," & _
                vbCr & "and is aborted through here", vbOKOnly
            'therefore we can also cancel the topSub, if necessary
            Exit Sub
    End Select
 
    'remaining code here
    MsgBox "7. topSub," & _
        vbCr & "this topSub has executed normally," & _
        vbCr & "and will exit now", vbOKOnly
 
End Sub
 

From the main sub topSub we go two ways.

On a "yes" cancel in topSub, there is no need to use bolCancel, just an Exit Sub is sufficient to abort.

A "no" cancel just allows the code to continue in topSub by calling someOtherSub.

someOtherSub

Sub someOtherSub()
Dim response As Integer
 
    'execute some code
 
    'to test without code
    'the message box simulates code that causes a condition,
    'from where you want to Cancel the call to SomeOtherSub
    response = MsgBox("8. SomeOtherSub," & _
        vbCr & "Click OK to test NO cancel in this SomeOtherSub," & _
        vbCr & "Click Cancel to test YES cancel", vbOKCancel)
 
    'chech what was returned by the message box, or some other code above
    Select Case response
        Case 1  'ok
            'execute some other code
            'execution continues after "end select"
        Case 2  'Cancel
            'cancel and return to calling sub
            MsgBox "9. SomeOtherSub," & _
                vbCr & "this SomeOtherSub has a cancel condition," & _
                vbCr & "and will cancel/exit now", vbOKOnly
            bolCancel = True
            Exit Sub
    End Select
 
    'other code here
    MsgBox "10. SomeOtherSub," & _
        vbCr & "after executing code," & _
        vbCr & "this SomeOtherSub is returning normally", vbOKOnly
 
End Sub
 

In the called SomeOtherSub we also have code to simulate a cancel/no-cancel conditions. However here, on a cancel request, the global bolCancel is set to True and the sub is exited/aborted.

When SomeOtherSub returns, topSub can check for a cancel request and act accordingly.

Conclusion
There are no doubt other ways of achieving the same purpose. The intent here is to show an example with more detail and possible methods for exiting/aborting. I have fond (no criticism intended) other examples often exclude details, as they are meant to be intuitively obvious. To me, "intuitively obvious" never is. Hope this helps some of you.