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 Width would not be accessible as [Width] but would work as expected if accessed through ThisWorkbook.Worksheets("Sheet1").Range("Width")