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

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

  1. #1
    Board Regular bernatbosch's Avatar
    Join Date
    Dec 2015
    Location
    Barcelona - CATALONIA
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  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: Funny behaviour when trying to check for a range's name

    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:

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

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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
    Microsoft MVP - Excel

  4. #4
    Board Regular bernatbosch's Avatar
    Join Date
    Dec 2015
    Location
    Barcelona - CATALONIA
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  5. #5
    Board Regular bernatbosch's Avatar
    Join Date
    Dec 2015
    Location
    Barcelona - CATALONIA
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    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: Funny behaviour when trying to check for a range's name

    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.

  7. #7
    Board Regular bernatbosch's Avatar
    Join Date
    Dec 2015
    Location
    Barcelona - CATALONIA
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    You mean not to use twice the variable sName?

    Or reset like
    sName = vbNullString?

  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: Funny behaviour when trying to check for a range's name

    Either of those will work.

Some videos you may like

User Tag List

Tags for this Thread

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
  •