Defining functions with .NET
The process of 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. Depending on if the function is in a workbook or an extension project, the function will be available in the defining workbook or all workbooks.
For a video demonstration click below:
Functions can return a single value or an entire table as their result.
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, your function's return type can be
1 2 3 4 5
Alternatively, functions can also return
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 online sources. To allow Excel to remain responsive while the function is evaluating, the function should return
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.
ConvertCurrencyfunction is available for download in the
Windy.ExchangeRates.ERAQueryStorm extension package. It uses the Fixer 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 events to notify when a new value has arrived or an error has occurred. The
Start() method is called as soon as the object is returned, and
Stop() is called when the stream is no longer being used (e.g. when the containing cell is deleted), 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. This class only provides protected 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>, though a bit more specialized for this particular use case. If you are using Reactive Extensions, however, you can easily expose an observable as an
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.
C# or VB.NET
When creating the project, you can choose which language to use:
The language setting is stored in the
The language setting determines the compiler and class templates that the project will use. If you choose VB.NET, the code will be compiled using the VB.NET Roslyn compiler, and the scaffolded function files will look something like this:
1 2 3 4 5 6
Simple functions that do not rely on any shared dependencies can be static and do everything on their own.
However, a function might need to get hold of a particular service (e.g. an API object) in order to perform its calculation. That service might be expensive to create, so we would not want to create a new instance each time the function is evaluated. It would be better to have a single instance of the service which would be reused in each function call.
If the function relies on such dependencies, it should not be static. The constructor of the class that owns the function is executed only once (just before the first function call), so any expensive dependencies can be created there.
However, if multiple functions should share the same dependency, the dependency should be registered centrally, in the IOC container in the
App class (inside the App.cs file). The constructor of the function's class can then request the service by simply declaring it as a constructor argument (constructor injection).
For example, we can register the service in App.cs:
1 2 3 4 5 6
We can then use the service in our function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
QueryStorm uses the Unity container for dependency injection.
QueryStorm does not currently have a built-in debugger, but there are two static methods that can help with debugging:
The simplest way to debug issues is to use the
Log(object obj) method to print values to the messages pane.
Log() method is contained in the
QueryStorm.Core.DebugHelpers class. All class files that QueryStorm generates have a static using directive for that class, so you can use the
Log method anywhere in your code, without qualifying it with the namespace or the class name.
It's important to note that QueryStorm has two log viewers. One is part of the IDE, and the other is part of the Runtime (launched separately from the ribbon). The output of the
Log() method will be visible in both places, so Runtime users will be able to see these messages.
Attaching a debugger
Log() method is useful, but quite often a proper debugger is needed to track down tricky bugs. QueryStorm compiles code in a debugger-friendly way, so it's fairly easy to debug your code with an external debugger.
To launch a debugger at a particular location in the source code, use the
Debug() method. The
Debug() method is also available anywhere in the code without prefixing it with the namespace or the class name, due to the
using directive that's part of all code files generated by QueryStorm.
If the local machine has Visual Studio installed, the
Debug() method will launch Visual Studio, attach it to the process and stop the debugger at the current line. If a debugger is already attached, it will simply stop at the line with the
If you do not have Visual Studio installed, you can use the small open-source DNSpy debugger, attach it to the Excel process, and use the
Debug() method to stop the debugger at the desired line in the code.
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: