VBA: Displaying the standard run-time error handler

I have a question about the correct way to handle errors in VBA in Excel. If a specific error, say xxxxxxx, occurs, then a MsgBox should be displayed. If another error occurs the standard run-time error handler should pop up. How can this be accomplished? Here is the example code:

On Error Resume Next

'Line of code that causes an error here.

If Err.Number = xxxxxxx Then

    MsgBox "Specific error message."

ElseIf Err.Number =开发者_StackOverflow社区 0 Then

    Do nothing

Else 'Some error other than xxxxxxx.

    'This is the problem. Here I would like to display standard run-time error
    'handler without causing the error again.

End If

On Error GoTo 0

You can get a message box that looks very much like the standard error message by putting this into your "Else" block:

MsgBox "Run-time error '" & Err.Number & "':" & _
    vbNewLine & vbNewLine & _
    Error(Err.Number), vbExclamation + vbOKOnly, _
    "YourProjectNameHere"

But this is just a facsimile. It's not the actual error message dialog that VB6 puts up; it's just formatted to look like it. Error handling is still disabled by the "On Error Resume Next" statement at this point.

But if you really, really want to invoke the standard error handling code, you can put this in the "Else" block:

Dim SaveError As Long
SaveError = Err.Number
On Error Goto 0
Error (SaveError)

This code saves the error number, re-enables error handling, and then re-raises the error. You invoke the VB runtime's true error handling machinery this way. But beware: if this error isn't caught with an active error handler somewhere higher in the call chain, it will terminate your program after the user clicks on the "OK" button.

Note that you'll also lose the ability to get the actual line number where the error occurs using" Erl" in that error handler because you are re-generating the runtime error with the "Error (SaveError)" statement. But that probably won't matter because most VB code doesn't actually use any line numbers, so Erl just returns 0 anyway.

Replace On Error Resume Next with

On Error Goto SomePlaceInCodeToHandleErrors

SomePlaceInCodeToHandleErrors:
If Err.Number = XXXX Then
MSGBOX "Message"
End If

Check out this Stack Overflow thread for some more information and example code.

In your VBA options, select "Break on unhandled errors".
To enable handling use on error goto SomeLabel or on error resume next.
To stop error handling use on error goto 0.

Your question is contradictory in this context. If you enable error handling, well, you disable the standard error handling.

As DaMartyr suggests, you can still use something like msgbox err.description.

So to follow up on JeffK's new suggestion the code below seems to work fine with VBA, and what's more I can't see the danger in using it. To terminate Excel is critical as that could lose a lot of work, but since the code always checks that the error is there how could that happen?

Thank you JeffK for this intriguing idea.

Dim savedNumber As Long

On Error Resume Next

'Line of code that causes an error.

If Err.Number = XXXXXXX Then

    'Specific error message.

ElseIf Err.Number <> 0 Then

    savedNumber = Err.Number

    On Error GoTo 0

    Error savedNumber

End If

Err.Clear

On Error GoTo 0