VBA "On Error" Statement Not Being Recognised

[Chris Mack]

Hi all

I have the following code:

Private Sub Workbook_Open()
    Dim InitialMonth As String, Restarted As Boolean
    Restarted = False
    InitialMonth = Format(Date - 1, "mmmm yyyy")
    GoTo Start
Restart:
    Restarted = True
Start:
    If Restarted = True Then
        AlternateMonth = Application.InputBox("""" & AlternateMonth & """ is not a valid entry." & vbNewLine & vbNewLine & _
            "I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
            "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
    Else
        AlternateMonth = Application.InputBox("I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
            "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
    End If
    On Error GoTo Restart
    If AlternateMonth = False Then
        MsgBox "Cancelled"
'        ActiveWorkbook.Save
'        Application.Quit
    ElseIf AlternateMonth = "" Then
        FirstOfMonthToProcess = DateValue("1 " & InitialMonth)
    ElseIf DateValue("1-" & AlternateMonth) < DateValue("1-Apr-15") Then
        GoTo Restart:
    Else
        FirstOfMonthToProcess = DateValue("1-" & AlternateMonth)
    End If
'    Sheets("Trust").[A15] = MonthToProcess
End Sub

If I type in a random text string into the input box, the error handler works fine the first time around ("On Error GoTo Restart"). However, if I type the same string again, I get a run time error when VBA attempts to evaluate the line "ElseIf DateValue("1-" & AlternateMonth) < DateValue("1-Apr-15") Then".

It definitely passes the error handling line after each iteration of an InputBox, so what is the problem here?

Many thanks

Chris

 

[puru.sve]

I got confused also .. I am also getting same error but why I do not know .. But I have a solution if you can replace

 On Error goto Restart with On Error Resume Next

then its working...

I know its not best way but it can be used for now

 

[jasonb75]

I'm sure that brain surgery and rocket science are less complicated than error handling.

You might be able to fix the problem by moving the error trap outside of the loop,

On Error GoTo Restart
Restart:

If that doesn't work try killing the active error handle.

    If Restarted = True Then
        AlternateMonth = Application.InputBox("""" & AlternateMonth & """ is not a valid entry." & vbNewLine & vbNewLine & _
            "I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
            "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
        On Error GoTo 0
        restarted = False
    Else

 

[Chris Mack]

Yes, it seems quiet odd, I hope someone can explain it.

Thanks for your workaround, it works perfectly, it seems that when "On Error Resume Next" is applied, everything that throws an error is true! Is guess we could refer to it as a "Boolean Relativist" or something.

Thanks

Chris

 

[RoryA]

You're missing a Resume statement - Goto restart doesn't clear the active exception and any further errors will be unhandled. This page may help to explain.

 

[Chris Mack]

Jason, I tried your second solution and still got the same error.

I don't think I can implement to first one because I need to go backwards in the macro.

Thanks

Chris

 

[puru.sve]

Thanks Ro, for providing better link for errro handler .....
Hey Chris.. Here you go

Private Sub Workbook_Open()    Dim InitialMonth As String, Restarted As Boolean
    Restarted = False
    InitialMonth = Format(Date - 1, "mmmm yyyy")
    On Error GoTo Restart
    GoTo Start
Restart:
    On Error GoTo -1
    
    Restarted = True
Start:
    If Restarted = True Then
        AlternateMonth = Application.InputBox("""" & AlternateMonth & """ is not a valid entry." & vbNewLine & vbNewLine & _
            "I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
            "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
    Else
        AlternateMonth = Application.InputBox("I am about to process " & InitialMonth & "." & vbNewLine & vbNewLine & _
            "To process a different month, please enter it here in the format ""mmm-yy"" (must be later than Apr-15) (otherwise, click OK to continue):")
    End If
    
    If AlternateMonth = False Then
        MsgBox "Cancelled"
'        ActiveWorkbook.Save
'        Application.Quit
    ElseIf AlternateMonth = "" Then
        FirstOfMonthToProcess = DateValue("1 " & InitialMonth)
    ElseIf DateValue("1-" & AlternateMonth) < DateValue("1-Apr-15") Then
        GoTo Restart:
    Else
        FirstOfMonthToProcess = DateValue("1-" & AlternateMonth)
    End If
'    Sheets("Trust").[A15] = MonthToProcess
End Sub

 

[Chris Mack]

Thanks, Rory!

So I now have the following and everything works perfectly:

Restart:
    Resume Next
    Restarted = True

Is the best way to use "Resume Next" or "On Error Goto -1"?

Thanks

Chris

 

[RoryA]

If you need On Error Goto -1, you need to rethink your code.

It appears here that you just need some date validation using IsDate rather than an error handler really.

 

[Chris Mack]

Thanks, good point.

So now I have the following with no need for error handling:

ElseIf IsDate("1-" & AlternateMonth) = False Then
    GoTo Restart
ElseIf DateValue("1-" & AlternateMonth) < DateValue("1-Apr-15") Then
    GoTo Restart

Nice!

Thanks again, Rory!

Chris