Defined Names
- 9 minutes to read
This document introduces the Defined Name concept and details how to manage defined names.
- Defined Name Overview
- Use Defined Name to Access a Range
- Defined Name Scope
- Syntax Rules for Names
- Create Defined Names
- Access and Change Defined Names
- Delete Defined Names
- Manage Defined Names via the UI
Defined Name Overview
To make it easier to understand the information contained in a worksheet and refer to individual cells, ranges of cells, formulas and constant values, you can use defined names. A defined name is an object that implements the DefinedName interface and contains the following information.
Property | Description |
|---|---|
Name | Indicates an individual cell, range of cells, formula or constant. Usually, a name explains the purpose of an object to which this name refers, making it easier to find and use this object. When specifying a name, you must take into account special syntax rules. |
Refers To | A string specifying a reference to a cell or cell range, formula or constant associated with the defined name. For example: “=Sheet1!$D$20” - refers to the D20 cell located on the Sheet1 worksheet; “=Sheet1!$A$1:$C$10” - refers to the A1:C10 range of cells located on the Sheet1 worksheet; “=SUM(Sheet1!$B$1:$B$10)” - refers to the formula that calculates the sum of values contained in the B1:B10 range of cells located on the Sheet1 worksheet; “=10.5” - refers to a constant value. By default, defined names use absolute cell references, including worksheet names. |
Comment | An explanation or additional information accompanying the defined name. Note The comment length cannot exceed 255 characters. |
Use Defined Name to Access a Range
To access a worksheet range with a defined name, use the Worksheet.Item or IWorkbook.Range property, as illustrated in the following code snippet:
DevExpress.Spreadsheet.CellRange myRange = workbook.Range["MyRange"];
DevExpress.Spreadsheet.CellRange myRange = worksheet["MyRange"];
Defined Name Scope
Each defined name has a scope - an area (individual worksheet or entire workbook) where a name is recognized and can be used without qualification. For example, a defined name (cellName) whose scope is the first worksheet of a workbook (Sheet1) is recognized without qualification in this worksheet only (e.g., =5+cellName). To use this defined name in other worksheets, precede it with the name of the worksheet to which the defined name is scoped (e.g., “=5+Sheet1!cellName”). If the scope of a defined name (cellName_global) is an entire workbook, this name is recognized in any worksheet of this workbook (e.g., “=5+cellName_global”).
Each worksheet contained in a workbook, as well as the workbook itself, has its own collection of defined names (DefinedNameCollection) that can be accessed via the Worksheet.DefinedNames and IWorkbook.DefinedNames properties, respectively. Each name must be unique in its scope (use the DefinedNameCollection.Contains method to determine whether or not a specific name already exists in the collection). However, note that the same name can be used in different scopes.
When you use the defined name without preceding it by a worksheet name, this name is searched within the DefinedNameCollection collection of the worksheet where this name is used. Then, if the name is not found in the worksheet, it will be searched for in the workbook’s collection of defined names. To use a global defined name explicitly, precede it with the workbook name (e.g., “=5+WorkbookName.xlsx!cellName”).
If the defined name is not found, the cell that uses this name displays the #NAME? error.
Syntax Rules for Names
When creating and modifying defined names, follow the rules below.
Start a name with a letter, the underscore symbol (“_”) or the backslash (“\“). The remaining characters in the name can be letters, numbers, periods and underscore symbols.
Note that the single letters “C”, “c”, “R”, or “r” cannot be used as defined names.
- A name cannot be the same as a cell reference (for example, “A1”, “$M$15”, etc.).
- A name cannot contain spaces (use underscore symbols and periods instead).
- A name cannot be an empty string.
- The length of a name cannot exceed 255 characters.
- Names are case-insensitive. For example, you are not allowed to create the Products and PRODUCTS names in one scope.
Create Defined Names
You can create defined names via the CellRange.Name property, or the Worksheet.DefinedNames.Add and IWorkbook.DefinedNames.Add methods.
-
Access an object that specifies a cell or cell range to be named and set its CellRange.Name property. The corresponding DefinedName object is automatically created and added to the Worksheet.DefinedNames collection of the worksheet that contains the named cell or cell range. Thus, this worksheet is the scope of the created name. The DefinedName.RefersTo property is automatically set to the absolute cell reference (including the worksheet name).
using DevExpress.Spreadsheet; // ... IWorkbook workbook = spreadsheetControl1.Document; Worksheet sheet1 = workbook.Worksheets["Sheet1"]; Worksheet sheet2 = workbook.Worksheets["Sheet2"]; // Create a range. CellRange range = sheet1.Range["A1:C3"]; // Specify the name for the created range. range.Name = "namedRange"; // Access an object specifying the created name. DefinedName definedName = sheet1.DefinedNames.GetDefinedName("namedRange"); // Use the defined name in the scope worksheet. sheet1.Cells["D4"].Formula = "=SUM(namedRange)"; // Use the defined name in another worksheet. sheet2.Cells["D4"].Formula = "=SUM(Sheet1!namedRange)"; -
This method allows you to create a defined name whose scope is the specified worksheet and associate this name with an individual cell, range of cells, formula or constant value.
using DevExpress.Spreadsheet; // ... IWorkbook workbook = spreadsheetControl1.Document; Worksheet sheet1 = workbook.Worksheets["Sheet1"]; Worksheet sheet2 = workbook.Worksheets["Sheet2"]; // Access the "Sheet1" worksheet's collection of defined names. DefinedNameCollection sheet1_DefinedNames = sheet1.DefinedNames; // Create a defined name for a range of cells. sheet1_DefinedNames.Add("items", "Sheet1!$A$1:$C$3"); // Create a defined name for a formula. sheet1_DefinedNames.Add("totalSum", "=SUM(items)"); // Create a defined name for a constant. sheet1_DefinedNames.Add("coefficient", "=3"); // Use created names in the scope worksheet. sheet1.Cells["D4"].Formula = "=coefficient*totalSum"; // Use created names in another worksheet. sheet2.Cells["D4"].Formula = "=Sheet1!coefficient*Sheet1!totalSum"; -
This method allows you to create a defined name whose scope is the entire workbook and associate this name with an individual cell, range of cells, formula or constant value.
using DevExpress.Spreadsheet; // ... IWorkbook workbook = spreadsheetControl1.Document; Worksheet sheet1 = workbook.Worksheets["Sheet1"]; Worksheet sheet2 = workbook.Worksheets["Sheet2"]; Worksheet sheet3 = workbook.Worksheets["Sheet3"]; // Create a defined name to be recognized in any worksheet of a workbook. workbook.DefinedNames.Add("cellName_Global", "=Sheet2!$G$20"); // Use the defined name in different worksheets. sheet1.Cells["A1"].Formula = "=cellName_Global"; sheet2.Cells["A1"].Formula = "=cellName_Global"; sheet3.Cells["A1"].Formula = "=cellName_Global";
Access and Change Defined Names
All defined names of a spreadsheet document are stored in the DefinedNameCollection collections. Use the Worksheet.DefinedNames property to access the collection of defined names whose scope is a specific worksheet, or the IWorkbook.DefinedNames property to get defined names whose scope is an entire workbook. You can get an individual defined name by its index in the DefinedNameCollection collection or by its name (DefinedNameCollection.GetDefinedName). To obtain a name assigned to a particular cell range, use the CellRange.GetDefinedName methods.
An individual defined name is specified by the DefinedName object. Use this object’s properties (DefinedName.Name, DefinedName.RefersTo and DefinedName.Comment) to modify the corresponding defined name as required.
Note
After you change an existing defined name, all instances of this name in a workbook will also be changed. For example, if you change DefinedName.Name, all cells using the old name will display the #NAME? error.
Delete Defined Names
To delete an existing defined name, use the DefinedNameCollection.Remove or DefinedNameCollection.RemoveAt method. To remove all defined names from the collection, use the DefinedNameCollection.Clear method.
Note
After you delete a name, all cells using that name will display the #NAME? error. After you delete a named cell or range of cells, all cells using defined names that refer to the deleted cell or cell range will display the #REF! error.
Manage Defined Names via the UI
An end-user can create, edit and delete defined names using SpreadsheetControl’s built-in dialogs. The Formulas tab contains the Defined Names group with buttons that invoke these dialogs.
The Name Manager dialog allows an end-user to work with all defined names in the workbook.
The New Name dialog allows an end-user to create a defined name.
The Use in Formula button displays a list of existing defined names, allowing an end-user to quickly find the desired name and insert it into a formula.
The Create Names from Selection dialog allows an end-user to automatically create names for rows or columns in the selected range from values in the specified bound column or row in this range.
To create workbook level defined names for cell ranges and navigate to cells by their names, the name box accompanying the SpreadsheetControl can be used. For more information on the name box, see the Name Box document.
