User-Defined Functions (UDF)
- 2 minutes to read
Overview
The Spreadsheet API allows you to create custom functions. Custom functions are available for spreadsheet calculations and can be used in formulas in the same manner as built-in functions. The main difference is that custom functions are not saved within a workbook. They should be added to the Workbook.CustomFunctions or Workbook.GlobalCustomFunctions collection.
Note
Add a custom function to the CustomFunctionCollection collection before you load a document.
If a worksheet contains a custom function that is not recognized by the Spreadsheet (or Microsoft® Excel®), the “#NAME!” error is displayed after the function is calculated. To replace a custom function definition with a calculated value when you save the document, set the WorkbookExportOptions.CustomFunctionExportMode option to CustomFunctionExportMode.CalculatedValue.
Implementation
A custom function is an object that exposes the ICustomFunction interface. To create a custom function, derive it from this interface and implement necessary properties and methods. You should specify the name of the function (IFunction.Name), its arguments (IFunction.Parameters) and return type (IFunction.ReturnType). The IFunction.Volatile property specifies whether the custom function is volatile and should be recalculated each time the Spreadsheet calculates a workbook. The IFunction.Evaluate method performs required calculations.
The Spreadsheet validates the number and type of arguments for a custom function. If function parameters are missing, an exception occurs.
Custom functions are stored in the Workbook.CustomFunctions collection. To use a custom function in calculations, add an instance of your function to this collection.
Limitations
A custom function should not change properties and characteristics of a worksheet. The function’s IFunction.Evaluate method has access to the EvaluationContext object that contains information about the current workbook and worksheet. However, do not call methods or specify properties that can execute the following actions:
- Insert, delete, or format cells;
- Move, rename, delete, or add sheets to a workbook;
- Add names to a workbook;
- Change values of other cells.