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"
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.