Issue with Error Handler

excelpunk

Board Regular
Joined
May 6, 2011
Messages
165
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?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Do you have a line label that says:
Code:
ln320:
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,940
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!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,178
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You need to post more of the code. I suspect you've already raised an error and failed to reset the exception.
 

excelpunk

Board Regular
Joined
May 6, 2011
Messages
165
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
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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:
[COLOR=blue]For[/COLOR] irow = 7 [COLOR=blue]To[/COLOR] toc_lastrow
sname = Range("B" & (irow - 1)).Value
[COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] ln320
Worksheet(sname).Select
[COLOR=darkgreen]'--- -- more code ---------[/COLOR]
ln320:
[COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] -1
[COLOR=blue]Next[/COLOR] 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-questions/847379-error-handler-not-activating.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:

RoryA

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

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
…… 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:
[color=blue]If[/color] [color=blue]Not[/color] Evaluate("=ISREF('" & sname & "'!A1)") Then [color=darkgreen]'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[/color]
          
            Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "" & sname & "" [color=darkgreen]'Make it as that after the last sheet[/color]
            [color=blue]Else[/color]
            Worksheets("" & sname & "").Move after:=Worksheets(Worksheets.Count) [color=darkgreen]'Otherwise If the sheet is there it could be anywhere so we put it after last sheet[/color]
            [color=blue]End[/color] [color=blue]If[/color]
…..


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
 

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