Name Object

Multiple objects Name
Range

Represents a defined name for a range of cells, a formula, or a constant value. The Name object is a member of the Names collection.

Using the Name object

Use Names(index), where index is the name, index number or defined name, to return a single Name object.

The index number indicates the position of the name within the collection. The following example displays the cell reference for the first name in the application collection.

MsgBox Names(1).RefersTo

The following example deletes the name "mySortRange" from the active workbook.

ActiveWorkbook.Names("mySortRange").Delete

Use the Name property to return or set the text of the name itself. The following example changes the name of the first Name object in the active workbook.

Names(1).Name = "stock_values"

Use the Add method to create a name and add it to the collection. The following example creates a new name that refers to cells A1:C20 on the worksheet named "Sheet1."

Spreadsheet1.Names.Add "CurrentMonth", "=Sheet1!$A$1:$C$20"

The RefersTo argument must be specified in A1-style notation, including dollar signs ($) where appropriate. For example, if cell A10 is selected on Sheet1 and you define a name by using the RefersTo argument "=Sheet1!A1:B1", the new name actually refers to cells A10:B10 (because you specified a relative reference). To specify an absolute reference, use "=Sheet1!$A$1:$B$1".