How do you get a Range to return its Name?

Question

Dim sampleRange as Range
Set sampleRange = Worksheet.Range(Cells(1,1),Cells(1,4)
sampleRange.Name = "Range1"
MsgBox sampleRange.Name

The above code will show the actual address of the range, not the name. Why?
How do I get a named range to return its name?

Solution

For a Range, Name isn't a string it's a Name object, that you then take the Name property of to get the string:

MsgBox sampleRange.Name.Name

Comments

[hornetbzz]

Not Sure to edit the answer safely, but I think in VBA7.1, the answer does not apply : pls read MsgBox sampleRange.Names.Name instead.

[Lance Roberts]

@hornetbzz, Names is a collection for the Workbook or Application. Name is a "name" object of a range for which you then take the "name" property.

[hornetbzz]

@Lance: yes thx, this is my confusion btwn Collection, Objects and Ranges, so depending on how sampleRange is set.

[Andreas Covidiot]

This is not an answer, but a debatable useful comment. I recommend to delete it and add as a comment.

[Andreas Covidiot]

and in case someRang.Name does not exist (the above would throw an error), one could test it using error handling functionality, e.g. based on these nice functions GetNamedRange and IsNamedRange: ozgrid.com/forum/forum/help-forums/excel-general/…