Search code examples
vbascriptingexcelms-office

VBA: How to display an error message just like the standard error message which has a "Debug" button?

I created an error-handler using On Error Goto statement, and I put a few lines of cleaning code and display the error message, but now I don't want to lose the comfortableness of the default handler which also points me to the exact line where the error has occured. How can I do that?

Solution

  • First the good news. This code does what you want (please note the "line numbers")

    Sub a()
 10:    On Error GoTo ErrorHandler
 20:    DivisionByZero = 1 / 0
 30:    Exit Sub
 ErrorHandler:
 41: If Err.Number <> 0 Then
 42:    Msg = "Error # " & Str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
 43:    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
 44:    End If
 50:    Resume Next
 60: End Sub

    When it runs, the expected MsgBox is shown:

    alt text

    And now the bad news:
    Line numbers are a residue of old versions of Basic. The programming environment usually took charge of inserting and updating them. In VBA and other "modern" versions, this functionality is lost.

    However, Here there are several alternatives for "automatically" add line numbers, saving you the tedious task of typing them ... but all of them seem more or less cumbersome ... or commercial.

    HTH!