Your Azure Functions Can Be Excel Functions

In this post, I'll show how you can define custom Excel functions that invoke your Azure Functions in the cloud. This is an easy and simple procedure.

The End Result: What We're Building

For our example, we'll imagine an Azure Function named ConvertLength that performs simple length conversions, such as from meters to feet or inches to meters. After we create our function, we'll write some VBA code in Excel to connect to it. The end result will be a custom function in Excel that, when run, executes an Azure Function in the cloud.

=ConvertLength(A1, B1, D1)

Excel function ConvertLength is executing Azure Function 

Creating the Azure Function

The function we're creating, ConvertLength, is an HTTP Trigger-invoked in-portal function written in C#. ConvertLength expects 3 parameters:

• value : a value to convert, such as 832
• from : a from unit of measurement, such as ft
• to: a destination unit of measurement, such as in

The result will be the value in the destination unit of measurement, such as 9984

We create our function app in the Azure portal and then our function named ConvertLength. As we do so, we specify an HTTP Trigger, development in the portal, in .NET Core / C#.

Here is the C# code for our function:

#r "Newtonsoft.Json"

using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;

public static async Task<iactionresult> Run(HttpRequest req, ILogger log)
{
    log.LogInformation("C# HTTP trigger function processed a request.");

    double fromValue = Convert.ToDouble(req.Query["value"]);
    string fromUnits = NormalizeUnits(req.Query["from"]);         // m|meter|meters, ft|foot|feet, in|inch|inches
    string toUnits = NormalizeUnits(req.Query["to"]);

    if (fromUnits=="?")
    {
        return (ActionResult)new OkObjectResult($"invalid 'from' parameter - unrecognized unit of measurement");
    }
    if (toUnits=="?")
    {
        return (ActionResult)new OkObjectResult($"invalid 'to' parameter - unrecognized unit of measurement");
    }

    double toValue = 0.00;
    switch(fromUnits)
    {
        case "m":
            switch(toUnits)
            {
                case "m":
                    toValue = fromValue;
                    break;
                case "ft":
                    toValue = fromValue * 3.28084;
                    break;
                case "in":
                    toValue = fromValue * 3.28084 * 12;
                    break;
            }
            break;
        case "ft":
            switch(toUnits)
            {
                case "ft":
                    toValue = fromValue;
                    break;
                case "in":
                    toValue = fromValue * 12;
                    break;
                case "m":
                    toValue = fromValue * 0.3048;
                    break;
            }
            break;
        case "in":
            switch(toUnits)
            {
                case "in":
                    toValue = fromValue;
                    break;
                case "ft":
                    if (fromValue==0)
                        toValue = 0.00;
                    else
                        toValue = fromValue / 12;
                    break;
                case "m":
                    toValue = fromValue * 0.3048;
                    break;
            }
            break;
    }

    return (ActionResult)new OkObjectResult(toValue.ToString());
}

// Normalize units to standard expected values.

private static string NormalizeUnits(String units)
{
    switch(units.ToLower())
    {
        case "m":
        case "meter":
        case "meters":
            return "m";
        case "ft":
        case "foot":
        case "feet":
            return "ft";
        case "in":
        case "inch":
        case "inches":
            return "in";
        default:
            return "?";
    }
    return units;
}

ConvertLength source code

ConvertLength isn't very far along--it only understands feet, inches, and meters as units of measurement--but could become extensive with a little more work. For the purposes of our demo, it's nice to work with a small amount of code.

We can test this in the portal, but also from a browser. Copying the function URL from the portal, here's a test in a browser:

Testing ConvertLength Azure Function in Browser

We can see the function is working. We converted 1 m = 3.28084 ft, 18 ft = 5.4864m, and 5280 ft = 63,360 inches.

Creating The Excel Function

Now that we have a working Azure Function, we need to create an Excel Function to invoke it. To do that, you'll need to get set up for VBA development in Excel. You'll need to do the following in Excel:

1. Activate the VBA Add-in. Open Excel, select File > Options, select Add-ins. Select Analysis ToolPack - VBA and click OK.
2. Enable Access to VBA. In Excel, select File > Options, and go to Trust Center. Click Trust Center Settings and search for Macro Settings. Click the Trust access to the VBA project object model checkbox.
3. Enable Developer Menu. Go to File > Options and select Customize Ribbon. On the list of Main Tabs on the right, check the Developer checkbox and click OK.
4. Add References for Internet Controls. Select Tools > References from the menu, check the following and click OK: Mirosoft Internet Controls, Microsoft WinHTTP Services, Microsoft Scripting Runtime.

With the above out of the way, you should be able to enter Atl+F11 in Excel and have a Microsoft Visual Basic for Applications environment open up. This is where we'll add our code.

Right-click the VBAProject in the outline at left and select Insert Module. In the module, enter the code for the Excel ConvertLength function.

Excel VBA Code 

Below is a listing of the function code. Let's walk through what it's doing:

• The function declaration is named ConvertLength and expects three string parameters: value (number we want to convert), from (the source unit of measurement), and to (the destination unit of measurement). Value will actually be a number, but the function treats everything as string for ease of putting the URL together.
• A URL is assembled: the base URL of our Azure function plus query parameters value, from, and to, whose values are the parameters passed in to the function.
• Next, MSXML2.XLHTTP is called to make an HTTP GET request using variable request.
• Lastly, the function result is th request object's ResponseText parameter.

Function ConvertLength(value As String, fromUnits As String, toUnits As String) As String

Dim url As String
Dim request As Object

url = "https://globalization.azurewebsites.net/api/ConvertLength?value=" + value + "&from=" + fromUnits + "&to=" + toUnits

Set request = CreateObject("MSXML2.XMLHTTP")
    With request
        .Open "GET", url, False
        .Send
    End With

ConvertLength = request.ResponseText
End Function

Excel ConvertLength VBA Code

Save the module file by clicking the disk icon at top in the VBA development environment.

Using The Function in Excel

Now it's time to see it all work together in Excel. In your Excel worksheet, enter a number in cell A1, a unit ("m", "ft", or "in") in B1, and another unit in D1.

Excel parameter values

Now, go to cell C1. Up on the formula bar, enter this formula and press ENTER.

=ConvertLength(A1, B1, D1)

Right after you press ENTER, C1 should display the converted value. Pressing ENTER caused the local Excel function code to run, which in turn invoked the Azure Function.

Now that we see how straightforward this is, we can use the function as much as wish in the spreadsheet:

In Conclusion

In this post, we saw how easy it is to connect an Excel function to an Azure Function which can then be used in a natural, familiar way by Excel users. This provides yet another avenue to serverless computing from a familiar application, one with 750 million users: Excel.