Stop processing when there was an error in the previous of consecutively called subs

user_9627219 0
Viewed 105

I have three subs that runs one after another, the subs are called DataTrimming, Pivot and Email.

Sub AllTogether()
    DataTrimming
    Pivot
    Email
End Sub

I put an error handler inside Sub DataTrimming, so when there is an error, there will be an error message and exit the sub.

When I run sub AllTogether, it shows the error message when there is an error from Sub DataTrimming and it continues to run sub Pivot and sub Email.

Is there a way to exit sub AllTogether and not run the rest of the subs when an error occurs in sub DataTrimming?

vba excel
3 Answers

Use an On Error statement to handle runtime errors and redirect execution to a line label:

Sub AllTogether()
    On Error GoTo CleanFail

    DataTrimming
    Pivot
    Email

CleanExit:
    Exit Sub
CleanFail:
    'break here and debug if needed; use Resume without a label to get back to the failing call.
    Resume CleanExit
End Sub

If any of the called procedures have their own respective error-handling subroutines, then you'll want to re-throw the error in that procedure in order to have it handled in the scope of the AllTogether procedure, using Err.Raise like so:

Sub DataTrimming()
    On Error GoTo CleanFail
    '...do stuff...
CleanExit:
    Exit Sub
CleanFail:
    '...handle errors locally
    Err.Raise Err.Number 'rethrows the same error with the same message
End Sub

Side note, the procedures have a temporal coupling issue because they are clearly side-effecting procedures that must be invoked in a particular specific order. To fix this, you would turn DataTrimming into a Function procedure that returns the data that the Pivot procedure needs to intake as parameters; similarly, making Pivot a function that returns data that the Email procedure needs as parameters, would remove the coupling and reduce (or remove!) reliance on global state - in the end it might look something like this:

Dim trimmedData As Object
Set trimmedData = DataTrimming

Dim pivotedData As Object
Set pivotedData = Pivot(trimmedData)

Email pivotedData

See this post for various ways of passing data around procedures without involving globals.

user_11885130

One way of doing this would be to change your three subs DataTrimming, Pivot and Email to Functions instead, each of which returns a Boolean. Return True from each unless they hit an error, in which case return False.

In Altogether(), check the value that comes back from DataTrimming before continuing to Pivot (if False, Exit Sub) and the same for continuing from Pivot to Email.

user_33443770

Error Handling Among Procedures

  • You could rewrite your DataTrimming procedure as a function returning a boolean (e.g. True if successful) and use the boolean in the calling procedure (give it a better name though).
Option Explicit

Sub AllTogether()
    
    If wasDataTrimmingSuccessful Then
        Pivot
        Email
    End If
    
End Sub


Function wasDataTrimmingSuccessful() As Boolean
    
    On Error GoTo clearError
    
    
    ' DataTrimming code in here
    
    
    wasDataTrimmingSuccessful = True
    
ProcExit:
    
    Exit Function

clearError:
    'Debug.Print "Run-time error '" & Err.Number & "':" & Err.Description
    Resume ProcExit

End Function
user_98140690
Related