Defining functions with .NET
Apps can define their own Excel functions. In fact, many extension apps are just collections of custom Excel functions.
In the simplest case, defining a function with C# or VB.NET is simply a matter of writing the function and decorating it with the
1 2 3 4 5 6 7 8
Loading the new function
Once the project that contains the function is built (compiled), the runtime will automatically load it and make the function available in Excel.
If the function is defined in a workbook app it will be available only in the defining workbook. If it is defined in an extension app, it will be available in all workbooks.
For a video demonstration see the video below:
Custom functions can accept arguments of various data types. Arguments can be optional or mandatory and their values can be simple scalar values or 2d arrays.
The following data types are supported for scalar arguments:
- The four basic Excel data types:
- Other .NET types that the basic data types can be converted to, e.g.
- Enum types
- Nullable versions of the above types e.g.
When using an enum as a parameter, the enum options will be listed in the function description in the tooltip.
Parameters that specify a default value are optional and do not need to be entered by the user. If the user does not input a value for an optional parameter, the default value for that parameter is used.
If a function needs to receive an array of values, the argument type should the a 2D array. The following kinds of 2D arrays are supported in function arguments:
- 2D arrays of unspecified element type i.e.
- 2D arrays with a specific element type e.g.
- 2D arrays with a nullable element type e.g.
When working with value types (e.g.
DateTime), the element type should be nullable if it is legal for elements in the array to be missing, e.g.
DateTime?[,] instead of
Functions can return a single value or an entire table as their result. Calculation can be synchronous or asynchronous. Streaming results (values that change over time) are also supported.
If you are running one of the newer Office 365 versions of Excel that support dynamic arrays, tabular results will automatically spill.
To return a table-valued result, the function's return type can be
1 2 3 4 5
Alternatively, the function can also return
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Lastly, functions can also return a
Tabular, which is handy for functions that return database data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Some functions can take a while to complete. This is often the case when the function fetches data from a remote server. To allow Excel to remain responsive while the function is evaluating, the function should return
Task<T>. Both scalar and table-valued functions can be async.
1 2 3 4 5 6 7 8 9 10
While the task is running, Excel will remain responsive and display #N/A as the (temporary) result of the function. Once the task completes, the final results it returned.
The image below shows an async function that converts currencies using a REST API.
Functions can also return a value that changes over time:
A typical use case for this is displaying stock quotes, which change over time.
To achieve this, instead of returning some particular value, a function should return an object that implements the
1 2 3 4 5 6 7 8 9 10 11 12 13 14
Objects that implement this interface use the
Completed events to signal that a new value has arrived, that an error has ocurred and that no further values will arrive. The
Start() method is executed as soon as the custom function is called, while
Stop() is executed when the stream is no longer being used (e.g. formula was deleted from the cell), so the object can clean up and unsubscribe from any events it subscribed to during
As a convenience, a base class called
ValueStreamBase is provided to make implementing the interface slightly easier (though the only thing it provides are methods for firing the events).
Here is the code for the function shown in the animation above:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
IValueStreaminterface is very similar to
System.IObservable<T>, but more specialized for this particular use case. If you are using Reactive Extensions, however, you can easily convert an observable to an
IValueStreamby using the
observable.ToValueStream()extension method defined in the
Streaming functions internally rely on Excel's Real Time Data (RTD) feature. Excel defines a limit for how often values can be refreshed, which by default is every 2s. You can change this value using C# or VBA by modifying the
Application.RTD.ThrottleInterval property, but QueryStorm also exposes this property through its settings:
The throttle interval is specified in milliseconds and applies globally (to all RTD functions). A lower number will result in faster refreshing of the values, but could result in an unresponsive UI in case of aggressively updating functions.
A good practice to follow when creating functions is to ensure they always return the same output if given the same input. If a function satisfies this requirement, it is a good candidate for caching. Examples of such functions would be functions that translate text from one language to another as well as functions that fetch historical exchange rates data. When you translate a word from one language to another, you can save the result and reuse it in the future instead of hitting a translation API again which would incur a delay, and possible additional expenses.
Functions in QueryStorm can enable caching simply by applying a
1 2 3 4 5
[Cached] attribute ensures the results on evaluating the function are cached and reused every time the function is reevaluated using the same parameters. This reduces the load on APIs as well as conserves paid cloud resources.
Caching is supported for both synchronous and asynchronous functions.
Currently, only in-memory caching is available so the cache starts out empty each time Excel is started. We're considering adding support for persistent caching as well as more advanced expiration policies in the future.
When entering a function into a cell, the user will get a tooltip that displays the name of the function and its parameters. To make it easier for users to understand how to use the function, you can supply a description of the function and its parameters using the
Description property of the
If the function relies on any dependencies that were registered in the app, the dependencies can be injected via dependency injection. In this case, the function should not be static.
For example, if we register a service in App.cs:
1 2 3 4 5 6
We can use the service in our function like so:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
ExcelFunctions1 class in the example above is instantiated before the first call the the
Add method and the same instance is reused for subsequent calls.
A function can get information about the calling workbook using the
IFunctionContextAccessor interface. Using this interface is rarely required, however, as fiddling with the workbook inside a function should be avoided when possible.
An example of a function that does use this is the
Windy.Query function which uses it to read Excel tables that belong to the calling workbook.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
QueryStorm internally uses the popular ExcelDNA library for registering Excel functions.
For technical reasons, user code does not interact with ExcelDNA directly. However, any functionality that ExcelDna offers can very likely be surfaced by QueryStorm in future versions, so please feel free to get in touch for requests of this sort.
More information on ExcelDna, can be found in the following resources: