CODEWITHSUNDEEP
excelvbaerror-handling
TristB
TristB

Reputation: 17

How to display a changing message box on error and then continue code

I have a macro that runs multiple other macros. I would like for the code to skip to the next macro if there is an error with one of the macros it calls. I would also like that a message box appears when an error occurs so that I know in which macro the error occured. So, I would like that the message box that appears if an error occurs changes so that it tells me the name of that macro.


Sub Update_ALL()

                ' Run All the macros

                    Call Macro_1

                    Call Macro_2

                    Call Macro_3

                    Call Macro_4  

                    Call Macro_5  

                    Call Macro_6 

                    Call Macro_7 

                    Call Macro_8                    

End Sub

The code is very simple but I can't figure out how to skip to the next macro on error and have a changing message box.

Thanks for the help!

Upvotes: 1

Views: 1614

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Change your "macros" to handle and re-throw errors, specifying an argument for the error's Source in the call to Err.Raise:

Public Sub Macro_1()
    On Error GoTo CleanFail
    '...original code here...
    Exit Sub
CleanFail:
    Err.Raise Err.Number, "Macro_1", Err.Description
End Sub

If an error occurs while executing Macro_1, the Err.Source will be "Macro_1".

Now in the "main" procedure, you need to handle these errors bubbling up - pop a MsgBox and use Err.Source as e.g. the title (or as you please), and then Resume Next to clear the error state and move on to the next macro:

Public Sub Update_All()
    On Error GoTo CleanFail
    Macro_1
    Macro_2
    '...
    Macro_8
    Exit Sub
CleanFail:
    MsgBox Err.Description, vbExclamation, Err.Source
    Resume Next
End Sub

Consider renaming your procedures using meaningful names, e.g. FormatSummarySheet, or UpdateOrdersTable.

Upvotes: 1

Related Questions