If you have functions that you use on a regular basis that are not in the built-in functions or if you wish to combine some of the built-in functions into a single function, you can do so by defining your own custom functions. They can be called as you would call any of the built-in functions.
A custom function can have the same name as a built-in function. The custom function takes priority over the built-in function. Custom functions are dynamically linked at evaluation time. Thus, the application can redefine an existing custom function.
If a formula attempts to call a custom function with a parameter count outside of the range indicated by the MinArgs and MaxArgs properties of the function, then the Evaluate method of the function is skipped and the #VALUE! error value is used as the result.
Also, if a formula attempts to call a custom function with a parameter that is an error value (for example, #NUM!, #VALUE!, #REF!) and the AcceptsError method of the function returns false for that parameter, then the Evaluate method of the function is skipped and the error value is used as the result.
The custom function's Evaluate method does not receive any information regarding the location (or context) in which the formula is being evaluated. If your custom function needs the row and column in which it is being evaluated then you must add extra parameters to your custom function and manually pass the row and column coordinates in the extra parameters.
Return to the overview of Managing Formulas in Cells.
Using Code
- Define the custom function(s).
- Register the function(s) in the sheet.
- Use the custom function(s).
Example One: Three Functions
The first step is to create the custom functions. In this example, we create three functions: a cube mathematical function, an XOR logical function, and a null string function. The following code implements the custom functions.
The CUBE custom function raises a number to the third power. That is, CUBE(x) is equivalent to POWER(x,3).
| C# |  Copy Code |
|---|---|
public class CubeFunctionInfo : FunctionInfo { public override string Name { get { return "CUBE"; } } public override int MinArgs { get { return 1; } } public override int MaxArgs { get { return 1; } } public override object Evaluate (object[] args) { double num = CalcConvert.ToDouble(args[0]); return num * num * num; } } |
|
The XOR custom function performs an exclusive OR operation on two Boolean values. This is similar to the "^" operator in C or the Xor operator in VB.
| C# | Copy Code |
|---|---|
public class XorFunctionInfo : FunctionInfo { public override string Name { get { return "XOR"; } } public override int MinArgs { get { return 2; } } public override int MaxArgs { get { return 2; } } public override object Evaluate (object[] args) { bool arg0 = CalcConvert.ToBool(args[0]); bool arg1 = CalcConvert.ToBool(args[1]); return (arg0 || arg1) && (arg0 != arg1); } } |
|
The NULL function returns the constant value null similar to how the FALSE() function returns the constant value false.
| C# | Copy Code |
|---|---|
public class NullFunctionInfo : FunctionInfo { public override string Name { get { return "NULL"; } } public override int MinArgs { get { return 0; } } public override int MaxArgs { get { return 0; } } public override object Evaluate (object[] args) { return null; } } |
|
The following code registers the custom functions.
| C# | Copy Code |
|---|---|
spread.ActiveSheet.AddCustomFunction(new CubeFunctionInfo()); spread.ActiveSheet.AddCustomFunction(new XorFunctionInfo()); spread.ActiveSheet.AddCustomFunction(new NullFunctionInfo()); |
|
The following code uses the customs in formulas.
| C# | Copy Code |
|---|---|
spread.ActiveSheet.SetFormula(0, 0, "CUBE(5)"); spread.ActiveSheet.SetFormula(1, 0, "XOR(FALSE,FALSE)"); spread.ActiveSheet.SetFormula(1, 1, "XOR(TRUE,FALSE)"); spread.ActiveSheet.SetFormula(1, 2, "XOR(FALSE,TRUE)"); spread.ActiveSheet.SetFormula(1, 3, "XOR(TRUE,TRUE)"); spread.ActiveSheet.SetFormula(2, 0, "CHOOSE(1,100,NULL(),300)"); spread.ActiveSheet.SetFormula(2, 1, "CHOOSE(2,100,NULL(),300)"); spread.ActiveSheet.SetFormula(2, 2, "CHOOSE(3,100,NULL(),300)"); |
|
Parameters in Custom Functions
By default, parameters are passed by value. A single empty cell is passed as null (Nothing in Visual Basic). A single non-empty cell is passed as a boxed primitive (for example, double, boolean, string, etc.). A cell range is passed as an instance of the CalcArray class. The CalcArray class has RowCount and ColumnCount properties for determining the number of rows and columns in the two dimensional array. The CalcRange class has a GetValue method for getting a single value from of the array. The row and column indexes to the GetValue method are zero based.
If you want a parameter passed by reference, then you must override the AcceptsReference method in the FunctionInfo class. When AcceptsReference method returns true for a parameter, a single cell or a cell range is passed as an instance of the CalcReference class. The CalcReference class has Row and Column properties for determining the first row and column in the reference. The CalcReference class has RowCount and ColumnCount properties for determining the number of rows and columns in the reference. The CalcRange class has a GetValue method for getting a single value from the reference. The row and column indexes to the GetValue method start a Row and Column.
Sample Code
For example, suppose you want a function that counts the number of cells in a range that are less than a given criteria. The function might be coded like this:
| C# | Copy Code |
|---|---|
class CountIfLessThanFunctionInfo : FunctionInfo { public override string Name { get { return "COUNTIFLESSTHAN"; } } public override int MinArgs { get { return 2; } } public override int MaxArgs { get { return 2; } } public override bool AcceptsReference(int i) { return i == 0; } public override object Evaluate(object[] args) { CalcReference range = args[0] as CalcReference; double criteria = CalcConvert.ToDouble(args[1]); double count = 0.0; if (range == null) return CalcError.Value; for (int i = range.Row; i < range.Row + range.RowCount; i++) { for (int j = range.Column; j < range.Column + range.ColumnCount; j++) double cellValue = CalcConvert.ToDouble(range.GetValue(i, j)); if (cellValue < criteria) count++; } } return count; } } |
|