Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Excel 2003

    I am writing code in the Private Sub Worksheet_Change(ByVal Target As Range) routine for a specific worksheet.

    All is going well except that in the line:

    If ActiveCell.Name.Name = "JFA.Pending" Then

    I get an error if the Active Cell does not have a name at all

    Run-time error '1004': Application-defined or Object-defned error

    The line works correctly if the Active Cell does have a name, whether or not it is the one being tested for.

    What is the correct syntax to test for the existence of a name associated with a specific cell (before I execute the If statement), please ?

    Thanks

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    I don't know if there is a better way but this code will handle the problem:
    Code:
    Option Explicit
    
    Sub Test()
    
    On Error GoTo NoName
    If ActiveCell.Name.Name = "JFA.Pending" Then
      MsgBox "This is the one", vbOKOnly, "Found Cell"
    End If
    NoName:
    On Error GoTo 0   'turn off error trapping
    
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks - and I have invented an equally clunky workaround

    But surely there's a "proper" test for whether a cell has name ?

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    OK, now I am beginning to understand. Since a cell can be part of multiple ranges, I can only really be sure if it has a name or not by cycling through all the names to see if the Active Cell is included. And I've written a simple routine to test that way, which works.

    Or I thought I was understanding . . . rather like your example this works perfectly well (at least for the purpose of the test I am looking for):

    Code:
    On Error Resume Next
    If ActiveCell.Name Is Nothing Then [whatever action I want to take]
    On Error GoTo 0
    I am curious how this can both work and yet give an error if error trapping isn't turned off. It is perhaps because if the Active Cell were part of two named ranges then the result could be misleading ?

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by MartinM View Post
    Code:
    On Error Resume Next
    If ActiveCell.Name Is Nothing Then [whatever action I want to take]
    On Error GoTo 0
    Martin,

    It's interesting that if you step through the code above with the cursor on a cell w/o a name it will show the error as the value for ActiveCell.Name. If the cell does have a name it will return Sheet1!$A$1 or what ever the sheetname!celladdress is. I tried to capture the screen showing this but every time I tried as soon as I touched the Ctrl key the popup showing the value of ActiveCell.Name disappeared. Of course with the Resume Next in play the next statement is the Then clause so it works.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yes, I saw that when trying to work out what cell.name returned when there isn't a name - but I'm resigned to:

    1. All it returns is an error, not any sort of value. I was hoping for Null, Nothing, False, zero or something else useful.

    2. Unsurprisingly I suppose, the whole cell and range naming system is essentially "one-way" - given a name you can identify cells, but only by search methods can you reverse this process.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Sorry, I just couldn't leave this alone!

    Here's a function that makes the process cleaner and test code to show how to use it in your code. It probably isn't more efficient than the Resume Next method but it is cleaner.

    Code:
    Option Explicit
    
    Function bCellInRange(zTest As String, zFind As String) As Boolean
    
    'Returns: True if the zFind cell is within the zTest range
    '         False if the zFind cell is NOT found within the zTest range
    
       Dim zTestRange() As String
       Dim lRngCnt      As Long
       Dim rng          As Range
       
       lRngCnt = Range(zTest).Count
       ReDim zTestRange(lRngCnt)
    
       For Each rng In Range(zTest)
          If rng.Address(RowAbsolute:=False, ColumnAbsolute:=False, _
                         ReferenceStyle:=xlA1) = zFind Then
            bCellInRange = True
            Exit For
          End If
       Next rng
    
    End Function         'bCellInRange
    
    Sub Test()
    
    'Example of calling the bCellInRange Function
    
       Dim bRet As Boolean
    
       bRet = bCellInRange("JFA.Pending", ActiveCell.Address(RowAbsolute:=False, _
                                          ColumnAbsolute:=False, ReferenceStyle:=xlA1))
       Debug.Print bRet
       
    End Sub
    BTW: I tested this with both single and multiple cell zTest ranges and it will always work!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Many thanks for that, from one retired geek to another. I coudn't put this problem down either, even though I had my own workaround and some better alternatives from this thread.

    I have eventually solved the central conundrum - to avoid the error resulting from a simple query when the cell interrogated has no name associated with it, you have to use a double negative, that is to say an If Not . . . . Is Nothing syntax.

    Although I have not tested it exhaustively, this also seems to work every time:

    Code:
    If Not Intersect(Target, Range("MyRange")) Is Nothing Then [Whatever action is required]
    This takes action if and only if the Target cell is in MyRange, and I think its as simple as it gets.

    I have learned a lot in pursuing this apparently simple question.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Very interesting! I was trying, unsuccessfully, to accomplish the same thing with the intersection operator, the space, via Range(Target "MyRange") but couldn't get it to work. Now, thanks to you, I know about the Intersect function in VBA and testing for double negatives to avoid errors. It really does pay off when you stick with it.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Assuming that a cell name is text, wouldn't the error problem be resolved if you asked

    If ActiveCell.Name = "" then ...
    or
    If ActiveCell.Name <> "" then ... (for the double negative route)

    rather than 'is Nothing'?

    I might point out that I haven't tried it, I'm just curious - for future reference, as it were!
    Beryl M


  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The Name property of a range returns a Name object, rather than text (or an error if the cell doesn't have one).
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Just a comment on all the 2 methods being discussed. The 2 lines of code:

    If activecell.name.name = "MyRange" then

    vs

    If Not Intersect(activecell, Range("MyRange")) Is Nothing Then

    Will give different types of results depending on your setup. If "MyRange" is a named formula that refers to Cell A1 and the active cell is A1 the 2 lines will yield the same results and both IFs will be true. If the activecell is A2 the "name.name" will give a runtime error which must be trapped (and if trapped and compared it would be false, the 2nd will not give an error (and the IF will be false).

    There is also a difference if the name "MyRange" refers to more than just one cell. If "MyRange" refers to A1:A5 and the activecell is A1 then the name.name will give a runtime error (and yield the if is false if trapped) but the second will indicate the IF is true...

    So the question becomes to some degree do you want ANY cell within that range, or only 1 cell that refers to a particular name to be true.

    Steve

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Any comments on the VBA in post #7. Always looking for ways to do thing better.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Steve and Rory,

    I'm grateful for the clarification that completes this arcane story.

    When asking if a cell was in a range, I never expected simply to get an error - not because the cell wasn't in the range, but because it had no name at all. No, or false, would have been slightly more helpful answers.

    The humourist in me speculates that if I created a name range including every cell in the Workbook, I'd neatly avoid the whole problem

    Anyway, now I fully understand - thanks.

    Martin

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Any comments on the VBA in post #7.
    I think the not intersect method will work cleaner as someone already pointed out.

    You could replace your function with the shorter:
    [codebox]Option Explicit
    Function bCellInRange(sRange As String, rCell As Range) As Boolean
    'Returns: True if rCell is within the range named sRange
    ' False if rCell is NOT found within sRange
    bCellInRange = Not Intersect(rCell, Range(sRange)) Is Nothing
    End Function 'bCellInRange[/codebox]

    And call it in code easier than you do since you don't have to use the address at all...
    [codebox]Sub Test()
    'Example of calling the bCellInRange Function
    Dim bRet As Boolean
    bRet = bCellInRange("JFA.Pending", ActiveCell)
    Debug.Print bRet
    End Sub[/codebox]

    But with the not intersect, I don't see the need for the function at all. You could just use the line directly as it is not that much longer than the call to the function.
    [codebox]Sub Test2()
    'Example of calling the bCellInRange Function
    Dim bRet As Boolean
    bRet = Not Intersect(Range("JFA.Pending"), ActiveCell) Is Nothing
    Debug.Print bRet
    End Sub[/codebox]

    Using the one line of code directly not only requires no function, it should be faster and more efficient since VBA is doing the looping in its machine code and not in a VBA routine, but the actual line of code is even shorter than the function call you created:
    bRet = bCellInRange("JFA.Pending", ActiveCell.Address(RowAbsolute:=False, _
    ColumnAbsolute:=False, ReferenceStyle:=xlA1))

    The line directly in code is not that much longer than the function call:
    bRet = Not Intersect(Range("JFA.Pending"), ActiveCell) Is Nothing
    vs
    bRet = bCellInRange("JFA.Pending", ActiveCell)

    Steve




Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •