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.

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.

Debug.Print rng.Name.Name

assuming of course the range is named

Regards,
Peter T

"downwitch" wrote in message
ps.com...
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.

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.

On Jul 31, 5:55 pm, "Jim Cone" wrote:
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, USAhttp://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.