excel-vba Named Ranges Using Named Ranges in VBA
Example
Create new named range called ‘MyRange’ assigned to cell A1
ThisWorkbook.Names.Add Name:="MyRange", _
RefersTo:=Worksheets("Sheet1").Range("A1")
Delete defined named range by name
ThisWorkbook.Names("MyRange").Delete
Access Named Range by name
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("MyRange")
Call MsgBox("Width = " & rng.Value)
Access a Named Range with a Shortcut
Just like any other range, named ranges can be accessed directly with through a shortcut notation that does not require a Range object to be created. The three lines from the code excerpt above can be replaced by a single line:
Call MsgBox("Width = " & [MyRange])
Note: The default property for a Range is its Value, so
[MyRange]is the same as[MyRange].Value
You can also call methods on the range. The following selects MyRange:
[MyRange].Select
Note: One caveat is that the shortcut notation does not work with words that are used elsewhere in the VBA library. For example, a range named
Widthwould not be accessible as[Width]but would work as expected if accessed throughThisWorkbook.Worksheets("Sheet1").Range("Width")
