CODEWITHSUNDEEP
excelvbaexception
plainerman
plainerman

Reputation: 445

Rethrow error with Err.Raise

Info: Thanks to Gary McGill I could improve the source code of my question.

In my current project I have got a sub which handles all errors. The following should happen:

  1. Start Sub
  2. Send email
  3. Show default Debug-Error window (which points to the original error line when clicking "Debug")

The main point here is to find out in which line the error occured.

Private Sub Foo()
    On Error GoTo ErrorHandling
    a = 7 / 0

    Exit Sub
ErrorHandling:
        errorNumber = Err.Number
        errorSource = Err.Source
        errorDescription = Err.Description
        errorHelpFile = Err.HelpFile
        errorHelpContext = Err.HelpContext
    On Error GoTo 0
    Call HandleError(errorNumber, errorSource, errorDescription, errorHelpFile, errorHelpContext)
End Sub

Public Sub HandleError(errorNumber As Integer, errorSource As String, errorDescription As String, errorHelpFile As String, errorHelpContext As String)
    Call SendMail(subject, body, mail)
    Err.Raise errorNumber, errorSource, errorDescription, errorHelpFile, errorHelpContext
End Sub

But if I do it that way, the newly created error window simply points to the Err.Raise line, not the actual line in which the error occured. Numbering the lines with an addon is no solution since my companies restrictions do not allow addons.

Are there any other possible solutions?

Edit

So instead of this, the line a = 7 / 0 should be highlighted

Edit 2

Sadly, this does not work:

Private Sub Foo()
    On Error GoTo ErrorHandling
    a = 7 / 0

    Exit Sub
    ErrorHandling:
        ...
        Call HandleError(...)
        GoTo 0
End Sub

Upvotes: 4

Views: 1453

Answers (3)

Andre
Andre

Reputation: 27634

If you use error handling in VBA and want to get the line where the runtime error occured, you need Erl() - and for that you need line numbers, plain and simple.

Numbering the lines with an addon is no solution since my companies restrictions do not allow addons.

You should really ask them to rethink this policy. Programming VBA without MZ-Tools isn't fun, the Global Search and Procedure Callers functions alone are invaluable.

Of course you don't have line numbers on when developing, you add them when giving the code into production.

Upvotes: 1

andshrew
andshrew

Reputation: 418

You can use Stop and Resume to return to the line that threw an error however you couldn't use this in production code. What this will do is stop the code running, you can then use F8 to Step Into the next line of code which will return you to the line that raised the error. 

    Private Sub Foo()
    On Error GoTo ErrorHandling
    a = 7 / 0

    Exit Sub
ErrorHandling:
    errorNumber = Err.Number
    errorSource = Err.Source
    errorDescription = Err.Description
    errorHelpFile = Err.HelpFile
    errorHelpContext = Err.HelpContext

    HandleError errorNumber, errorSource, errorDescription, errorHelpFile, errorHelpContext
    Stop
    Resume
End Sub

Public Sub HandleError(errorNumber As Integer, errorSource As String,  errorDescription As String, errorHelpFile As String, errorHelpContext As String)
    Call SendMail(Subject, body, mail)

End Sub

If you need something which will provide error reports from production code I don't believe there is a way to return the Line that threw the error without numbering your lines and using Erl. The alternative to this would be to implement your own error raising with meaningful codes which would allow you to identify which part of your code caused an error.

Upvotes: 0

Gary McGill
Gary McGill

Reputation: 27516

There are a few issues with the code as (originally) posted:

  1. The On Error Goto 0 in HandleError will clear the values of Err.Number etc. All the On Error variants effectively do an Err.Clear. You could try saving those values at the start of the function for later use.
  2. It looks like your call to HandleError will raise an error, which in turn will go un-handled, and drop through to your ErrorHandling error handler again, which will just loop? So actually, you might need to catch the various error values in the error handler, then disable the error handler, and then pass them to the HandleError function as parameters. Maybe something like this:

.

Public Sub Foo()
    On Error Goto ErrorHandling
    number = 7/0
    Exit Sub
ErrorHandling:
    errorNumber = Err.Number
    errorSource = Err.Source
    errorDescription = Err.Description
    errorHelpFile = Err.HelpFile
    errorHelpContext = Err.HelpContext
    On Error Goto 0
    Call HandleError(errorNumber, errorSource, errorDescription, errorHelpFile, errorHelpContext)
End Sub

Public Sub HandleError(errorNumber As Integer, errorSource As String, errorDescription As String, errorHelpFile As String, errorHelpContext As String)
    Call SendMail(subject, body, mail)
    Err.Raise errorNumber, errorSource, errorDescription, errorHelpFile, errorHelpContext 
End Sub

I've not tried it, but it looks like it might work.

Upvotes: 3

Related Questions