Is range named?

[doofusboy]

I know I've done this before but can not for the life of me remeber the syntax I used. Writing an If statement and need to first determine if the target cell is a named range.

If Target.HasName kinda thing.

Any suggestions?

 

[UniMord]

I'm sure there must be a better way to do this, and when a more knowledgeable person steps in and gives you the one line answer, I'll have to post the redfaced icon, but, in the meantime, this was the best I was able to come up with:

Function test4Name(target As Range) As Boolean
    Dim n As Name, fullRef$
 
    fullRef = "='" & target.Parent.Name & "'!" & target.Address
 
    For Each n In ActiveWorkbook.Names
        If n = fullRef Then
            test4Name = True
            Exit Function
        End If
    Next n
End Function

This will tell you if the target range IS a named range. If you need to find out if it's part of a named range, or if it intersects a named range, then we'll need to tweak it.

 

[UniMord]

Upon further testing, I've found that I have to make the single quotes conditional, so here's a revised version:

Function test4Name(target As Range) As Boolean
    'Test if target is a Named Range.
    Dim n As Name, fullRef$, quote$
 
    With target
        quote = IIf(UBound(Split(.Parent.Name)), "'", "")
        fullRef = "=" & quote & .Parent.Name & quote & "!" & .Address
    End With
 
    For Each n In ActiveWorkbook.Names
        If n = fullRef Then
            test4Name = True
            Exit Function
        End If
    Next n
End Function

 

[Jaafar Tribak]

A much shorter function :

Function test4Name(Target As Range) As Boolean

    On Error Resume Next
    
    test4Name = ObjPtr(Target.Name)

End Function

 

[UniMord]

Hey! No fair! ObjPtr() is undocumented, unsupported, and is making me look bad! It's also no longer available in VB.net. Since MS seems intent on dumping it, it doesn't look like a long-term solution.
(Did I hear someone mention XLM? Okay, fine, but, still - you never know when they're going to pull the rug out from under you...)

That rant out of the way...COOL! I like it!

I tested it, and it works, but please explain to me why it works. Target.Name returns the range, for example, Range("C7:C12").Name returns: =Sheet1!$C$7:$C$12, so how does feeding it to ObjPtr() test if it's a Named Range?

 

[CharlesChuckieCharles]

I know I've done this before but can not for the life of me remeber the syntax I used. Writing an If statement and need to first determine if the target cell is a named range.

If Target.HasName kinda thing.

Any suggestions?

lookup Intersect

<CODE>Worksheets("Sheet1").ActivateSet isect = Application.Intersect(Range("rg1"), Range("rg2"))If isect Is Nothing Then    MsgBox "Ranges do not intersect"Else    isect.SelectEnd If</CODE></PRE>
<CODE>

</CODE></PRE>

 

[UniMord]

Charles,

I think you might not have grasped doofusboy's question.
Doofusboy wants to determine if the target cell is a Named Range.
He doesn't know in advance which Named Range it might be, so Intersect isn't going to help.

As an aside, as of Excel 2007, your sample ranges "rg1" and "rg2" aren't legitimate named ranges anymore, because they're addresses.

 

[CharlesChuckieCharles]

Example taken for the microsoft help system as a pointer to helping self

Does Doofusboy want to know the name of every Named range a cell is in?

 

[UniMord]

Since he hasn't come back to clarify, I can only assume, given the wording of his question, that he wants to know if the target is a Named Range, it's a yes or no question. My longwinded function (see above), provides the answer, but Jaafar's concise & efficient function is, by far, the better way to go.

 

[doofusboy]

Yes UniMord you've correctly interpreted what I was after, and yes, Jaafar's ObjPtr(Target.Name) does work [although I'm not sure why/how either].

Thanks all for your efforts.