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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

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
10,019
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
35,177
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
35,177
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,858
Messages
5,489,304
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top