DefinedNameCollection.Add(String, String) Method
Adds a new defined name to the end of the collection and returns the newly created object.
A string that specifies the name of a cell, cell range, formula or constant.
A string that specifies a cell reference, formula or constant to be associated with the defined name. If the specified value is empty or invalid, an ArgumentException is thrown.
Use the Add method to create a new defined name with the specified settings and append it to the DefinedNameCollection object. If you what to create a defined name to be recognized and accessed within a specific worksheet only, add it to the collection that is returned by this worksheet’s Worksheet.DefinedNames property. To create a defined name whose scope is an entire workbook, add it to the workbook’s DefinedNameCollection returned by the IWorkbook.DefinedNames property. Refer to the Defined Names topic to get more information on defined names.
When you name a cell or cell range via the CellRange.Name property, the corresponding DefinedName object is automatically added to the DefinedNameCollection collection of a worksheet that contains this cell or range of cells. This object’s DefinedName.Name property is set to the specified value of the CellRange.Name property, and the DefinedName.RefersTo property is set to a string that specifies the absolute cell reference preceded by the worksheet name.
This example demonstrates how to define names for formulas. To do this, call the DefinedNameCollection.Add method with a name to be associated with a formula and the formula string passed as parameters. Use the Worksheet.DefinedNames or IWorkbook.DefinedNames property to access and modify the collection of defined names of a particular worksheet or entire workbook, depending on which scope you want to specify for a name.
Worksheet worksheet1 = workbook.Worksheets["Sheet1"]; Worksheet worksheet2 = workbook.Worksheets["Sheet2"]; // Create a name for a formula that sums up the values of all cells included in the "A1:C3" range of the "Sheet1" worksheet. // The scope of this name will be limited by the "Sheet1" worksheet. worksheet1.DefinedNames.Add("Range_Sum", "=SUM(Sheet1!$A$1:$C$3)"); // Create a name for a formula that doubles the value resulting from the "Range_Sum" named formula and // make this name available within the entire workbook. workbook.DefinedNames.Add("Range_DoubleSum", "=2*Sheet1!Range_Sum"); // Create formulas that use other formulas with the specified names. worksheet2.Cells["C2"].Formula = "=Sheet1!Range_Sum"; worksheet2.Cells["C3"].Formula = "=Range_DoubleSum"; worksheet2.Cells["C4"].Formula = "=Range_DoubleSum + 100";