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

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.

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:

Parameters

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.

Scalar arguments

The following data types are supported for scalar arguments:

• The four basic Excel data types: String, Double, DateTime and Boolean
• Other .NET types that the basic data types can be converted to, e.g. int, short, long, byte, decimal, single
• Enum types
• Nullable versions of the above types e.g. int?, DateTime?

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.

Array-valued arguments

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. object[,]
• 2D arrays with a specific element type e.g. int[,], string[,] etc...
• 2D arrays with a nullable element type e.g. int?[,], DateTime?[,] etc...

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 DateTime[,].

Function results

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.

Table-valued results

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 object[,]:

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

Alternatively, the function can also return IEnumerable<T>:

[ExcelFunction]
public static IEnumerable<Person> GiveMeAListOfPeople()
{
    yield return new Person("Tim", 29);
    yield return new Person("Jim", 44);
}

class Person
{
    public string Name { get; }    
    public int Age { get; }

    public Person(string name, int age)
    {
        Name = name;
        Age = age;        
    }
}

Lastly, functions can also return a Tabular, which is handy for functions that return database data:

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

[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.

Streaming functions

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

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 the Next, Error and 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 Start().

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:

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>, but more specialized for this particular use case. If you are using Reactive Extensions, however, you can easily convert an observable to an IValueStream by using the observable.ToValueStream() extension method defined in the QueryStorm.Apps 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:

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.

Caching

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 [Cached] attribute.

[ExcelFunction, Cached]
public async Task<string> TranslateEnglishToSpanish(string textInEnglish)
{
    // ... use e.g. Google Translate API here
}

The [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.

Providing tooltips

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 [ExcelFunction] and [ExcelArgument] attributes.

Dependencies

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:

public App(IAppHost appHost)
    : base(appHost)
{
    // register the service as a singleton
    Container.RegisterType<FancyCalculator>(new ContainerControlledLifetimeManager());
}

We can use the service in our function like so:

public class ExcelFunctions1
{
    FancyCalculator fancyCalculator;

    // request the service by adding a ctor argument
    public ExcelFunctions1(FancyCalculator fancyCalculator)
    {
        this.fancyCalculator = fancyCalculator;
    }

    // note: the function is not static
    [ExcelFunction]
    public int Add(int a, int b)
    {
        // use the service to perform the calculation
        return fancyCalculator.Add(a, b);
    }
}

The 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.

Caller information

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.

public class WindyQueryFunc
{
    IFunctionContextAccessor functionContextAccessor;

    public WindyQueryFunc(IFunctionContextAccessor functionContextAccessor)
    {
        this.functionContextAccessor = functionContextAccessor;
    }

    [ExcelFunction("Windy.Query")]
    public int Query(string sql)
    {
        var callingWorkbook = functionContextAccessor.CallingWorkbook;
        // ... get tables from the calling workbook implement the SQL querying functionality ...
    }
}

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:

• ExcelDna wiki on GitHub
• ExcelDna documentation
• ExcelDna Google group
• ExcelDna on StackOverflow