Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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

0
gabrielPennyback
Asked:
gabrielPennyback
  • 2
1 Solution
 
dlmilleCommented:
Let me suggest a simple approach.  Change the subordinate macros to functions (or, alternatively, create an additional parameter to pass a boolean result through and continue to use as a subroutine - or create a global variable that the child function can change) such that if the result is TRUE/FALSE based on the status of the child macros.

In that way, the parent macro can continue or not, based on that status.

Example:
 
Sub AddCustomers()
  Call AddSingleCustomer
    If OpenOneBook = True Then
        'continue processing
    Else
        'terminate processing - end the parent macro AddCustomers
        Exit Sub
    End If
End Sub


Function OpenOneBook() As Boolean
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

    OpenOneBook = True
    Exit Function
    
here:
    OpenOneBook = False
    MsgBox "This workbook has not been created yet, or has been renamed."
End Function

Open in new window

------------------------------
The alternative is to use a global variable, or just pass a boolean status variable through to your child function that the child can change...

Example:
 
Sub AddCustomers()
Dim bStatus As Boolean

  Call AddSingleCustomer
    bStatus = True
    Call OpenOneBook(bStatus)
    
    If bStatus = True Then
        'continue processing
    Else
        'terminate processing - end the parent macro AddCustomers
        Exit Sub
    End If
End Sub


Sub OpenOneBook(bStatus As Boolean)
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

    bStatus = True
    Exit Sub
    
here:
    bStatus = False
    MsgBox "This workbook has not been created yet, or has been renamed."
End Sub

Open in new window


Dave
0
 
Rory ArchibaldCommented:
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.
0
 
gabrielPennybackAuthor Commented:
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
0
 
dlmilleCommented:
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.
0

Featured Post

Technology Partners: 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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now