Irratic error handling, what is the problem?

[328mike]

Hey all,

I am having my error handler work only sometimes and I can't figure out why. What is causing the inconsistency?

Dim DataMonth
Dim DataMonthFormatted
Dim ETCMonth

DataMonth = InputBox("Please enter a data month. (Mm/01/Yyyy)")

'Processes date information again if it is entered incorrectly
Static ErrCount As Integer
On Error GoTo ErrHandler

...more code...

Exit Sub
ErrHandler:
ErrCount = ErrCount + 1
DataMonth = Application.InputBox _
("Please enter the closest date that resembles " & _
DataMonth & _
", in format (Mm/01/Yyyy)", "Invalid Date Format!")
If ErrCount = 4 Then
MsgBox ("I've just picked up a fault in the AE35 unit. It's going to go 100% failure in 72 hours Dave. ")
DataMonth = Application.InputBox _
("Please enter the closest date that resembles " & _
DataMonth & _
", in format (Mm/01/Yyyy)", "Invalid Date Format!")
ElseIf ErrCount = 9 Then
MsgBox ("Just what do you think you're doing, Dave? ")
DataMonth = Application.InputBox _
("Please enter the closest date that resembles " & _
DataMonth & _
", in format (Mm/01/Yyyy)", "Invalid Date Format!")
Else
Resume
End If
Resume

End Sub

 

[shg]

"Doesn't work" in what way?

An error handler can't handle its own errors; the code will break if there are errors.

EDIT: You've declared ErrCount as static, so it retains its value between executions. Is that what you mean?

 

[328mike]

The if statement in the error handler isn't working. I can get the prompt to appear indefinitely with a type mismatch error but it won't display the custom messages.

 

[shg]

Works fine for me after the VBE is reset to zero the ErrCount variable. Try changing Static to Dim.

 

[328mike]

When I remove static I get a compiler error (statement invalid outside of type block)

 

[328mike]

Thanks SHG that fixed it, I'm having kind of a brain fart this morning!

 

[328mike]

Added some complexity and am getting a new problem now...

When I run my code I get error 20 when I enter a correct answer. I am not sure why it's not working but I assume it is because I have multiple exit sub commands. Any advice here?

Exit Sub
ErrHandler:
Dim AnswerErr As Integer
ErrCount = ErrCount + 1
DataMonth = Application.InputBox _
("Please enter the closest date that resembles " & _
DataMonth & _
", in format (Mm/01/Yyyy)", "Invalid Date Format!")

'Easter Egg
If ErrCount = 4 Then
MsgBox "I've just picked up a fault in the AE35 unit. It's going to go 100% failure in 72 hours Dave. ", vbCritical, "HAL"
DataMonth = Application.InputBox _
("Please enter the closest date that resembles " & _
DataMonth & _
", in format (Mm/01/Yyyy)", "Invalid Date Format!")
If DataMonth = False Then
AnswerErr = MsgBox("Are you sure you don't want to enter a data month? LinkUp Eligibility will not be calculated!", vbYesNo, "Cancel Clicked")
If AnswerErr = 6 Then
DataMonth = ""
Exit Sub
Else
GoTo ErrHandler
End If
Else
GoTo ErrHandler
End If
ElseIf ErrCount = 9 Then
MsgBox "Just what do you think you're doing, Dave? ", vbCritical, "HAL"
DataMonth = Application.InputBox _
("Please enter the closest date that resembles " & _
DataMonth & _
", in format (Mm/01/Yyyy)", "Invalid Date Format!")
If DataMonth = False Then
Answer = MsgBox("Are you sure you don't want to enter a data month? LinkUp Eligibility will not be calculated!", vbYesNo, "Cancel Clicked")
If Answer = 6 Then
DataMonth = ""
Exit Sub
Else
GoTo ErrHandler
End If
Else
On Error GoTo ErrHandler
End If
ElseIf ErrCount = 14 Then
MsgBox "Cake, and grief counseling, will be available at the conclusion of the test. ", vbExclamation, "GLaDOS"
DataMonth = Application.InputBox _
("Please enter the closest date that resembles " & _
DataMonth & _
", in format (Mm/01/Yyyy)", "Invalid Date Format!")
If DataMonth = False Then
Answer = MsgBox("Are you sure you don't want to enter a data month? LinkUp Eligibility will not be calculated!", vbYesNo, "Cancel Clicked")
If Answer = 6 Then
DataMonth = ""
Exit Sub
Else
GoTo ErrHandler
End If
Else
On Error GoTo ErrHandler
End If
ElseIf DataMonth = False Then
Answer = MsgBox("Are you sure you don't want to enter a data month? LinkUp Eligibility will not be calculated!", vbYesNo, "Cancel Clicked")
If Answer = 6 Then
DataMonth = ""
Exit Sub
Else
GoTo ErrHandler
End If
End If
Resume
End Sub

 

[shg]

Have you tried setting a breakpoint and stepping through the code?

 

[328mike]

I haven't.

I'm thinking that when my code hits a confirmation box to terminate the error handling I am not letting the 'no' option run to the end of the error handler, thus making that Resume not included in the process and triggering the error code. I will filter out all of the GoTo commands and try it again.

 

[328mike]

Ok I've figured out the problem. Apparently when you click 'No' on a yes / no box and send from no to error handler it won't work because it doesn't recognize an error, I made a fix for it but I wish their was another way to do it because what I have done seems inefficient and incorrect.

'Begin Error Handling
Exit Sub
ErrHandler:
ErrCount = ErrCount + 1
DataMonth = Application.InputBox _
        ("Please enter the closest date that resembles " & _
        DataMonth & _
        ", in format (Mm/01/Yyyy)", "Invalid Date Format!")

'Easter Egg
If ErrCount = 4 Then
MsgBox "I've just picked up a fault in the AE35 unit. It's going to go 100% failure in 72 hours Dave. ", vbCritical, "HAL"
DataMonth = Application.InputBox _
        ("Please enter the closest date that resembles " & _
        DataMonth & _
        ", in format (Mm/01/Yyyy)", "Invalid Date Format!")
        If DataMonth = False Then
            AnswerErr = MsgBox("Are you sure you don't want to enter a data month? LinkUp Eligibility will not be calculated!", vbYesNo, "Cancel Clicked")
                If Answer = 6 Then
                    DataMonth = ""
                    GoTo Terminate
                Else
                DataMonth = "ThisWillTriggerErrHandler"
                End If
        End If
ElseIf ErrCount = 9 Then
MsgBox "Just what do you think you're doing, Dave? ", vbCritical, "HAL"
DataMonth = Application.InputBox _
        ("Please enter the closest date that resembles " & _
        DataMonth & _
        ", in format (Mm/01/Yyyy)", "Invalid Date Format!")
        If DataMonth = False Then
            Answer = MsgBox("Are you sure you don't want to enter a data month? LinkUp Eligibility will not be calculated!", vbYesNo, "Cancel Clicked")
                If Answer = 6 Then
                    DataMonth = ""
                    GoTo Terminate
                Else
                DataMonth = "ThisWillTriggerErrHandler"
                End If
        End If
ElseIf ErrCount = 14 Then
MsgBox "Cake, and grief counseling, will be available at the conclusion of the test. ", vbExclamation, "GLaDOS"
DataMonth = Application.InputBox _
        ("Please enter the closest date that resembles " & _
        DataMonth & _
        ", in format (Mm/01/Yyyy)", "Invalid Date Format!")
        If DataMonth = False Then
            Answer = MsgBox("Are you sure you don't want to enter a data month? LinkUp Eligibility will not be calculated!", vbYesNo, "Cancel Clicked")
                If Answer = 6 Then
                    DataMonth = ""
                    GoTo Terminate
                 Else
                 DataMonth = "ThisWillTriggerErrHandler"
                End If
        End If
ElseIf DataMonth = False Then
Answer = MsgBox("Are you sure you don't want to enter a data month? LinkUp Eligibility will not be calculated!", vbYesNo, "Cancel Clicked")
    If Answer = 6 Then
        DataMonth = ""
        GoTo Terminate
        Else
        DataMonth = "ThisWillTriggerErrHandler"
    End If
End If
Resume
End Sub