4

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 = 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
Dr. belisarius
  • 60,527
  • 15
  • 115
  • 190
strcompnice
  • 208
  • 4
  • 11

4 Answers4

3

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.

JeffK
  • 3,019
  • 2
  • 26
  • 29
  • Thank you for this suggestion. This is plan B if getting the standard run-time error handler isn't possible. – strcompnice Dec 30 '10 at 12:01
  • Edited the answer with another (dangerous) option to **really** invoke the error handling machinery. – JeffK Dec 30 '10 at 15:45
0

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.

Community
  • 1
  • 1
NoAlias
  • 9,218
  • 2
  • 27
  • 46
  • Thank you for the link and the useful code. When using SomePlaceInCodeToHandleErrors when the Err.Number is different from XXXX however, the error is just ignored. – strcompnice Dec 30 '10 at 11:55
0

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.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Thank you for the clarification. The only possiblility then is to change On Error back to GoTo 0 and cause the error again, thus causing the error two times in total, which is not so desirable? – strcompnice Dec 30 '10 at 12:04
  • You could see it like that but I am not sure it is a good idea, and I haven't tried to change the error handling method during the handling of an error. Normally you KNOW the parts of your code which require error handling, and outside of those parts, it is your choice to use "On error Goto 0" to use Excel'error handling. – iDevlop Dec 30 '10 at 14:50
0

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
strcompnice
  • 208
  • 4
  • 11
  • I think this will just terminate the execution of your VBA routine, and Excel will keep running. When I said "dangerous" in my original answer, I was thinking about a stand-along VB6 project, having forgotten that I was answering a VBA question. – JeffK Jan 03 '11 at 20:14