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

Question

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

Thanks in advance.

Answer 1

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:

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!