How to get the name from a range object variable?

[downwitch]

I don't understand why this works this way, but when you read
the .Name property of an Excel.Range variable, it gives you the
address, even if the named range exists.

Is there a way to easily read the saved name of the range, rather than
its address, from the object?

To test what I'm describing, create a new workbook, and name a range
MyRange on its first worksheet.

Then run this:


Public Sub Test_RangeName()

Dim rng As Excel.Range

Set rng = Range("MyRange")
Debug.Print rng.Name, rng.Address, rng.AddressLocal

rng.Name = "SomethingElse"
Debug.Print rng.Name, rng.Address, rng.AddressLocal


End Sub

I know I could loop through the .Names collection of the workbook, but
that seems to be an awful lot of work for what I want--the saved name
out of the object itself, the same saved name I used to set the object
in the first place.

 

[Jim Cone]

The guys at MS, I think, were having a bad day when they
decided that a name is a String except where it is applied to a Range.
A range name is an object and therefore being an object it has a name.
So...
Public Sub Test_RangeName()
Dim rng As Excel.Range
Set rng = Range("MyRange")
Debug.Print rng.Name.Name, rng.Address, rng.AddressLocal
rng.Name.Name = "SomethingElse"
Debug.Print rng.Name.Name, rng.Address, rng.AddressLocal
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"downwitch" <[email protected]>
wrote in message
I don't understand why this works this way, but when you read
the .Name property of an Excel.Range variable, it gives you the
address, even if the named range exists.

Is there a way to easily read the saved name of the range, rather than
its address, from the object?

To test what I'm describing, create a new workbook, and name a range
MyRange on its first worksheet.

Then run this:
Public Sub Test_RangeName()
Dim rng As Excel.Range

Set rng = Range("MyRange")
Debug.Print rng.Name, rng.Address, rng.AddressLocal

rng.Name = "SomethingElse"
Debug.Print rng.Name, rng.Address, rng.AddressLocal
End Sub
I know I could loop through the .Names collection of the workbook, but
that seems to be an awful lot of work for what I want--the saved name
out of the object itself, the same saved name I used to set the object
in the first place.

 

[Peter T]

Debug.Print rng.Name.Name

assuming of course the range is named

Regards,
Peter T

 

[downwitch]

WOW.

Not only would I never have guessed that if I'd spent the rest of my
days trying, it makes absolutely no sense to me.

Thanks a lot.