CODEWITHSUNDEEP
excelvbaerror-handling
NorwegianLatte
NorwegianLatte

Reputation: 89

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

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?

Upvotes: 2

Views: 201

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

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.

Upvotes: 3

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 1

Srhino
Srhino

Reputation: 21

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.

Upvotes: 2

Related Questions