Funny behaviour when trying to check for a range's name

[bernatbosch]

Hi everybody!


Let's see if someone can help me understand this:


I have a table object in my worksheet (or many, but let's assume I have one, to make it easy).
The user will be able to add one or two elements above the table, which are meant to work together with the table's data, in a pair of ranges that have to be called 'table statistics' and 'table fields', respectively. Therefore, placed above the table, the user can choose to have one of these ranges, both ranges, or have just the table alone.
When the range called 'table statistics' is created (by a call of the user) the same procedure incorporates a cell at the top in which a title is added: "STATS". This cell is given a name that may help users to know what are the sheet and the table to which the range actually refers to (something like: 'Sheet1Table1StatsTitle').
A similar thing happens when the user creates the range called 'table fields': also a cell is being named (something like: 'Sheet1Table1FieldsTitle').
In case the 'statistics' range for this particular table does exist, the cell corresponding to StatsTitle name is always meant to be placed 20 rows above the table object.
In case the 'fields' range for this particular table does exist, the cell corresponding to FieldsTitle name is always meant to be placed 9 rows above the table object.


In order to apply certain procedure, one a table object is selected, I need to check whether the selected table has one of these ranges, both ranges, or the table is alone.
To do so, I try to check whether the cell placed 9 rows above the table object (first table column) has a name or not. If it has, I may consider the table has the fields range. If not, I may consider the table has not the field's range.
Also, I try to check whether the cell placed 20 rows above the table object (first table column) has a name or not. If it has, I may consider the table has the stats range. If not, I may consider the table has not the stats range.
Simple!


To check for the existance of these two names (named ranges's names) I use the following syntax:


If FieldsTitleRng.Name.Name <> vbNullString Then


and


If StatsTitleRng.Name.Name <> vbNullString Then


If the table being checked has not its fields range (fields range does not exist) the code returns an error, so I tell the procedure to go to certain line when this error occurs and there to note that the table has no fields range.


Untill this moment everyting works just fine.


BUT, once I've checked whether the selected table has fields range or not, when I try to check whether the selected table has stats range or not (by using the same procedure) in the case that in my first checking I got an error (so that fields range did not exist for the selected table) I get a new error that I'm not able to catch.


The result is that, under these circumstances (when the selected table has no names associated with it) in the checking for the existance of the second named range, I get the following message:


Run-time error '1004':
Method 'name' of object 'range' failed.


CAN ANYONE TELL ME WHY THE FIRST TIME I CHECK FOR A NAMED RANGE EXISTANCE USING THIS SYNTAX I CAN CATCH THE ERROR PRODUCED BY THE NON EXISTANCE OF THE NAME, BUT THE SAME SYNTAX DOES NOT ALLOW ME TO CATCH THE SAME ERROR FOR THE SECOND TIME?


Here is my code (neither FieldsTitleRng and StatsTitleRng have a name):


'check fields


On Error GoTo SelectedTableHasNoFields


Set FieldsTitleRng = TableObject.Range.Cells(1, 1).Offset(-9, 0).Resize(1, 1)

If FieldsTitleRng.Name.Name <> vbNullString Then 'Line that works fine

SelectedTableHasFields:
TableHasFields = True
Else
SelectedTableHasNoFields:
Err.Clear
TableHasFields = False
End If



'check stats


On Error GoTo SelectedTableHasNoStats


Set StatsTitleRng = TableObject.Range.Cells(1, 1).Offset(-20, 0).Resize(1, 1)


If StatsTitleRng.Name.Name <> vbNullString Then 'Line I get a Run-time error '1004': Method 'name' of object 'range' failed.
SelectedTableHasStats:
TableHasStats = True
Else
SelectedTableHasNoStats:
Err.Clear
TableHasStats = False
End If

 

[RoryA]

Because you don't have a Resume statement or other method to clear the current exception. (see here: On Error WTF? | Excel Matters)

I would suggest you simply use a local error handler:

Dim sName as string
On Error Resume Next
sName = FieldsTitleRng.Name.Name
On Error Goto 0
If sName <> vbNullString Then

for example.

 

[Andrew Poulsom]

I expect that the first error hander is still active when the second error occurs, so the second error is not trapped by the On Error statement. Here's a good write up on error handling:

Error Handling In VBA

 

[bernatbosch]

Grate! Your suggestion works perfectly! Thank you so much.

I really need to improve my knowledge on error handling…

This is how it looks now:

'check fields

Set FieldsTitleRng = TableObject.Range.Cells(1, 1).Offset(-9, 0).Resize(1, 1)

On Error Resume Next
sName = FieldsTitleRng.Name.Name
On Error GoTo 0

If sName <> vbNullString Then
TableHasFields = True
Else
TableHasFields = False
End If


'check stats

Set StatsTitleRng = TableObject.Range.Cells(1, 1).Offset(-20, 0).Resize(1, 1)

On Error Resume Next
sName = StatsTitleRng.Name.Name
On Error GoTo 0

If sName <> vbNullString Then
TableHasStats = True
Else
TableHasStats = False
End If

 

[bernatbosch]

Thank you very much for your answer!
Everything seems fairly simple once you got to know it!
But I've spent one hour trying to find out for my own without understanding it.

Thanks.

 

[RoryA]

You should use a separate variable for each test, or reset it after the first test. Otherwise if the first test passes, the variable will still have that name in it after the second error occurs.

 

[bernatbosch]

You mean not to use twice the variable sName?

Or reset like sName = vbNullString?

 

[RoryA]

Either of those will work.