Class Names (Excel VBA)

A collection of all the Name objects in the application or workbook.

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

Dim nms as Names
Set nms = ActiveWorkbook.Names

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

Removing names with invalid references

Code below lists the deleted Name items

Dim nm As Name
For Each nm In ActiveWorkbook.Names
  with nm
    If Instr(1, .RefersTo, "#REF!")>0 Then
      Debug.Print .Name & ": deleted"
      .Delete
    End If
  End With
Next nm

Add

Defines a new name for a range of cells.

Add (Name, RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local)

ActiveWorkbook.Names.Add, Name:="Range1", RefersTo:="=Sheet1!$A$1:$D$3"

Arguments

Optional arguments

The following arguments are optional

Name (String) - Specifies the text, in English, to use as the name if the NameLocal parameter is not specified. Names cannot include spaces and cannot be formatted as cell references.

RefersTo (String) - Describes what the name refers to, in English, using A1-style notation, if the RefersToLocal, RefersToR1C1, and RefersToR1C1Local parameters are not specified. NOTE: Nothing is returned if the reference does not exist.

Visible (Boolean) - True specifies that the name is defined as visible. False specifies that the name is defined as hidden. A hidden name does not appear in the Define Name, Paste Name, or Goto dialog box. The default value is True.

MacroType (Byte) - The macro type, determined by one of the following values: 1 - User-defined function (Function procedure) 2 - Macro (Sub procedure) 3 or omitted - None (the name does not refer to a user-defined function or macro).

ShortcutKey (String) - Specifies the macro shortcut key. Must be a single letter, such as "z" or "Z". Applies only for command macros.

Category (String) - The category of the macro or function if the MacroType argument equals 1 or 2. The category is used in the Function Wizard. Existing categories can be referred to either by number, starting at 1, or by name, in English. Excel creates a new category if the specified category does not exist.

NameLocal (String) - Specifies the localized text to use as the name if the Name parameter is not specified. Names cannot include spaces and cannot be formatted as cell references.

RefersToLocal (String) - Describes what the name refers to, in localized text using A1-style notation, if the RefersTo, RefersToR1C1, and RefersToR1C1Local parameters are not specified.

CategoryLocal (String) - Specifies the localized text that identifies the category of a custom function if the Category parameter is not specified.

RefersToR1C1 (String) - Describes what the name refers to, in English using R1C1-style notation, if the RefersTo, RefersToLocal, and RefersToR1C1Local parameters are not specified.

RefersToR1C1Local (String) - Describes what the name refers to, in localized text using R1C1-style notation, if the RefersTo, RefersToLocal, and RefersToR1C1 parameters are not specified.

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveWorkbook.Names.Count

Item

Returns a single Name object from a Names collection.

You must specify one, and only one, of these three arguments.

Item (Index, IndexLocal, RefersTo)

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

Optional arguments

The following arguments are optional

Index (Long) - The name or number of the defined name to be returned.

IndexLocal (String) - The name of the defined name, in the language of the user. No names will be translated if you use this argument.

RefersTo (String) - What the name refers to. You use this argument to identify a name by what it refers to.