Tag: functions

Using Azure Functions in Excel

David E. Craig3 Comments

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:

And to my surprise it was actually very easy to set this up. Here are the steps:

  1. Log into https://portal.azure.com
  2. Click “Create a Resource” in the upper left
  3. Type “Functions App” then “Create”
  4. Then follow the directions to create your own Functions App called “ExcelFunctions,” for example.
  5. You will want to create an HTTP Trigger function and I called mine “AddNumbers”
  6. 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.
  7. 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");
}
}

view raw

AddNumbersFunction.cs

hosted with ❤ by GitHub

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:

azurefunctiontest

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:

geturl

This will give you value like this:

https://xxx.azurewebsites.net/api/AddNumbers?code=123456…==

Now the fun part, getting this to work in Excel.

  1. Open Excel and create a new Workbook
  2. In  A1 type 100, in B1 type -58
  3. 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.

How to Expose Methods in your VSTO Add-in

David E. Craig7 Comments

I have had this question a number of times and surprised myself when I did not see it in my blog. So here goes.

There are times when (like my Master Add-in Entry), you need to expose a set of methods or properties in your add-in so that other add-ins or applications are able to access them. Here is how it is done:

1) You create an interface and a class that uses that interface, like this:

[ComVisibleAttribute(true)]
public interface IExposedMethods
{
    void LoadDocument(string path);
}

[ComVisibleAttribute(true)]
[ClassInterface(ClassInterfaceType.None)]
public class ExposedClass : IExposedMethods
{
    public void LoadDocument(string path)
    {
        Globals.ThisAddIn.Application.Workbooks.Open(path);
    }
}

 

2) Next, you need to add the following code to your ThisAddin.cs:

private ExposedClass _exposedInstance;
protected override object RequestComAddInAutomationService()
{
    if (_exposedInstance == null)
        _exposedInstance = new ExposedClass();

    return _exposedInstance;
}

3) And finally, to call it and access this from another application instance, you do this:

Excel.Application xlApp = new Excel.Application();
xlApp.Visible = true;
object addinName = "MyAddinName";
Office.COMAddIn addIn = xlApp.COMAddIns.Item(ref addinName);
addIn.Object.LoadDocument(@"c:\path\test.xlsm");

It is actually quite easy to implement. And the beauty here is that the ExposedClass you created can have all sorts of methods and properties that are exposed. And because the _exposedInstance is global to ThisAddIn (via Globals.ThisAddin._exposedInstance), you can access it anywhere. So it is a perfect way for one add-in to contact another or for a parent Windows Form application to reach into the Add-in and set properties and call methods and have it open documents and such for you.