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 CollectionThe 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 CollectionTo access a Name in the Names collection, you use a method called Item, which takes three optional parameters, as shown in Table 5.13.
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
The Name ObjectGiven 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. |