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 automatically created by features of Excel. For example, when the user defines a custom print area, 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 the foreach keyword in C#. For example, 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 instance, "=Sheet1!$A$5").

foreach (Excel.Name name in workbook.Names) { Console.WriteLine(String.Format( "{0} refers to {1}", name.Name, name.RefersTo)); }

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.

Listing 5-22 shows some code that creates a Name and then accesses it in several ways. It creates the Name 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

private void ThisWorkbook_Startup(object sender, EventArgs e) { Excel.Names names = this.Names; names.Add("MyName", "=Sheet1!$A$5", missing, missing, missing, missing, missing, missing, missing, missing, missing); Excel.Name name1 = names.Item(missing, missing, "=Sheet1!$A$5"); MessageBox.Show(String.Format( "Name: {0} RefersTo: {1} RefersToR1C1: {2} Count: {3}", name1.Name, name1.RefersTo, name1.RefersToR1C1, name1.RefersToRange.Cells.Count)); Excel.Name name2 = names.Item("MyName", missing, missing); MessageBox.Show(String.Format( "Name: {0} RefersTo: {1} RefersToR1C1: {2} Count: {3}", name2.Name, name2.RefersTo, name2.RefersToR1C1, name2.RefersToRange.Cells.Count)); }

The Name Object

Given a Name object, you will commonly 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 importantly, 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 and the Name Box drop-down, you can set the Visible property to false. To delete a Name, use the Delete method.