Skip to content

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 ExcelFunction attribute.

1
2
3
4
5
6
7
8
public class MyFunctions
{
    [ExcelFunction]
    public static int Add(int a, int b)
    {
        return a + b;
    }
}

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:

YOUTUBE

Table-valued functions

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.

Dynamic function spill

To return a table, your function's return type can be object[,]:

1
2
3
4
5
[ExcelFunction]
public static object[,] GiveMeA2DArray()
{
    return new object[,] { {1,2}, {3,4}};
}

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
public class DatabaseSampleFunctions
{
    SqlServerEngineBuilder engineBuilder;
    public DatabaseSampleFunctions(SqlServerEngineBuilder engineBuilder)
    {
        // QueryStorm uses engines and engine builders for DB access
        this.engineBuilder = engineBuilder;
    }

    // this one returns Tabular
    [ExcelFunction]
    public Tabular GetSomeDataFromDatabase()
    {
        var sqlServerEngine = this.engineBuilder
            .WithConnectionString("...")
            .Build();

        return sqlServerEngine.Execute("select * from Department");
    }

    // this one is async and returns Task<Tabular>
    [ExcelFunction]
    public async Task<Tabular> GetSomeDataFromDatabaseAsync()
    {
        var sqlServerEngine = this.engineBuilder
            .WithConnectionString("...")
            .Build();

        return await sqlServerEngine.ExecuteAsync("select * from Department", CancellationToken.None);
    }
}

Async functions

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 Task<T>.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[ExcelFunction]
public static async Task<string> ExampleAsync(int delay)
{
    var sw = new System.Diagnostics.Stopwatch();
    sw.Start();

    await Task.Delay(delay);

    return $"This function took {sw.ElapsedMilliseconds}ms to execute";
}

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.

Async function example

The ConvertCurrency function is available for download in the Windy.ExchangeRates.ERA QueryStorm extension package. It uses the Fixer API.

Streaming functions

Functions can also return a value that changes over time:

Streaming function example

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 IValueStream interface:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
public interface IValueStream
{
    // Provide next value
    event Action<object> Next;
    // Notify of error
    event Action<Exception> Error;
    // Signal that the stream ended (no more values)
    event Action Completed;

    // Start providing values
    void Start();
    // Clean up
    void Stop();
}

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 Start().

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
public class DemoFunctions2
{
    [ExcelFunction]
    public static IValueStream GetMessagesStream(int intervalMs)
    {
        return new MyDemoStream(intervalMs);
    }

    private class MyDemoStream : ValueStreamBase
    {
        bool stop = false;
        private readonly int intervalMs;

        public MyDemoStream(int intervalMs)
        {
            this.intervalMs = intervalMs;
        }

        public override async void Start()
        {
            string[] messages =
            {
                "Hi there",
                "I'm a streaming function",
                "That gives back strings",
                $"Every {intervalMs} milliseconds",
                "But now I'm done:)"
            };

            foreach (var str in messages)
            {
                if(stop)
                    break;

                OnNext(str);
                await Task.Delay(intervalMs);
            }

            OnCompleted();
        }

        public override void Stop()
        {
            stop = true;
        }
    }
}

The IValueStream interface 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 IValueStream using the observable.ToValueStream() extension method defined in the QueryStorm.Core namespace.

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:

RTD Throttle Interval

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:

New project dialog

The language setting is stored in the module.config file.

1
"Language": "CSharp"

...or...

1
"Language": "VisualBasic"

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
Public Module MyFunctions1
        <ExcelFunction>
        Public Function Add(val1 As Int32, val2 As Int32) as int32
            return val1 + val2
        End Function
End Module

Dependencies

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
public App(IUnityContainer container)
    : base(container)
{
    // register the service as a singleton
    container.RegisterType<SomeService>(new ContainerControlledLifetimeManager());
}

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
public class ExcelFunctions1
{
    SomeService someService;
    // request the service by adding a ctor argument
    public ExcelFunctions1(SomeService someService)
    {
        this.someService = someService;
    }

    [ExcelFunction]
    public int Add(int a, int b)
    {
        // use the service to perform the calculation
        return someService.Add(a, b);
    }
}

QueryStorm uses the Unity container for dependency injection.

Debugging functions

QueryStorm does not currently have a built-in debugger, but there are two static methods that can help with debugging: Log() and Debug().

YOUTUBE

The Log() method

The simplest way to debug issues is to use the Log(object obj) method to print values to the messages pane.

The 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

The 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 Debug() call.

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.

ExcelDNA

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: