Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA "On Error" Statement Not Being Recognised

  1. #1
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA "On Error" Statement Not Being Recognised

    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

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Location
    New Delhi, India
    Posts
    292
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    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
    Thanks

    Purushottam
    Excel 2010 , Windows 7

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,314
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    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,

    Code:
    On Error GoTo Restart
    Restart:
    If that doesn't work try killing the active error handle.

    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

  4. #4
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    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

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    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.

  6. #6
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    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

  7. #7
    Board Regular
    Join Date
    Apr 2010
    Location
    New Delhi, India
    Posts
    292
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    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
    Thanks

    Purushottam
    Excel 2010 , Windows 7

  8. #8
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    Thanks, Rory!

    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

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    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.

  10. #10
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    803
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA "On Error" Statement Not Being Recognised

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •