VBA - goto excel error message in error handling if

Question

I wanted to write the error handling for vba, which after directing to the error handling will first check for a specific error and if there is no match, it will show the error message as according to Excel.

Here is my code:

On Error GoTo ErrorHandl

ErrorHandl:
If Err.Number = 13 Then
  MsgBox "Do that …", vbCritical
  Exit Sub
Else
??????

What could I write in the place of questions marks, so that if error number is different than 13 then, excel will show me the other error that is currently occurring?

You could use a Message Box to show Err.Description.

user6722034
– user6722034

2019-06-12 12:42:00 +00:00
Commented Jun 12, 2019 at 12:42 — user6722034
– user6722034, Commented Jun 12, 2019 at 12:42

Vityata · Accepted Answer · 2019-06-12 13:22:52Z

1

This is a possible way to get custom errors with Select Case:. If the error is number 11 (Division by 0), then it gives a customized MsgBox. Othewise it gives a standard one with the Err.Number and Err.Description, based on the current language of the system (Mine is German):

The code:

Sub TestMe()

    On Error GoTo TestMe_Error

    Debug.Print 5 / 0

    On Error GoTo 0
    Exit Sub

TestMe_Error:

    Select Case Err.Number
    Case 11:
        MsgBox "Division by null is not allowed on this planet!"
    Case Else:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TestMe."
    End Select

End Sub

And if you need a multipurpose Error-Handler, this is the code:

Sub TestMe()

    On Error GoTo TestMe_Error

    Debug.Print 5 / 0

    On Error GoTo 0
    Exit Sub

TestMe_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TestMe."

End Sub

edited Jun 12, 2019 at 13:22

answered Jun 12, 2019 at 13:16

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dozens Over a year ago

I am getting error 0. I am using if loop and else ifs instead of 'Case is'. Then I get the MsgBox as you typed and gives Error(0).

Vityata Over a year ago

@Dozens - error number 0 means no error. Did you copy+paste my solution or you wrote something else? What exactly?

Vityata Over a year ago

The idea of the error handler is to write all your code in the place Debug.Print 5 / 0 of the code above.

Dozens Over a year ago

the thing was that there was missing 'Exit sub' before Erroe handler

Dozens Over a year ago

Yes, Now I see. But I have not seen it at the time.

|

Tim Stack · Accepted Answer · 2019-06-12 12:42:52Z

0

The code you wrote now is error number specific. You could also write a general error handler

Sub test()
On error GoTo Errorhandler

 '''
 'Code
 '''

Exit Sub
Errorhandler:
MSgbox "Warning: fatal error occured." & vbCr & vbCr "Error message:" & vbCr & Err.Description, vbcritical, "Error"
End Sub

This will, regardless of which error occured, display a message box warning the user of a fatal error followed by the error message as displayed in VBE.

Of course, you could also specify the error code instead of making it general

answered Jun 12, 2019 at 12:42

Tim Stack

3,2483 gold badges25 silver badges46 bronze badges

2 Comments

Dozens Over a year ago

The Exit sub before Errorhandler was the bull's eye for me. Thanks

Tim Stack Over a year ago

Glad to have helped!

Collectives™ on Stack Overflow

VBA - goto excel error message in error handling if

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related