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?

 

[CharlesChuckieCharles]

Surprisingly it mostly returns false when i test it 1004 application/object defined error when i step through

ONLY Appears to work when the named range is one cell in size,

or you get the entire named range test4size(B1:b10)

But B7 will return false even though i falls within the range B1:b10

So you have to know quite a bit before its use full

 

[Jaafar Tribak]

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?

Target.Name returns a Name Object

Function test4Name(Target As Range) As Boolean

    On Error Resume Next
    
    [COLOR=Red]MsgBox TypeName(Target.Name)[/COLOR]
    test4Name = ObjPtr(Target.Name)

End Function

The reason you are getting the =Sheet1!$C$7:$C$12 String is because RefersTo is the default Property of the Name Class so it can be safely omited. You normally should get the same string with Range("C7:C12").Name.RefersTo

As for ObjPtr, it returns a non zero long value ( which translates to TRUE) if the passed argument is an instanciated object otherwise it returns 0 (FALSE)

 

[UniMord]

Okay, now that I understand what's going on, I've revised my function to accomplish the same goal, while avoiding ObjPtr(). This is not to say that your function using ObjPtr() isn't beautiful, it is. I just think that if there is a supported, documented way to do it, without too much sacrifice of efficiency, that should be the preferred approach.

Function test4Name(target As Range) As Boolean
    On Error Resume Next
    test4Name = Len(target.Name.Name)
End Function