Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Issue with Error Handler

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Issue with Error Handler

    I have a piece of code:

    On Error GoTo ln320
    Worksheets(sName).Select
    There are cases where sName does not exists, hence I used error handler. However, the macro does not seems to go to line 320 when the error occurs (when the non existent sheet is asked to select) and the macro breaks with subscription out of range error. How do we fix this?

  2. #2
    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: Issue with Error Handler

    Do you have a line label that says:
    Code:
    ln320:

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with Error Handler

    Yes! Good question though!

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,179
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Issue with Error Handler

    Have a look at the setting for "Error Trapping" in the General tab of Options under the Tools menu. It should be set to "Break on Unhandled Errors"...

    Code:
    Tools > Options > General > Error Trapping
    Hope this helps!

  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: Issue with Error Handler

    You need to post more of the code. I suspect you've already raised an error and failed to reset the exception.

  6. #6
    Board Regular
    Join Date
    May 2011
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with Error Handler

    I think you are right. How do we reset the exception? The code has many lines, I will post the relevant part:

    For irow = 7 To toc_lastrow
    sName = Range("B" & (irow - 1)).Value
    On error goto ln320
    worksheet(sname).select
    --- -- more code ---------
    ln320:
    Next irow

  7. #7
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with Error Handler

    Quote Originally Posted by excelpunk View Post
    I think you are right. How do we reset the exception? The code has many lines, I will post the relevant part:

    .. Usually in similar looping codes of this form that I have this works to overcome this problem.....

    …………………………………………………………

    Code:
    For irow = 7 To toc_lastrow
    sname = Range("B" & (irow - 1)).Value
    On Error GoTo ln320
    Worksheet(sname).Select
    '--- -- more code ---------
    ln320:
    On Error GoTo -1
    Next irow

    ……..Brief explanation

    … As others have indicated.. VBA is ( after your error occurs ) in it’s “Exceptional - state of being” modus “ … - where it “thinks” an error is being handled.

    This happened because…. the On Error you have ….. had the effect of “enabling” your error handler. – (Your error handler is the On Error Goto ln320, along with the subsequent actions, if any at that ln320 which effect the error state… ),

    . This error handler was subsequently activated when the error occurred. It began doing what it should.. In this case going to ln320. VBA now won’t respond to other errors ( in your case the next time an error occurs in the loop ) as VBA ‘aint expecting them. It gets confused if they do come up, “freaks out” and reverts back to it’s “normal” state giving a “normal” default type error…

    ……
    . This line …….…

    On Error Goto -1

    . …. don’t actually “go anywhere..” ( It is just a weird syntax ). This is just a code line ( error statement ) that resets this exceptional state ( In the computer jargon, - “clears the exception”) - ( at the same time as this it deactivates your error handler ( but does not disable it ) and so it can be used again in the loop. Then when the next error occurs, the process starts again…

    . That is all a bit much to take in maybe…, see here for a good explanation
    On Error WTF? | Excel Matters
    http://www.mrexcel.com/forum/excel-q...tivating.html?
    ( And here for one in “my language…..” if you feel so inclined…
    https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo
    .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.Clear [SOLVED]

    …. )

    Alan
    Bavaria
    Last edited by DocAElstein; May 9th, 2015 at 02:21 AM.

  8. #8
    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: Issue with Error Handler

    It is almost never necessary to use on error goto -1. I would use a generic function to test for the existence of the worksheet so you don't need to handle errors inside the loop.

  9. #9
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue with Error Handler

    Quote Originally Posted by RoryA View Post
    …… I would use a generic function to test for the existence of the worksheet so you don't need to handle errors inside the loop.
    Like this for example??
    … this I use / stole sometime from someone better than me at VBA (That is just about everyone these days..) … I use it sometimes,

    Code:
    If Not Evaluate("=ISREF('" & sname & "'!A1)") Then 'Check to see if the sheet is there by seeing if the reference to cell A1 in that sheet doesn#t exist. If it is true that it does not exist, then
              
                Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "" & sname & "" 'Make it as that after the last sheet
                Else
                Worksheets("" & sname & "").Move after:=Worksheets(Worksheets.Count) 'Otherwise If the sheet is there it could be anywhere so we put it after last sheet
                End If
    …..


    Quote Originally Posted by RoryA View Post
    It is almost never necessary to use on error goto -1……..
    … I am using on error goto -1 and other error handlers a lot currently, … so it proves wot Rory said…

    ….. as I am ..Nuts

    Alan

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
  •