What is a User Defined Function (UDF)?
A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment.
Although Microsoft Office Excel includes a multitude of built-in worksheet functions, chances are that it doesn’t have a function for every type of calculation you might want to perform. Excel offers an alternative approach for these functions, allowing you to create custom functions using the Visual Basic for Applications (VBA) programming language.
SpreadsheetWEB provides a similar functionality, except it allows you to create those custom functions in the C# programming language.
Applying UDF to Your SpreadsheetWEB Applications
Preparing UDF in C#
Let’s start with an example:
We have a UDF in Excel called RectangleArea, which has two Double parameters named Height and Width and returns a Double value that constitutes the area of a rectangle. This UDF takes the product of its two parameters to produce the rectangle’s area.
For example, when the Height (cell B4) is 15 and Width (cell B5) is 20, the UDF returns 300 as the output:
As mentioned above, SpreadsheetWEB provides a similar functionality using the C# programming language. In C# syntax, the same UDF would be written as follows:
Note: All submitted user defined functions should be defined as public and static. This will make them accessible to invocation by SSW at runtime.
Add UDF code to application
As of SSW version 4.7, the UDF feature can only be used with custom web page SpreadsheetWEB applications, which require the user to code and create the applications’ front-ends in HTML and JavaScript. The assumption here is that the front-end for the application that we are going to submit has already been created for us.
To create the custom web page application, simply click the Add Web Page link in the SpreadsheetWEB Control Panel.
On the Add Web Page screen, fill in the required fields and check the User Defined Functions checkbox to expose the section for user defined functions.
The Version Name is a unique identifier to manage and control updated versions of the UDFs. Directives and Namespaces defines references which will be used in the compilation process of the UDFs (described below). Namespace and Class Name – from the end-user perspective – will help provide feedback for any possible run-time errors. The UDF code itself is submitted via the Code field.
Here is how it should looks once it has been filled in:
Note: If there are any error during the compilation process, you will see the list displayed in the Results field.
Here is a slightly more detailed description of each field:
| Field | Description |
| Version Name | A unique identifier for the version name. This can be an internal flag for version control. Note: SpreadsheetWEB imposes a restriction on version name that requires this identifier to be unique, meaning that subsequent uploads of UDF functions cannot share a version name with any prior uploaded versions. |
| Directives | A semicolon-delimited list of all imports that the code will require. In C#, this is the equivalent of the using statements at the top of your class file (e.g. if you have 'using System; using System.Xml;' at the top of your class, the valid entry for this field would be 'System; System.Xml'). |
| Assemblies | The names of the .NET framework assemblies. This is the equivalent of the references required by a project in order for your code to compile. For example, all .NET framework code will likely require a reference to mscorlib. If you also required a reference to the System, System.Net, and System.Linq namespaces, the valid entry would be 'mscorlib; System; System.Net; System.Core'. |
| Namespace | This string will define the namespace, so any valid namespace (e.g. no spaces or special characters) is appropriate entry. Using a unique name that signifies this particular segment of code will allow easier identification of any runtime errors that appear in the log. |
| Class Name | This string will define the class name, so any valid class name (e.g. no spaces or special characters) is appropriate entry. Using a unique name that signifies this particular segment of code will allow easier identification of any runtime errors that appear in the log. |
| Code | User Defined Functions are entered here. Important Note: At compile-time, your code will be used to generate static classes. This means that any functions submitted in the Code section should be static functions (i.e. no access to instance members or non-static functions). Overloads and optional parameters are supported. Simply copy your code into this field. |
| Results | Upon hitting the Save button, the system will attempt to compile your code. Any compilation errors will be displayed in the Results section, along with a compiler message and the offending line and character position. |
The next step is to save the application and open it. Below, you can see that the user defined function is executing appropriately and displaying the results in the custom web page.
Assumptions
- All functions should be defined as public and static. You can use overloads and optional parameters, subject to the same restrictions imposed by the C# language.
- User defined functions should return values that are going to map directly to their location in the workbook. This means that the output of a UDF will not map to a range, but rather to its specific place of invocation.
- For example, if cell D10 has formula =RectangleArea(B4,B5), the results of this function will populate cell D10. They cannot change the values of other cells, such as D11.
- Range parameters should be defined as two-dimensional object arrays (object[,]) and should be handled as such.
- Native Excel functions cannot be called within UDF code (e.g. WorksheetFunction.Max() cannot be nested inside of the user defined function). An equivalent function in C# can be used or programmed from scratch.
- Users have access to import and reference all of the classes and assemblies provided by the .NET Framework 4.5.2.
- Excel ranges and cells cannot be accessed directly within the UDF (e.g. var rng = Range(“A1:B5”) is not a valid call) . All range and cell references should be sent as parameters to the user-defined functions and be referenced in the Excel function context (e.g. =MyUDF(A1:B5)).
- For example, let’s say that you have function TestUserDefinedFunction() in Excel that accesses the range A1:B5 within the function.
- This function should be refactored to inject that dependency into the function itself: rather than accessing the range within the function, the range should be send into the function.
- In this scenario, the function signature would change to TestUserDefinedFunction(object[,] myRange) and the call to the function from Excel would change to =TestUserDefinedFunction(A1:B5). Within the UDF, you would simply access the designated range through the parameter myRange.
