CODEWITHSUNDEEP
vbaexcel
logan
logan

Reputation: 8346

VBA OnError Exception Handling not working

I have following sample code in my VBA. Whenever i face system related error, i want to display the my own error message. But the following code is not working.

VBA tells

Type Mismatch

I want

Hello your date is invalid

My Code

Sub good()

Dim date_ As Date
date_ = "hello"

On Error GoTo Err1:

Err1:
    MsgBox "Hello your date is invalid"
    ' more code

End Sub

Upvotes: 1

Views: 994

Answers (2)

Peter Albert
Peter Albert

Reputation: 17475

You need to place the On Error statement before the error!

Also, don't forget the Exit Subat the end, else your routine will always run the error code:

Sub good()
    Dim date_ As Date

    On Error GoTo Err1:
    date_ = "hello"

    On Error Goto 0 'goes back to default, i.e. show debugger
    Exit Sub

Err1:
    MsgBox "Hello your date is invalid"
    ' more code

End Sub

Alternatively, you can do this

Sub good()
    Dim date_ As Date

    On Error Resume Next
    date_ = "hello"
    If Err.Number <> 0 Then
        MsgBox "Wrong type"
        Err.Clear
        Exit Sub
    End If

    On Error Goto 0

    ' more code

End Sub

This approach can be repeated to catch individual errors...

Upvotes: 2

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

You need to put the On Error statement before an error occurs, usually at the very beginning of the procedure. Think of the On Error statement as a directive that tells VBA how to handle any errors that are encountered later in the procedure.

Sub good()

On Error GoTo Err1

Dim date_ As Date
date_ = "hello"

Exit Sub 'Make sure you include this so successful executions 
         'don't continue into the error block.

Err1:
    Select Case Err.Number
      Case 101 'Not the actual error number!
         MsgBox "Hello your date is invalid"
      Case 102 'Not the actual error number!
         MsgBox "Something else bad happened!"
      Case Else
         MsgBox "I don't know what happened - unexpected error!"
    End Select
    ' more code

End Sub

Upvotes: 2

Related Questions