Solved

How to exit the calling procedure when the user cancels a sub-procedure

Posted on 2011-09-09
8
253 Views
Last Modified: 2012-06-27
I am trying to find a way to exit the calling procedure when the user cancels a sub-procedure (a procedure called from within another procedure);
MyTestSub()

Dim Vars ' declare variables
     
On Error GoTo MyErrorHandler

Call MyFirstSub
     ' In this sub I prompt the user to cancel if a certain condition is true
     ' Answer = MsgBox("Do you want to continue ?", vbOKCancel, "Multiple Currency Types Selected")
     ' If I cancel here it goes on to the next sub - MySecondSub
     ' I want to have it exit MyTestSub
 

Call MySecondSub

Call OtherSubs

 
 MsgBox "Processing Complete for Business Report"

Exit Sub

MyErrorHandler:
  MsgBox Err.Description, _
    vbExclamation + vbOKCancel, _
    "Error: " & CStr(Err.Number)
   
End Sub
0
Comment
Question by:Ed_CLP
  • 4
  • 3
8 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 250 total points
Comment Utility
There are various approaches to this issue, including raising custom errors and handling them, but I prefer simply to make MySecondSub a Function returning a Boolean value - True if it succeeds, and false if the user cancelled.

Then you can use code like

     If Not MySecondSub() Then Exit Sub

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
You can end all code execution with End.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
not for points...

I follow @andrewssd3 style.    It would be:

if not myFirstSub() then exit sub

Dave
0
 
LVL 17

Expert Comment

by:andrewssd3
Comment Utility
Yes - just saying End is a bit brutal - you would usually need to do some tidying up, destroy objects, etc.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

by:Norie
Comment Utility
You could say turning a sub into a function is a bit much.

I suppose there's also the option of a public variable.

I'm sure there's other methods too.:)
0
 
LVL 17

Expert Comment

by:andrewssd3
Comment Utility
Agreed - It's really just a matter of preference.  I've got in the habit of making all called procedures Functions because it does generally make life easier, and you almost always need to know whether a called subroutine has run successfully.  I know people who like to raise custom errors for everything and have elaborate error handling.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Can't say I've not done that but not very often.

Error handling  in VBA...mmmm.:)
0
 
LVL 17

Expert Comment

by:andrewssd3
Comment Utility
inmorie - my feelings exactly - best avoided!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now