User-Defined Functions (UDF)

• Jan 11, 2021
• 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 IWorkbook.CustomFunctions or IWorkbook.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 control (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 SpreadsheetExportOptions.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 control calculates a workbook. The IFunction.Evaluate method performs required calculations.

The Spreadsheet control validates the number and type of arguments for a custom function. If function parameters are missing, an error message is displayed.

Custom functions are stored in the IWorkbook.CustomFunctions collection. To use a custom function in calculations, add an instance of your function to this collection.

Read Tutorial: Create a Simple Custom Function

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.

Function Arguments Dialog

The Function Arguments dialog allows users to enter function arguments. Use the ICustomFunctionDescriptionsRegisterService service to define captions and text descriptions for a custom function and its arguments.