I have been delving more and more into Azure recently. In looking for a way to build an Excel User Defined Function (UDF) that did not require Visual Basic for Applications (VBA) or similar, I looked at what I could do with:
- Azure Functions
- Excel WEBSERVICE function
And to my surprise it was actually very easy to set this up. Here are the steps:
- Log into https://portal.azure.com
- Click “Create a Resource” in the upper left
- Type “Functions App” then “Create”
- Then follow the directions to create your own Functions App called “ExcelFunctions,” for example.
- You will want to create an HTTP Trigger function and I called mine “AddNumbers”
- You will be asked where you want to edit the function and I chose online in the browser, which is what makes this the coolest thing ever.
- Here is what my function looks like:
| #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."); | |
| string v1 = req.Query["value1"]; | |
| string v2 = req.Query["value2"]; | |
| if(v1!=null && v2!=null) | |
| { | |
| int result = int.Parse(v1) + int.Parse(v2); | |
| return (ActionResult)new OkObjectResult($"{result}"); | |
| } | |
| else | |
| { | |
| return new BadRequestObjectResult("Invalid input"); | |
| } | |
| } |
Once you have created your function, you can also test it right there in Azure. On the Right is a tab called “Test.” You can select it, and then add parameters like this:
You can click “Save and Run” at the top of the page to run the function and verify the result: 42. Now, to get this to work in Excel, you need the URL. To do this click the “</> Get Function URL” button:
This will give you value like this:
Now the fun part, getting this to work in Excel.
- Open Excel and create a new Workbook
- In A1 type 100, in B1 type -58
- Select C1 and add the following function:
=WEBSERVICE("https://xxx.azurewebsites.net/api/AddNumbers?code=123456...==&value1=" & A1 & "&value2=" & B1)
Once you press enter and Excel updates you should see the value of 42 appear.
This is a very simple example of how you can implement an Azure Function in Excel as a User Defined Function. Imagine all the possibilities where you can collect data, analyze results, use Cognitive Services and vast stores of data from a Data Lake and much, much more. The possibilities are endless.
