Custom, user-defined names are identifiers to represent information in the spreadsheet, used mostly in formulas. A custom name can refer to a cell, a range of cells, a computed value, or a formula. You can define a custom name and then use the name in formulas. When the formula is evaluated, the custom name's value is referenced and evaluated.
You can create sheet level or workbook level custom names. The scope of the sheet level custom name is limited to the sheet for which it was created. This allows you to use the same name on several sheets. Formulas in a sheet will ignore sheet level custom names on other sheets.
Use the AddCustomName method in the SheetView class to add workbook or sheet level custom names. The sheetViewScope parameter in the AddCustomName method can be set to true for a sheet level custom name and false for a workbook level custom name.
Avoid using custom names that start with C# or R# patterns (# stands for any number).
It also supports the hidden custom Name.
If a customer creates a custom name without specifying the sheet name (using reference like A1, A1:A2 etc.), the reference will automatically refer to the active sheet. In other words, if you want to use the current sheet with the custom name's formula, you must use special syntax. For instance, if a user provides "!A1" as an input, the final formula of custom name will be changed to "Sheet1!A1".
Define the custom name using the AddCustomName method for the workbook or sheet.
To add a custom name for a cell specified with A1 notation, use the AddCustomName method as shown in the following example, which creates a workbook level custom name.
C# |
Copy Code
|
---|---|
fpSpread1.Sheets[0].AddCustomName("test", "$B$1", 0, 0); |
VB |
Copy Code
|
---|---|
fpSpread1.Sheets[0].AddCustomName("test", "$B$1", 0, 0); |
To add a custom name for a computed value, use the AddCustomName method as shown in this code:
C# |
Copy Code
|
---|---|
fpSpread1.Sheets[0].AddCustomName("alpha", "101", 0, 0); |
VB |
Copy Code
|
---|---|
fpSpread1.Sheets[0].AddCustomName("alpha", "101", 0, 0); |
The following example adds a name that is a range reference.
C# |
Copy Code
|
---|---|
fpSpread1.Sheets[0].AddCustomName("Sales", "Sheet1!$F$20:$F$50", 0, 0); |
VB |
Copy Code
|
---|---|
fpSpread1.Sheets[0].AddCustomName("Sales", "Sheet1!$F$20:$F$50", 0, 0); |