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

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!

There is a simpler way simply disable the error handler in your error handler if it does not match the error types you are doing and resume.

The handler below checks agains each error type and if none are a match it returns error resume to normal VBA ie GoTo 0 and resumes the code which then tries to rerun the code and the normal error block pops up.

On Error GoTo ErrorHandlerx = 1/0ErrorHandler:if Err.Number = 13 then ' 13 is Type mismatch (only used as an example)'error handling code for thisend ifIf err.Number = 1004 then ' 1004 is Too Large (only used as an example)'error handling code for thisend ifOn Error GoTo 0Resume

This answer does not address the Debug button (you'd have to design a form and use the buttons on that to do something like the method in your next question). But it does address this part:

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.

First, I'll assume you don't want this in production code - you want it either for debugging or for code you personally will be using. I use a compiler flag to indicate debugging; then if I'm troubleshooting a program, I can easily find the line that's causing the problem.

# Const IsDebug = TrueSub ProcA()On Error Goto ErrorHandler' Main code of procExitHere:    On Error Resume Next    ' Close objects and stuff here    Exit SubErrorHandler:    MsgBox Err.Number & ": " & Err.Description, , ThisWorkbook.Name & ": ProcA"    #If IsDebug Then        Stop            ' Used for troubleshooting - Then press F8 to step thru code         Resume          ' Resume will take you to the line that errored out    #Else        Resume ExitHere ' Exit procedure during normal running    #End IfEnd Sub

Note: the exception to Resume is if the error occurs in a sub-procedure without an error handling routine, then Resume will take you to the line in this proc that called the sub-procedure with the error. But you can still step into and through the sub-procedure, using F8 until it errors out again. If the sub-procedure's too long to make even that tedious, then your sub-procedure should probably have its own error handling routine.

There are multiple ways to do this. Sometimes for smaller programs where I know I'm gonna be stepping through it anyway when troubleshooting, I just put these lines right after the MsgBox statement:

    Resume ExitHere         ' Normally exits during production    Resume                  ' Never will get hereExit Sub

It will never get to the Resume statement, unless you're stepping through and set it as the next line to be executed, either by dragging the next statement pointer to that line, or by pressing CtrlF9 with the cursor on that line.

Here's an article that expands on these concepts: Five tips for handling errors in VBA. Finally, if you're using VBA and haven't discovered Chip Pearson's awesome site yet, he has a page explaining Error Handling In VBA.