CODEWITHSUNDEEP
vbaexcelerror-handling
Devsman
Devsman

Reputation: 498

Why does only one of my VBA error handlers handle errors?

This is kind of an XY problem, but I found it odd in itself.

Here is some code I put together as part of a solution to search for dates:

...
    Dim Short_Date As Date
    Dim Long_Date As Date
    Dim Not_A_Short_Date As Boolean
    Dim Not_A_Long_Date As Boolean
    Not_A_Short_Date = False
    Not_A_Long_Date = False
    On Error GoTo Short_Date_Error
        Short_Date = CDate(Format(Find_What, "Short Date"))
        GoTo Long_Date
Short_Date_Error:
        Not_A_Short_Date = True
Long_Date:
    On Error GoTo Long_Date_Error
        Dim Temp
        Temp = Format(Find_What, "Long Date")
        Long_Date = CDate(Format(Find_What, "Long Date"))
        GoTo Date_Find
Long_Date_Error:
        Not_A_Long_Date = True
Date_Find:
...

I found that the VBA runtime raises an error saying Type mismatch at the Long_Date = CDate(Format(Find_What, "Long Date")) line. Sure enough, Find_What was being passed in something that is not at all a date. Which, as luck would have it, is exactly the error I'm trying to catch.

But when I step through, I found that the runtime jumps from Short_Date = CDate(Format(Find_What, "Short Date")) to Short_Date_Error without giving me the error window, while almost the same code (just with a "Long Date" instead of a "Short Date") with the same value of Find_What just a few lines below results in the error window.

Does anybody know what might cause this?

(Also, if there are any good resources on how to search for dates that would be great. I got the idea for this code from the result of a Google search.)

Upvotes: 0

Views: 109

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

Move your error handling outside of the main body of code. It should go between an Exit Sub and End Sub.

This code will not deal with the Type mismatch error and will never display the "Long Date Error" or "Finding" messages.

Sub Test()

    Dim Short_Date As Date
    Dim Long_Date As Date

    On Error GoTo Short_Date_Error
        Short_Date = 1 / 0 'Overflow
        GoTo Long_Date
Short_Date_Error:
    MsgBox "Short Date Error"
Long_Date:
    On Error GoTo Long_Date_Error
        Long_Date = Int("A") 'Type mismatch
        GoTo Date_Find
Long_Date_Error:
    MsgBox "Long Date Error"
Date_Find:
    MsgBox "Finding"

End Sub

This code on the other hand moves outside the body of code, deals with the error and then resumes: 

Sub Test1()

    Dim Short_Date As Date
    Dim Long_Date As Date

    On Error GoTo Short_Date_Error
        Short_Date = 1 / 0 'Overflow

Long_Date:
    On Error GoTo Long_Date_Error
        Long_Date = Int("A") 'Type mismatch

Date_Find:
    MsgBox "Finding"

Exit Sub
Short_Date_Error:
    MsgBox "Short Date Error"
    Resume Long_Date

Long_Date_Error:
    MsgBox "Long Date Error"
    Resume Date_Find

End Sub

This link seems to have a fair amount regarding error handling.

Upvotes: 3

Related Questions