Class Name (Excel VBA)

The class Name represents a defined name for a range of cells. Names can be either built-in names—such as Database, Print_Area, and Auto_Open—or custom names.

The main procedures of class Name are Delete and Names.Add

Set

To use a Name class variable it first needs to be instantiated, for example

Dim nam As Name
Set nam = Application.Names.Add(Name:="", RefersTo:="", ShortcutKey:="", Category:="", NameLocal:="", RefersToLocal:="", CategoryLocal:="", RefersToR1C1:="", RefersToR1C1Local:="")

The following procedures can be used to set variables of type Name: Names.Item, Names.Add, Application.Names, Workbook.Names and Worksheet.Names

For Each

Here is an example of processing the Name items in a collection.

Dim namName As Name
For Each namName In Application.Names
	
Next namName

Methods

These are the main methods of the Name class

Delete - Deletes the object.

Names.Add - Defines a new name for a range of cells.

Sub MakeRange() 
 
    ActiveWorkbook.Names.Add _ 
        Name:="tempRange", _ 
        RefersTo:="=Sheet1!$A$1:$D$3" 
 
End Sub

Other Methods

Names.Item - Returns a single Name object from a Names collection.

ActiveWorkbook.Names.Item("mySortRange").Delete

Properties

Category returns or sets the category for the specified name in the language of the macro. The name must refer to a custom function or command.

With ActiveWorkbook.Names(1) 
 If .MacroType <> xlNone Then 
 MsgBox "The category for this name is " & .Category 
 Else 
 MsgBox "This name does not refer to" & _ 
 " a custom function or command." 
 End If 
End With

CategoryLocal returns or sets the category for the specified name, in the language of the user, if the name refers to a custom function or command.

With ActiveWorkbook.Names(1) 
 If .MacroType <> xlNone Then 
 MsgBox "The category for this name is " & .CategoryLocal 
 Else 
 MsgBox "This name does not refer to" & _ 
 " a custom function or command." 
 End If 
End With

Comment returns or sets the comment associated with the name.

Index returns a Long value that represents the index number of the object within the collection of similar objects.

MacroType returns or sets what the name refers to.

With ActiveWorkbook.Names(1) 
 If .MacroType <> xlNotXLM Then 
 MsgBox "The category for this name is " & .Category 
 Else 
 MsgBox "This name does not refer to" & _ 
 " a custom function or command." 
 End If 
End With

Name returns or sets a String value representing the name of the object.

NameLocal returns or sets the name of the object, in the language of the user.

Parent returns the parent object for the specified object. Read-only.

RefersTo returns or sets the formula that the name is defined to refer to, in the language of the macro and in A1-style notation, beginning with an equal sign. Read/write String.

Set newSheet = Worksheets.Add 
i = 1 
For Each nm In ActiveWorkbook.Names 
 newSheet.Cells(i, 1).Value = nm.Name 
 newSheet.Cells(i, 2).Value = "'" & nm.RefersTo 
 i = i + 1 
Next 
newSheet.Columns("A:B").AutoFit

RefersToLocal returns or sets the formula that the name refers to. The formula is in the language of the user, and it's in A1-style notation, beginning with an equal sign. Read/write String.

Set newSheet = ActiveWorkbook.Worksheets.Add 
i = 1 
For Each nm In ActiveWorkbook.Names 
 newSheet.Cells(i, 1).Value = nm.NameLocal 
 newSheet.Cells(i, 2).Value = "'" & nm.RefersToLocal 
 i = i + 1 
Next

RefersToR1C1 returns or sets the formula that the name refers to. The formula is in the language of the macro, and it's in R1C1-style notation, beginning with an equal sign. Read/write String.

Set newSheet = ActiveWorkbook.Worksheets.Add 
i = 1 
For Each nm In ActiveWorkbook.Names 
 newSheet.Cells(i, 1).Value = nm.Name 
 newSheet.Cells(i, 2).Value = "'" & nm.RefersToR1C1 
 i = i + 1 
Next

RefersToR1C1Local returns or sets the formula that the name refers to. This formula is in the language of the user, and it's in R1C1-style notation, beginning with an equal sign. Read/write String.

Set newSheet = ActiveWorkbook.Worksheets.Add 
i = 1 
For Each nm In ActiveWorkbook.Names 
 newSheet.Cells(i, 1).Value = nm.NameLocal 
 newSheet.Cells(i, 2).Value = "'" & nm.RefersToR1C1Local 
 i = i + 1 
Next

RefersToRange returns the Range object referred to by a Name object.

p = Sheets(ActiveSheet.Name).Names("Print_Area").RefersToRange.Value 
MsgBox "Print_Area: " & UBound(p, 1) & " rows, " & _ 
 UBound(p, 2) & " columns"

ShortcutKey returns or sets the shortcut key for a name defined as a custom Microsoft Excel 4.0 macro command.

ActiveWorkbook.Names(1).ShortcutKey = "K"

ValidWorkbookParameter returns True if the specified Name object is a valid workbook parameter.

Value returns or sets a String value that represents the formula that the name is defined to refer to.

Visible returns or sets a Boolean value that determines whether the object is visible.

WorkbookParameter returns or sets the specified Name object as a workbook parameter.

Names.Count returns a Long value that represents the number of objects in the collection.

Names.Parent returns the parent object for the specified object. Read-only.