Testing for the existence of a cell name

Topic

New Reply

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

Reply | Quote

Replies

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!
Computer Specs

Reply | Quote

Thanks – and I have invented an equally clunky workaround

But surely there’s a “proper” test for whether a cell has name ?

Reply | Quote

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 ?

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

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!

Reply | Quote

The Name property of a range returns a Name object, rather than text (or an error if the cell doesn’t have one).

Reply | Quote

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

Reply | Quote

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!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

SteveA: that is exactly the conclusion I have iterated to. Everything is easy when you know how !

Reply | Quote

Steve,

Thanks much for the critique. I’ve learned a few new things…thanks.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks much for the critique. I’ve learned a few new things…thanks

You are very welcome. Glad I could help.

Steve

Reply | Quote