How to Work with Defined Names
- 9 minutes to read
The Spreadsheet and Report Designer controls provide the Defined Names functionality to make it easier to understand and maintain the information and formula expressions in spreadsheet documents. You can assign meaningful names to constants, formulas, and both individual cells and cell range references:
| Defined Name Type | Expression Example without Defined Names | Expression Example with Defined Names |
|---|---|---|
| Reference | =NPER(E5/E7, I4+E9 ,-E4) | =NPER(InterestRatePerMonth, ScheduledPayment+ExtraPayments, -LoanAmount) |
| Constant | =PRODUCT(A2, 8.3) | =PRODUCT(A2, SalesTax) |
| Formula | =NPER(E5/E7, I4+E9 ,-E4) | =ActualNumberPayments |
To learn how to work with defined names, refer to one of the following task-related topics:
How to Create Defined Names;
How to Modify Existing Defined Names;
How to Delete Defined Names;
How to Obtain the Area Information from Cell References.
How to Create Defined Names
You can manage defined names by using the control’s DefinedNames property providing access to the defined names collection.
To assign a new defined name to the reference, constant or a formula, invoke the DefinedNames.Add method which accepts three parameters: the new name (caption) that will be used instead of the referenced object, the reference, and the defined name’s scope. The optional Scope parameter determines whether the defined name can be used only in a particular worksheet or in an entire document without qualification.
Follow the syntax rules for assigning and modifying defined names:
DefinedName property | Syntax Rules |
|---|---|
Start a defined name with a letter, the underscore symbol (“_”) or the backslash (“"). The remaining characters in the name can be letters, numbers, periods, and underscore symbols. A name cannot be the same as a cell reference (like “A1”, “$C$12”, etc.). A defined name cannot contain spaces or be an empty string. A defined name’s length cannot exceed 255 characters. Like in Excel, defined names are not case-sensitive. For instance, you cannot create both the ExtraPayments and EXTRAPAYMENTS defined names within the same scope. | |
Specify a string as a reference to a cell, cell range, formula, or constant associated with the defined name. Refer to the following examples: The “=Sheet1!$D$20” string refers to the D20 cell located on the Sheet1 worksheet. The “=Sheet1!$A$1:$C$10” string refers to the A1:C10 cell range located on the Sheet1 worksheet. The “=SUM(Sheet1!$B$1:$B$10)” string refers to the formula that calculates the sum of values containing in the B1:B10 cell range located on the Sheet1 worksheet. The “=8.3” string refers to a constant value. | |
Specify the worksheet that is the scope the defined name. If the optional Scope parameter is unspecified or set to nil, you can use the created defined name in all worksheets in the spreadsheet document without additional qualification. |
To assign a new defined name, refer to the following code example:
dxSpreadSheet1.DefinedNames.Add('ExtraPayments', // The reference's assigned name
'=Sheet1!$E$9', // The actual reference
dxSpreadSheet1.ActiveSheet); // Limits the reference's scope to the active worksheet
You can enter a defined name referring to a cell, cell range, constant, or formula expression into any cell within the defined scope, like any other reference. To address the defined name outside its scope, prepend the scope (that is, the worksheet name followed by the exclamation mark) to the defined name’s caption (for instance, the resulting expression for the ExtraPayments name defined within the Sheet1 is “Sheet1!ExtraPayments”).
How to Modify Existing Defined Names
Use the control’s DefinedNames property for an indexed access to all defined names created in the opened spreadsheet document. To change a defined name’s caption, reference, and/or scope, use the Caption, Reference, and Scope properties, respectively:
var
AIndex: Integer; // A defined name's index
//...
// Make sure that the required defined name exists...
if((dxSpreadSheet1.DefinedNames.Count > 0) and (AIndex < dxSpreadSheet1.DefinedNames.Count)) then
begin
dxSpreadSheet1.DefinedNames[AIndex].Caption := 'SalesTax'; // A new name
dxSpreadSheet1.DefinedNames[AIndex].Reference := '=8.3'; // The defined name now references a constant
dxSpreadSheet1.DefinedNames[AIndex].Scope := nil; // You can use the defined name in any worksheet
end;
How to Delete Defined Names
The Spreadsheet and Report Designer controls provide two different options for removing defined names:
The DefinedNames.Clear procedure for deleting all defined names at once;
The DefinedNames.Delete procedure for removing a specific defined name.
The following example illustrates how to remove the specified defined name from the collection:
var
AIndex: Integer; // A defined name's index
//...
// Make sure that the required defined name exists
if((dxSpreadSheet1.DefinedNames.Count > 0) and (AIndex < dxSpreadSheet1.DefinedNames.Count)) then
dxSpreadSheet1.DefinedNames.Delete(AIndex);
How to Obtain the Area Information from Cell References
The defined name object provides no common way to obtain the stored reference’s technical information, such as the referred area or cell, since defined names can also refer to formula expressions and constants.
To obtain the cell range referred by a defined name, call its EnumReferences procedure. Refer to the How to Extract the Reference Area Information in Delphi and How to Extract the Reference Area Information in C++Builder topics for information on language-dependent specifics of using the TdxSpreadSheetDefinedName.EnumReferences procedure.
How to Extract the Reference Area Information in Delphi
Obtaining the reference area information is relatively simple, provided that the defined name stores a cell or cell range reference. In Delphi, you can call the defined name’s EnumReferences procedure passing the reference to the information extraction procedure:
var
AIndex: Integer; // The required defined name's index
AReferenceArea: TRect; // Stores the extracted area occupied by a referenced cell or cell range
AReferenceSheet: TdxSpreadSheetTableView; // Stores the extracted worksheet where the referenced area is located
//...
procedure ExtractReferenceInfo(const AArea: TRect; ASheet: TObject); // The information extraction procedure
begin
AReferenceArea := AArea; // Obtains the area occupied by a referenced cell or cell range
AReferenceSheet := ASheet as TdxSpreadSheetTableView; // Obtains the Table View worksheet to which the referenced area belongs
end;
//...
// Make sure that the required defined name exists
if((dxSpreadSheet1.DefinedNames.Count > 0) and (AIndex < dxSpreadSheet1.DefinedNames.Count)) then
dxSpreadSheet.DefinedNames[AIndex].EnumReferences(ExtractReferenceInfo);
Alternatively, you can implement the information extraction procedure as the anonymous procedure:
var
AReferenceArea: TRect; // Stores the extracted area occupied by a referenced cell or cell range
AReferenceSheet: TdxSpreadSheetTableView; // Stores the extracted worksheet where the referenced area is located
ALastDefinedName: TdxSpreadSheetDefinedName;
//...
if(dxSpreadSheet1.DefinedNames.Count > 0) then // Make sure there is at least one defined name within the collection
ALastDefinedName := dxSpreadSheet1.DefinedNames[dxSpreadSheet1.DefinedNames.Count - 1]; // Accesses the last defined name by its index
ALastDefinedName.EnumReferences(
procedure (const AArea: TRect; ASheet: TObject)
begin
AReferenceArea := AArea; // Obtains the area occupied by a referenced cell or cell range
AReferenceSheet := ASheet as TdxSpreadSheetTableView; // Obtains the worksheet to which the referenced area belongs
end
);
If the defined name is actually a reference to a cell or cell range, the AArea parameter returns the row and column cell indexes in the top-left and bottom-right corners of the occupied area. Otherwise, the AArea parameter returns Rect(0, 0, 0, 0).
If the returned ASheet value is nil, the defined name refers to a cell or cell range within the same worksheet. Otherwise, the name refers to the returned worksheet. Note that the defined name is invalid if it is referring to a deleted worksheet.
The ASheet parameter provides no information about the defined name’s scope. To identify it, use the defined name’s Scope property.
How to Extract the Reference Area Information in C++ Builder
First, you need to wrap the TdxSpreadSheetFormulaEnumReferencesProc method interface exposed as the Invoke method.
__interface TdxSpreadSheetFormulaEnumReferencesProc;
typedef System::DelphiInterface<TdxSpreadSheetFormulaEnumReferencesProc> _di_TdxSpreadSheetFormulaEnumReferencesProc;
__interface TdxSpreadSheetFormulaEnumReferencesProc: public System::Iinterface
{
public:
virtual void __fastcall Invoke(const Types::TRect &AArea, TObject* ASheet) = 0;
}
The following C++ code shows a template example that can be used to pass C++ methods as method references to Delphi. Place both the template class and the dummy enumeration type declaration to the project header file:
//...
enum __DummyType {}; // The parameter type used as default
//...
template<
typename InvokeInterface, // Interface with the virtual Invoke method
typename TFunction, // The function type
typename TReturned, // The returned data type
// Five parameters
typename TParameter1 = __DummyType,
typename TParameter2 = __DummyType,
typename TParameter3 = __DummyType,
typename TParameter4 = __DummyType,
typename TParameter5 = __DummyType
>
class TMethodReference: public TInterfacedObject, public InvokeInterface
{
private:
TFunction callback;
public:
TMethodReference(TFunction _callback): callback(_callback) {}
HRESULT STDMETHODCALLTYPE QueryInterface(const GUID& riid, void** ppvObject)
{ return TInterfacedObject::QueryInterface(riid, ppvObject); }
ULONG STDMETHODCALLTYPE AddRef(void)
{ return TInterfacedObject::_AddRef(); }
ULONG STDMETHODCALLTYPE Release(void)
{ return TInterfacedObject::_Release(); }
TReturned __fastcall Invoke(TParameter1 p1)
{ return callback(p1); }
TReturned __fastcall Invoke(TParameter1 p1, TParameter2 p2)
{ return callback(p1, p2); }
TReturned __fastcall Invoke(TParameter1 p1, TParameter2 p2, TParameter3 p3)
{ return callback(p1, p2, p3); }
TReturned __fastcall Invoke(TParameter1 p1, TParameter2 p2, TParameter3 p3, TParameter4 p4)
{ return callback(p1, p2, p3, p4); }
TReturned __fastcall Invoke(TParameter1 p1, TParameter2 p2, TParameter3 p3, TParameter4 p4, TParameter5 p5)
{ return callback(p1, p2, p3, p4, p5); }
};
Declare the global variables storing the extracted values:
//...
TRect AReferenceArea; // Stores the extracted area occupied by a referenced cell or cell range
TdxSpreadSheetTableView* AReferenceSheet; // Points to the worksheet where the referenced area is located
Then, implement the ExtractReferenceInfo procedure:
void ExtractReferenceParameters(const TRect &AArea, TObject* ASheet)
{
AReferenceArea = AArea; // Obtains the area occupied by a referenced cell or cell range
AReferenceSheet = dynamic_cast<TdxSpreadSheetTableView*>(ASheet); // Obtains the worksheet to which the referenced area belongs
}
Invoke the EnumReferences method passing the pointer to the ExtractReferenceInfo procedure:
dxSpreadSheet1->DefinedNames->Add("ExtraPayments", "=Sheet1!$E$9", dxSpreadSheet1->Sheets[0]);
_di_TdxSpreadSheetFormulaEnumReferencesProc proc = new
TMethodReference<
TdxSpreadSheetFormulaEnumReferencesProc,
void (*) (
const Types::TRect &,
TObject*
),
void, // The data type returned by the method
const Types::TRect &, // Referenced area information
TdxSpreadSheetTableView* // Pointer to the worksheet to which the referenced area belongs
>(ExtractReferenceInfo);
dxSpreadSheet1->DefinedNames->Items[0]->EnumReferences(proc);
The values returned by the AArea and ASheet parameters are the same as in Delphi.