How to Create Custom Functions
- 7 minutes to read
The ExpressSpreadSheet control provides a large number of various built-in functions for formula calculations to satisfy the needs of most users. However, if the functions you need are not on the list, you can extend it with the functions you implemented. Moreover, you can easily override a built-in function with a custom implementation.
In order to use custom functions in the Spreadsheet Control, you need to:
Implement the custom functions. Each custom function must have a corresponding procedure of the TdxSpreadSheetFunction procedural type;
Set the number and the type of the implemented function’s parameters. Write specification procedures setting both the number and the type of the custom functions’ parameters. Each custom function must have its own set of parameter settings specified by a corresponding procedure of the TdxSpreadSheetFunctionParamInfo procedural type;
Add the implemented custom function to the Spreadsheet’s functions repository.
Implementing the Custom Function
To add a new formula to the list of available functions, you need to implement it as a procedure. The Sender argument is used to return the formula calculation result, and the AParams argument passes the function’s arguments entered into the formula.
The custom function implementation procedure has the TdxSpreadSheetFunction procedural type:
TdxSpreadSheetFunction = procedure(Sender: TdxSpreadSheetFormulaResult; const AParams: TdxSpreadSheetFormulaToken);
The following code example implements the custom TriangleArea function that calculates the area of a triangle (S) given two sides (a and b) and the angle (γ) between them according to the formula: S = 0.5 * a * b * sin γ.
procedure functionTriangleArea(Sender: TdxSpreadSheetFormulaResult; const AParams: TdxSpreadSheetFormulaToken);
var
P1, P2, P3: Variant; // Three parameters accepted by the function
begin
// If the function accepts more or less than three parameters...
if(Sender.GetParamsCount(AParams) <> 3) then
Sender.SetError(ecValue) // The function displays the #VALUE! error code as a calculation result
else
// The function consecutively extracts the numeric values from the formula tokens
if(Sender.ExtractNumericParameter(P1, AParams) and
Sender.ExtractNumericParameter(P2, AParams, 1) and
Sender.ExtractNumericParameter(P3, AParams, 2)) then
// If the arguments were successfully extracted, the function
// calculates the triangle's area according to the respective formula
Sender.AddValue(0.5 * P1 * P2 * SIN(P3 * Pi / 180));
end;
Assigning the Number and the Types of the Custom Function’s Parameters
Aside from the custom function implementation, you need to specify the number of parameters accepted by the function and their types (whether it is a single value, an array, or an open-ended list).
The custom function specification procedure has the TdxSpreadSheetFunctionParamInfo procedural type:
TdxSpreadSheetFunctionParamInfo = procedure(var AParamCount: Integer; var AParamKind: TdxSpreadSheetFunctionParamKindInfo);
The TriangleArea custom function requires three mandatory arguments to calculate the area of a triangle: the length of two adjacent sides and the angle between them. The following code example demonstrates an implementation of the custom function’s specification procedure.
procedure parametersTriangleArea(var AParamCount: Integer; var AParamKind: TdxSpreadSheetFunctionParamKindInfo);
begin
AParamCount := 3; // The TriangleArea function accepts three parameters
// Set the size of the AParamKind dynamic array according to the number of accepted parameters
SetLength(AParamKind, AParamCount);
// All three parameters of the custom function are numeric values
AParamKind[0] := fpkValue;
AParamKind[1] := fpkValue;
AParamKind[2] := fpkValue;
end;
Adding the Custom Function to the Repository
To use the implemented custom function in the same way as formulas from the list of built-in functions, you need to add it to the ExpressSpreadSheet‘s functions repository. Formulas are entered into the cells (either programmatically or by using the cell in-place editor) by their appointed names stored in resource strings. In Delphi projects, you can write resource strings directly within the unit.
The TdxSpreadSheetFunctionsRepository class’ Add method accepts the pointer to the resource string that contains the custom function’s name, and both the custom function’s implementation and specification procedures that were created earlier. The Add method also accepts the function’s unique inner token. The tokens ranging between 0 and 2047 are reserved for the built-in Spreadsheet Control’s functions. Additionally, you can specify the custom function’s type for grouping with already existing functions.
The custom function names are capitalized in cells in the same manner as built-in functions.
The following code example adds the TriangleArea custom function to the repository, accessible via the dxSpreadSheetFunctionsRepository global method.
// Additionally, we need to specify the function's displayed name in a resource string
resourcestring // The resource strings' declaration
stringTriangleArea = 'TriangleArea'; // The custom function's
// public name (always shows capitalized)
//...
dxSpreadSheetFunctionsRepository.Add(@stringTriangleArea, functionTriangleArea, parametersTriangleArea, frkValue, 2048, ftMath);
When this code is executed, the TriangleArea custom function is ready for use like any formula from the list of built-in functions.
Unlike Delphi projects, there is no direct way of creating resource strings in C++Builder. The resource strings are imported into the C++Builder projects as statically linked libraries. In your Delphi package project you need to fill the resource strings within the dedicated unit:
unit ResourceStringNames; // The generated resource string header
// name matches the delphi unit name
interface
resourcestring // The resource strings' declaration
stringTriangleArea = 'TriangleArea'; // The custom function's public name (always shows capitalized)
implementation
end.
The following code example adds the implemented TriangleArea custom function to the Spreadsheet Control’s functions repository:
#include "ResourceStringNames.hpp" // Add the resource strings
// header (generated from the Delphi unit with resource strings)
// to the rest of your project's other #include directives
//...
#pragma link "DelphiResourcePackage.lib" // Add the resource static
// library to the rest of your project's linker directives
//...
// Adding the TriangleArea custom function to the repository.
// Note that the Delphi compiler adds the "_" symbol to the
// resource string names
dxSpreadSheetFunctionsRepository()->Add(&_stringTriangleArea, functionTriangleArea, parametersTriangleArea, frkValue, 2048, ftMath);
Overriding a Built-in Function
Overriding a built-in function with a custom implementation is very similar to adding a new custom function to the Spreadsheet control and therefore, includes the same steps:
Implementing a custom function;
Assigning the same number and types of parameters as in the case of an overridden built-in function;
Registering the custom implementation.
The first two steps are identical to implementing any other custom function. To register a custom function overriding a built-in function, call the Spreadsheet control function repository’s Add procedure and pass one of the following pointers as the AName parameter:
A pointer to a resource string with the name of the function you need to override;
A pointer to a custom resource string with a name that matches one of the built-in function names.
When overriding an existing function, the Add procedure automatically replaces the value passed as the AToken parameter with a token of the respective built-in function. A registered custom function whose name is on the list of built-in functions (including those found in Microsoft Excel® and not yet implemented in the Spreadsheet control) is always invoked instead of the corresponding built-in variant.