21
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?

w5m
  • 2,286
  • 3
  • 34
  • 46
GenericJam
  • 2,915
  • 5
  • 31
  • 33

1 Answers1

64

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
Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
  • 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. – hornetbzz Feb 02 '18 at 20:37
  • @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. – Lance Roberts Feb 05 '18 at 19:01
  • @Lance: yes thx, this is my confusion btwn Collection, Objects and Ranges, so depending on how `sampleRange` is set. – hornetbzz Feb 06 '18 at 11:11
  • 2
    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`: https://www.ozgrid.com/forum/forum/help-forums/excel-general/42560-determine-in-vba-whether-a-range-has-a-name – Andreas Covidiot May 10 '19 at 17:24
  • 1
    sampleRange.Name = "Range1" to name the range is poor practice It only works because Name (the string containing "The Name") is the default property of Name. Much better sampleRange.Name.Name = "Range1" It's never good practice to use the default property without referring to it explicitly. Good programming exactly controls the environment by referring explicitly to the property required. Removes ambiguity as created the issue here. Eliminates the issue created if the default property is changed in a future update – sirplus Oct 11 '22 at 14:01