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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
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
19,211
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
35,177
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
35,177
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,855
Messages
5,489,297
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top