Excel VBA: Re-throw error/exceptions

Microsoft Excel1 Comment on Excel VBA: Re-throw error/exceptions

Excel VBA: Re-throw error/exceptions

The current version of Microsoft Excel does not support the new Try/Catch blocks found in VB.net or any other modern programming languages. To handle errors within a procedure the “On Error Goto” statement must be used. When used, VBA expects the procedure to handle the error by either displaying a message visible to the user or just ignore it and continue as before.

However, if you are a Java or .NET developer you might want to restore the state of the application when a error is caught and then re-throw the error to let another part of the application handle it, just like when the “using” statement or throw/catch blocks are applied in C# code. There is no direct way of re-throwing a error in VBA, like throw or throw [Exception] in Java and .NET. The correct way of solving this challenge is to use the Err.Raise method, and specify all the existing error parameters in the following way:

Sub Test()
    On Error GoTo ErrorHandler
   
    ' Do Something   
    Exit Sub
ErrorHandler:
    ' Restore state

    ' Re-throw/Raise existing error   
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,    Err.HelpContext
End Sub

A custom method can be made to simplifying the re-throwing of errors:

' Rethrow
Public Sub ReThrowError(ByVal objError As ErrObject)
    ' Raise   
    Err.Raise objError.Number, objError.Source, objError.Description, objError.HelpFile, objError.HelpContext
End Sub

Related

Ulf Emsoy has long working experience in project management, software development and supply chain management.

One thought on “Excel VBA: Re-throw error/exceptions

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top


Subscribe to our newsletter