Working with the Names Collection and Name Object

The Names collection represents a set of ranges in the workbook that have been given names so that the range can be accessed by a name in a formula or by your code accessing the Names collection. The user can create and edit names using the Name Box, as shown in Figure 5.2, or by using the Name menu in the Insert menu. Also, names are sometimes created automatically by features of Excel. When the user defines a custom print area, for example, Excel creates a named range with the name Print_Area.

Iterating over the Names Collection

The Names collection has a GetEnumerator method that allows it to be iterated over using a For Each loop in Visual Basic. The following snippet iterates the Names collection associated with a workbook and displays the name of each Name object, as well as the address of the range it refers to in standard format (for example, "=Sheet1!$A$5"):

  For Each name As Excel.Name in workbook.Names     Console.WriteLine(String.Format( _       "{0} refers to {1}", name.Name, name.RefersTo))   Next 

Accessing a Name in the Names Collection

To access a Name in the Names collection, you use a method called Item, which takes three optional parameters, as shown in Table 5.13.

Table 5.13. Optional Parameters for the Item Method

Parameter Name	Type	What It Does
Index	Object	Pass the name of the Name or the index of the Name in the Names collection.
IndexLocal	Object	Pass the localized name of the Name. A localized name typically exists when an Excel feature has created the name.
RefersTo	Object	Pass the standard format refers to address (=Sheet1!$A$5) to get back the Name object that refers to that address.

Listing 5.22 shows some code that creates a Name and then accesses it in several ways. It creates the Name by using the Add method that takes the name to be used for the Name object and the standard format address string (such as "=Sheet1!$A$5") that the newly created name will refer to.

Listing 5.22. A VSTO Customization That Creates a Name Object and Accesses It

Public Class ThisWorkbook   Private Sub ThisWorkbook_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim names As Excel.Names = Me.Names     names.Add("MyName", "=Sheet1!$A$5")     Dim name1 As Excel.Name     name1 = names.Item(RefersTo:="=Sheet1!$A$5")     MsgBox(String.Format( _       "Name: {0} RefersTo: {1} RefersToR1C1: {2} Count: {3}", _       name1.Name, name1.RefersTo, name1.RefersToR1C1, _       name1.RefersToRange.Cells.Count))     Dim name2 As Excel.Name = names.Item("MyName")     MsgBox(String.Format( _       "Name: {0} RefersTo: {1} RefersToR1C1: {2} Count: {3}", _       name2.Name, name2.RefersTo, name2.RefersToR1C1, _       name2.RefersToRange.Cells.Count))   End Sub End Class 

The Name Object

Given a Name object, you commonly will use several properties. The Name returns the name as a String. The RefersTo property returns the standard format address as a String that the Name refers to. The RefersToR1C1 returns the "rows and columns" format address as a String (such as "=Sheet1!R26C9") that the Name refers to. Most important, the RefersToRange property returns an Excel Range object representing the range of cells that the name was assigned to.

To hide the name from the Define Name dialog box and the Name Box drop-down list, you can set the Visible property to False. To delete a Name, use the Delete method.