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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Do you have a line label that says:
Code:
ln320:
 
Upvote 0
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!
 
Upvote 0
You need to post more of the code. I suspect you've already raised an error and failed to reset the exception.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
…… 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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top