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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #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.