By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 860 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

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

kdsdata
P: 1
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
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. 'declare bolCancel as a global variable
  4. Public bolCancel As Boolean
  5.  
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
Expand|Select|Wrap|Line Numbers
  1. Public Sub topSub()
  2. Dim choice As Integer
  3.  
  4.     'initialize Cancel, do this first
  5.     bolCancel = False
  6.  
  7.     'could execute some code here
  8.  
  9.     'to test without code
  10.     'msgbox is used to simulate Cancel/No-cancel
  11.     choice = MsgBox("1. topSub," & _
  12.         vbCr & "Click OK to test NO cancel in this topSub," & _
  13.         vbCr & "Click Cancel to test YES cancel", vbOKCancel)
  14.  
  15.     'chech the cancel condition by some code above
  16.     Select Case choice  'choice could be created by any other code
  17.         Case 1  'ok (return code by msgbox)
  18.             'call some other sub
  19.             MsgBox "2. topSub," _
  20.                 & vbCr & "this topSub has NO cancel condition," & _
  21.                 vbCr & "and will call SomeOtherSub now", vbOKOnly
  22.             someOtherSub
  23.         Case 2  'Cancel (return code by msgbox)
  24.             'this topSub code has a cancel condition
  25.             MsgBox "3. topSub," & _
  26.                 vbCr & "this topSub has a cancel condition," & _
  27.                 vbCr & "and will cancel/exit now", vbOKOnly
  28.             Exit Sub
  29.     End Select
  30.  
  31.     'check if Sub Call returned a Cancel
  32.     Select Case bolCancel
  33.         Case False  'No Cancel
  34.             'call some other sub
  35.             MsgBox "4. topSub," & _
  36.                 vbCr & "SomeOtherSub did NOT return Cancel," & _
  37.                 vbCr & "and is executed normally through here", vbOKOnly
  38.             'execution continues after "end select"
  39.         Case True  'Yes Cancel
  40.             'call some other sub
  41.             MsgBox "5. topSub," & _
  42.                 vbCr & "SomeOtherSub did return Cancel," & _
  43.                 vbCr & "and is aborted through here", vbOKOnly
  44.             'therefore we can also cancel the topSub, if necessary
  45.             Exit Sub
  46.     End Select
  47.  
  48.     'remaining code here
  49.     MsgBox "7. topSub," & _
  50.         vbCr & "this topSub has executed normally," & _
  51.         vbCr & "and will exit now", vbOKOnly
  52.  
  53. End Sub
  54.  
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
Expand|Select|Wrap|Line Numbers
  1. Sub someOtherSub()
  2. Dim response As Integer
  3.  
  4.     'execute some code
  5.  
  6.     'to test without code
  7.     'the message box simulates code that causes a condition,
  8.     'from where you want to Cancel the call to SomeOtherSub
  9.     response = MsgBox("8. SomeOtherSub," & _
  10.         vbCr & "Click OK to test NO cancel in this SomeOtherSub," & _
  11.         vbCr & "Click Cancel to test YES cancel", vbOKCancel)
  12.  
  13.     'chech what was returned by the message box, or some other code above
  14.     Select Case response
  15.         Case 1  'ok
  16.             'execute some other code
  17.             'execution continues after "end select"
  18.         Case 2  'Cancel
  19.             'cancel and return to calling sub
  20.             MsgBox "9. SomeOtherSub," & _
  21.                 vbCr & "this SomeOtherSub has a cancel condition," & _
  22.                 vbCr & "and will cancel/exit now", vbOKOnly
  23.             bolCancel = True
  24.             Exit Sub
  25.     End Select
  26.  
  27.     'other code here
  28.     MsgBox "10. SomeOtherSub," & _
  29.         vbCr & "after executing code," & _
  30.         vbCr & "this SomeOtherSub is returning normally", vbOKOnly
  31.  
  32. End Sub
  33.  
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.
Dec 30 '10 #1
Share this Article
Share on Google+