Solved

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

Posted on 2011-09-09
8
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 250 total points
ID: 36513669
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
ID: 36514245
You can end all code execution with End.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36515170
not for points...

I follow @andrewssd3 style.    It would be:

if not myFirstSub() then exit sub

Dave
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:andrewssd3
ID: 36515829
Yes - just saying End is a bit brutal - you would usually need to do some tidying up, destroy objects, etc.
0
 
LVL 33

Expert Comment

by:Norie
ID: 36515907
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
ID: 36516051
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
ID: 36516064
Can't say I've not done that but not very often.

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

Expert Comment

by:andrewssd3
ID: 36516435
inmorie - my feelings exactly - best avoided!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 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