asked on

Code that On Error will stop a parent macro

I like to nest macros inside each other to make a very long code more manageable, but occasionally I have a situation in which I want to end the parent macro if certain errors occur in one of the child macros. Sometimes the child macro in question is 2 levels down as in this example:

Sub AddCustomers()
Call AddSingleCustomer
Call OpenOneBook

Is there something I can put in "OpenOneBook" that will shut down the "AddCustomers" macro?

Thanks,
John

Sub OpenOneBook()
.....
Set cel = Columns("A").Find(What:=[Q1], after:=Cells(1, 1), LookIn:=xlValues)
    Dim wb As String
    wb = [Q1] & " " & ActiveSheet.[annum] & " " & "Week " & [F1] & "_2BCoded.xls"
On Error GoTo here
Workbooks.Open Filename:=Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) - 11) & [Q1] & "\3_Working Files\2BCoded\" & wb
Call AddNewWeekOneBook
here:
MsgBox "This workbook has not been created yet, or has been renamed."
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

Not for points:

You can also use deliberate errors that get passed up the call stack, which allows you to pass a specific error number:

Sub AddCustomers()
   On Error GoTo err_handler
'  Call AddSingleCustomer
    Call openonebook
    
    MsgBox "got here and continuing code"
    
   Exit Sub
   
err_handler:
   If Err.Number - vbObjectError = 5000 Then
      MsgBox "error in OpenOneBook"
   Else
      Resume Next
   End If
End Sub
Sub openonebook()
   On Error GoTo err_handler
   Workbooks.Open "this path can't exist"
   
   Exit Sub
   
err_handler:
   Err.Raise 5000 + vbObjectError
End Sub

Open in new window

for example.

John Carney

ASKER

Thanks! The only thing better than getting a solution when you need one is learning something new tabout which you didn't have a clue before.

Rory, I'm not sure how to implement your solution but I have a feeling it will be a great tool when I figure out how to use it, so thanks. I'm going to honor your request and give the points to Dave.

Thank you both. Have I said thank you enough :- )

- John

dlmille

E-E is such a wealth of knowledge and its additional tips like what Rory adds that make it worthwhile for me, as well. I'd never thought of raising errors in subordinate processes and I'm cogitating on it, the pros/cons/etc., but its great to know there's another accepted way to go at the problem.

Thanks for the points, though I'm always happy to share on collaborative items.