VBA "On Error" Statement Not Being Recognised

Chris Mack

Well-known Member
Joined
Jun 18, 2013
Messages
803
Hi all

I have the following code:

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

Active Member
Joined
Apr 29, 2010
Messages
292
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

Code:
 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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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,

Rich (BB code):
On Error GoTo Restart
Restart:
If that doesn't work try killing the active error handle.

Rich (BB code):
    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

Well-known Member
Joined
Jun 18, 2013
Messages
803
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. :LOL:

Thanks

Chris
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Well-known Member
Joined
Jun 18, 2013
Messages
803
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

Active Member
Joined
Apr 29, 2010
Messages
292
Thanks Ro, for providing better link for errro handler .....
Hey Chris.. Here you go

Code:
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

Well-known Member
Joined
Jun 18, 2013
Messages
803
Thanks, Rory! :cool:

So I now have the following and everything works perfectly:

Code:
Restart:
    Resume Next
    Restarted = True
Is the best way to use "Resume Next" or "On Error Goto -1"?

Thanks

Chris
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Well-known Member
Joined
Jun 18, 2013
Messages
803
Thanks, good point.

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

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

Thanks again, Rory!

Chris
 

Forum statistics

Threads
1,078,252
Messages
5,339,097
Members
399,277
Latest member
Jyoti C

Some videos you may like

This Week's Hot Topics

Top