Results 1 to 8 of 8

Thread: Difference Between 'On Error GoTo 0' And 'On Error GoTo -1'

  1. #1
    Member Transformer's Avatar
    Join Date
    Mar 2012
    Posts
    91
    Rep Power
    13

    Lightbulb Difference Between 'On Error GoTo 0' And 'On Error GoTo -1'

    On Error GoTo 0
    It disables the current error handler.So it is used to resume execution normally (without any error handler).

    Code:
    Sub ErrorHandler()
        
        Dim dblValue        As Double
          
        On Error Resume Next
        ThisWorkbook.Worksheets("A").Range("A1") = "Transformer" ' Subscript out of range error will occur if there is no sheet named "A" in the Workbook
        'On Error GoTo 0 'error handler disabled..next error will not be handled.Uncomment the line to check the effect.
        dblValue = 1 / 0 ' Division by Zero error
        MsgBox "Ingnored all the errors."
        
    End Sub
    In above example if one uncomments the line On Error GoTo 0 then it will not handle the error that comes after this statement.

    On Error GoTo -1
    When an error occurs within a procedure, an exception object is created. On Error GoTo -1 is used to set this exception object to nothing.
    If it is not done then next error handler statement (e.g. On Error GoTo errHandler / On Error Resume Next ...) will not work.

    To check the effect of not using it comment the line 'On error GoTo -1' in the snippet given below.

    Code:
    Sub ErrorTest()
        
        Dim dblValue        As Double
          
        On Error GoTo ErrHandler1
        dblValue = 1 / 0
    ErrHandler1:
        MsgBox "Exception Caught"
        On Error GoTo -1           'Comment this line to check the effect
        On Error GoTo ErrHandler2
        dblValue = 1 / 0
    ErrHandler2:
        MsgBox "Again caught it."
            
    End Sub
    But if you use Resume Next as an error handler then an exception object is not created.

    Code:
    Sub ErrorTest()
        
        Dim dblValue        As Double
          
        On Error Resume Next
        dblValue = 1 / 0
        MsgBox "Exception Caught"
        On Error GoTo ErrHandler
        dblValue = 1 / 0
        'No need to set exception object to nothing as it has not been created
    ErrHandler:
        MsgBox "Again caught it."
            
    End Sub
    More examples to understand the same.


    Code:
    Sub ErrorTestUserHandler()
        
        Dim dblValue        As Double
       
            For lngloop = 1 To 10
                On Error GoTo ErrHandler
                dblValue = 1 / 0
    ErrHandler:
                MsgBox "Caught it."
                On Error GoTo -1 'Comment this line and run it again to see the effect
            Next
            
    End Sub

    Code:
    Sub ErrorTestResumeNext()
        
        Dim dblValue        As Double
       
            For lngloop = 1 To 10
                On Error Resume Next
                dblValue = 1 / 0
                MsgBox "Caught it."
                'No need to set exception object to nothing as it has not been created
            Next
            
    End Sub
    Last edited by Transformer; 04-14-2013 at 12:02 PM.
    Regards,

    Transformer

  2. #2
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Nice explanation buddy. Really helps us to understand why and how to use error handling in VBA.

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Last edited by DocAElstein; 04-24-2024 at 07:29 PM.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    To the point explanation. Before this i also don't even know the use of on error goto -1 and now my confusion is clear. thanks excelfox and transformer.
    Last edited by LalitPandey87; 04-11-2013 at 08:55 PM.

  5. #5
    Junior Member
    Join Date
    May 2014
    Posts
    3
    Rep Power
    0
    Finally I found an explanation for GoTo 0 that I could understand.
    The GoTo -1 and the no exception object with Resume Next made it a 10 out of 10 post.
    Thanks so much for posting this!!!

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    Quote Originally Posted by skywriter View Post
    .... the no exception object with Resume Next made it a 10 out of 10 .....
    ... Agreed. That jem of info was the bit missing by me...now I see ( I think ) why despite an error "occuring", ( andbeing "Handeled by the Resume Next ) I can follow it by enabling another error handler without resetting the exception-- in the case of Resume Next I see now there is no acception raised. It had been explained to me as "surpressing the errror", which I read as something else.. ( not quite sure what - I was puzzeled ) - now I understand ( I think! )

    Thanks for Sharing

    Alan

  7. #7
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    .
    . Hi,
    . A Couple of follow up questions.

    . 1 ) Would you describe On Error GoTo 0 and On Error GoTo -1 as error statements,
    And
    . 2 ) On Error Resume Next and On Error GoTo some_label/line_number as error handlers.
    . Thus we have in VBA 2 types of “error handlers”. I ask as the literature is often confusing, ( possibly caused partly by the GoTo 0 and GoTo -1 which do not actually “go anywhere” I believe ).
    . One is often mislead in thinking in terms of 4 “error handlers”

    Thanks
    Alan
    Last edited by DocAElstein; 05-27-2015 at 08:55 PM. Reason: Editor lost some spaces between words!!??

  8. #8
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    P.s. Just for info: Similar Blog is here
    On Error WTF? | Excel Matters

    Edit April 2018
    Also see here:
    http://www.excelfox.com/forum/showth...GoRoT-N0Nula-1
    Last edited by DocAElstein; 05-07-2018 at 02:06 PM.

Similar Threads

  1. Get External Data Error
    By marreco in forum Excel Help
    Replies: 2
    Last Post: 01-05-2013, 08:20 PM
  2. #Value Error in Working File
    By Suhail in forum Excel Help
    Replies: 2
    Last Post: 11-19-2012, 11:39 PM
  3. Swapping columns Error 2
    By jomili in forum Excel Help
    Replies: 1
    Last Post: 11-16-2012, 08:52 PM
  4. Excel Error
    By aarbuckle in forum Excel Help
    Replies: 5
    Last Post: 03-13-2012, 03:12 AM
  5. On Error Statement (Visual Basic)
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 10-12-2011, 09:06 AM

Posting Permissions

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