Using Azure Functions in Excel

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:

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 Configure VSCode for Office Development

I thought I had written this some time ago, but I guess I did not. So here goes…

NOTE: First, I want to state that if you are creating applications for the full Office clients, it might be best to continue using Visual Studio 2017 as you are able to debug directly in these clients with much more ease than you can from VSCode.

Some might find it hard to believe, but I have been geeking out hard and using VSCode to develop OfficeJS (Office 365 Web Add-ins). I find it a very useful, light client, where I am able to focus on just the basics. When combined with tools like Node Package Manager (NPM), browser sync, Yoeman and Git, you have a surprisingly robust development environment what makes doing things delightfully easy. wlEmoticon-hotsmile.png But it was not always this way, I had a learning curve and it took me a while to get it all setup and running correctly. I am not saying it was hard, just… different. So I channeled my inner geek and much to his delight, it turned out for the better.

So as I stated the challenge was getting it all setup. This is going to be very different, especially if you are coming from the Visual Studio 2017 world (much more so, from the VBA world). So, here are the step by step instructions I use for getting my VSCode development environment all configured for Office Development:

  1. Download and install VSCode: https://code.visualstudio.com/ .
  2. Download and install Node to get Node Package Manager (NPM): https://nodejs.org/en/download/. On this page you will want to download the Windows/MSI/64-bit version.
  3. Download and install Git: https://git-scm.com/download. On this page you will want to make sure you select to install the latest which is from the link in the upper right of the page.
  4. Now we have the basic packages we need in order to begin development. What we need to do now is install the Yeoman generator. To do this:
    • Open VSCode
    • Press CTRL+`. This will open the console window. Alternatively you can go to the View menu and click Integrated Terminal
    • Switch to the Terminal in the Console and then type the following command: npm install global yo
  5. Now you are ready to start generating OfficeJS add-ins from VSCode, and these steps will walk you through the first one:
    • Type: yo office sample-1
    • Once you do this the scaffold generator will kick in and ask you a few questions. Answer them as I have, in bold, below:? Would you like to create a new subfolder for your project? No
      ? Which Office client application would you like to support? Excel
      ? Would you like to create a new add-in? Yes, I need to create a new web app and manifest file for my add-in.
      ? Would you like to use TypeScript? No
      ? Choose a framework: Jquery
      ? Would you like to open it now while we finish creating your project? Yes
    • Once complete you will not have a solution folder called sample1 under your user profile c:\users\<yourname>\sample-1, but the project will not be open in VSCode, yet…
    • To open your project on the File menu, click Open Folder and then browse to and open the sample1 folder, then press Select Folder. NOTE: If you are coming from Visual Studio where the tree of solution files appears on the right side of the screen, the “Explorer” in VSCode appears on the left and it is actually a listing of ALL files and ALL folders in the solution directory.
    • You will see the code files for your project on the left hand side in the Explorer. The key files of importance to you, getting started will be:
      • sample-1-manifest.xml – this is your manifest for publishing your add-in.
      • index.html – this is your primary page or “task pane” for your add-in.
    • In this example I will not have you edit any of the files, but the basics are completely provided to build a solution. At this point we will check this into Git. Press CTRL+SHIFT+G. This will open the Source Control repository page. Click the icon to the right of the words Source Control, to Initialize Repository. This will open the folder to your solution, simply click Initialize Repository. You can now work with Git to manage your project. I will not go into more detail, but if you are interested, please watch this video: https://git-scm.com/video/what-is-git
    • Finally, we are ready to debug. But first, if you are on Windows 10, and you use Edge or Internet Explorer as your default browser, I would strongly suggest using the Google Chrome browser because the debug tools are so much better. However, there is one change you need to make:
      • Open the bsconfig.json file in the Explorer
      • At the top of the configuration you need to place this line: “browser”: “chrome”, so that the file looks like this:
        {
            "browser": "chrome",
            "ui": {...
    • To debug, press CTRL+` to open or return to the Integrated Terminal. Type: npm start. This will open your project in Chrome. You might get a warning that the site is not trusted, click Advanced and select to trust anyway / continue.
    • Open another tab and browse to office365.com. Once there, log into your account. On the Office menu in the upper left, click Excel. On the Insert menu, click Office Add-ins. In the upper right of the dialog, click Upload my add-in. Click Browse and then select the file: c:\users\<yourname>\sample-1\sample-1-manifest.xml. Your add-in will now load on the Home tab, switch there, and press Show Taskpane.

This looks like a lot of work and I know for most of you that are like me, coming from a Visual Studio and/or VBA background, this is very alien. You might consider this a step backwards or it might seem like it is time to hang up the spurs. wlEmoticon-disappointedsmile.png But give it some time, especially if you are just getting into OfficeJS, it will grow on you. In the meantime, here are some of my other blog entries around this to help you:

Please let me know if you have questions or would like some help with any of this. What I hope to be able to do at some point is to add posts on:

  • Importing a Script Lab project into a Yo Office scaffold
  • A video on the steps outlined in this post. I have done videos for internal training at Microsoft before, but never on my own for my blog. So this will be new territory if I can get around to it.
  • Other samples posts to getting started with various projects.

If you have any ideas of what you would like to see, please let me know.

OfficeJS.dialogs Updated (v1.0.5)

I have been working hard on my OfficeJS.Dialogs library and just published version 1.0.5. You can get it from NPM and GitHub. See my previous post for more information on how to do this.

I have added a few new features:

  • A simple Alert.Show() method that displays a simple OK box. For those times you want to just simply pop up a quick notification to the user.
  • A Progress.Show() that displays a progress bar. This allows for you to show the progress bar and then issue Progress.Update() to move the progress bar along. When you are done you call Progress.Complete().
  • A Wait.Show() dialog that will show an indeterminate spinner. This form will remain up until you issue a Wait.CloseDialog().
  • New UpdateMessage() and Update() methods were added to the MessageBox. This was done to allow you to quickly ask a lot of questions of the user in one instance of the dialog, without giving the user back to the application for a second while the new dialog is rendered. UpdateMessage() will just update the message but keep all the buttons the same, but you will specify a new callback. Update() will allow you to fundamentally change all the settings the MessageBox (buttons, icon, caption, text and all), plus a new callback function.
  • Behind the scenes I made some improvements/bug fixes:
    • If you try to show two dialogs too quickly, nothing will happen. So I added a half-second delay between dialog displays to make sure you never get an overlap.
    • You will get an error message in your callback if more than one dialog is attempted to be opened at once.
    • “Window Messaging” has been setup with Progress and MessageBox to allow the parent and the dialog to pass messages back and forth. It involves using setTimeout().

For those interested in the last item, here is what that look like:

        /**
        * Handles messages coming from the parent
        */
        function startMessageHandler() {
            setTimeout(function() {
                var message = localStorage.getItem("dialogMessage");
                localStorage.setItem("dialogMessage", ""); // clear the message
                if(message !== undefined && message !== null && message != "")
                {
                    var msg = JSON.parse(message);
                    if(msg.message == "update") {
                        // update the form
                        updateForm(msg.settings);
                    } else if(msg.message == "close") {
                        // do nothing special here
                        return; // stops the message pump
                    } else if(msg.message == "progress") {
                        if(msg.settings.Number > 100) return;
                        $("#bar").prop("value",msg.settings.Number);
                    }
                }
                startMessageHandler(); // call again
            }, 0);
        }

Here is an example of how to use the Progress dialog. The method signature is like this:

Progress.Show( [message], [start], [max], [completeCallback], [cancelCallback] )

  • The message is the message the user see’s when the dialog is opened.
  • The start is the number you want the progress bar to start at. Usually this should just be zero (0).
  • The max is the number you want the Progress bar to end at. Usually this should be 100. But it can be any number you want. If you have 5 steps to perform in the background while this dialog is up, you can set this to 5.
  • The completeCallback is your callback function to be called when your code calls the Progress.Compelte().
  • The cancelCallback is what gets called when the user presses the Cancel button on the form.

By itself, this will do nothing. You will have to call the Progress.Update() command in order to move the progress bar, or update the message to the user. Here is the method signature for the Update method:

Progress.Update( [amount], [message] )

  • The amount is how much you want the progress bar to move. If you do not specify an amount, an amount of 1 is assumed.
  • The message is a new message to provide the progress bar. If you want to update the message and do not want to increment the progress bar, specify an amount of zero (0).

Once you are all done with the Progress dialog, you issue a Progress.Complete() call. There are no parameters to it. Once called, your completeCallback in the Progress.Show() call will then be executed.

Here is an example:

// reset first to make sure we get a fresh object
Progress.Reset();
// display a progress bar form and set it from 0 to 100
Progress.Show("Please wait while this happens...", 0, 100, function() {
    // once we are done - when your code
    // calls Progress.Complete()
    Alert.Show("All done folks!");
  }, function() {
    // this is only going to be called if the user cancels
    Alert.Show("The user cancelled");
});
doProgress();

function doProgress() {
  // increment by one, the result that comes back is
  // two pieces of information: Cancelled and Value
  var result = Progress.Update(1);
  // if we are not cancelled and the value is not 100%
  // we will keep going, but in your code you will
  // likely just be incrementing and making sure
  // at each stage that the user has not cancelled
  if(!result.Cancelled && result.Value <= 100) {     setTimeout(function() {       // this is only for our example to       // cause the progress bar to move       doProgress();     },100);   } else if(result.Value >= 100) {
    Progress.Compelte(); // done
  }
};

That example also uses the new Alert dialog. This one is very simple:

Alert.Show ( [message] )

This next example uses the new Wait dialog, which is much simpler to implement. Here is the method signature:

Wait.Show( [message], [showCancel], [cancelCallback] )

  • The message is the message you want to show the user. If you specify null, it will appear as simply “Please wait…”
  • The showCancel flag if set will allow the user to see a Cancel button.
  • The cancelCallback function is only valid if the showCancel option is true. When the user presses cancel, this function gets called.

When you are ready to close the Wait dialog, you issues a Wait.CloseDialog(). Here is an example:

  Wait.Show(null, true, function() {
    Alert.Show("The user cancelled.");
  });
  setTimeout(function(){
    Wait.CloseDialog();
    Alert.Show("Done!");
  }, 15000);

If you have some suggestions for some things you would like to see added to this library, please add a comment below or reach out to me on LinkedIn or Twitter. Some ideas I will be working on:

  • Allow you to call another dialog type without having the close the dialog.
  • A selection dialog, where you have a dropdown list of a listbox where you wan select (or multi-select) items.
  • An option to resize forms.
  • An option to use the message handler in your own custom form – minimal code

Automating Excel and Add-ins Don’t load

There is a common problem I see when automating Excel from an external .NET application:

You will launch Excel, open a workbook, perform work, and then try to close Excel and find that it remains in memory. You might even have code in your solution to try to re-use existing instance of Excel and find that it uses one of these “zombie” instances that do not have any of the add-ins loaded… and maybe an add-in your code relies on.

The issue is that Excel is not able to close because  the primary thread of your application is holding on to a COM reference. Even adding Marshal.ComReleaseObject() and GC.Collect() like this probably will not improve the situation either:

From my experience, and from what I have gathered is that until the thread is stopped, the COM reference will be locked. Not sure where the issue lies (EXCEL, COM, or .NET), but I have found a workaround to this. What you have to do is terminate your thread. But because you do not want to terminate your application, you need to create a NEW thread. So the following pattern is what I have found works:

 

 

Office JavaScript API Code Explorers

Recently while preparing an internal Chalk Talk on Office Web Add-in Development, a co-worker presented me with two links I had not seen before and I wanted to share them with everyone:

These Code Explorers are pretty cool in that they contain some common use code patterns that you might find useful in your projects.

excelCodeExplorerCapture

Unfortunately, there does not appear to be one for PowerPoint and or Outlook yet. But the fact they are there for Excel and Word is pretty cool.

PInvoke Object Extender

If you have done work in Office (especially Excel) and performed operations that look across all the cells in the UsedRange, you might note – if you do a performance analysis of your code – that you will see up to 100ms per Object Model touch, especially for properties and methods from the Cell object. The issue here are all the checks and balances in the Primary Interops as well as the weight of .NET through COM. To avoid it you can Private Invoke (PInvoke) the Excel application directly and .NET does two things for you:

  1. It keeps the function table for the private interface cached so it does not always have to keep going and getting it for you.
  2. It allows you to interface with the EXE directly, bypassing several layers.

The end result, is usually something like a 100ms to 3ms performance improvement.

To help with PInvoke operations, I developed the following class that EXTENDS the OBJECT class type:

/// <summary>
/// This class extends object with a set of methods to help
/// PInvoke into COM objects much faster.
/// </summary>
public static class ObjectExtender
{
public static object InvokeMethod(this object PobjItem, string PstrMethod, object[] PobjMethodParams)
{
try
{
return PobjItem.GetType().InvokeMember(PstrMethod, BindingFlags.InvokeMethod, null, PobjItem, PobjMethodParams, null);
}
catch
{
return null;
}

}

public static object InvokeMethod(this object PobjItem, string PstrMethod)
{
return InvokeMethod(PobjItem, PstrMethod, null);
}

public static object GetProperty(this object PobjItem, string PstrProperty, object[] PobjParams)
{
try
{
return PobjItem.GetType().InvokeMember(PstrProperty, BindingFlags.GetProperty, null, PobjItem, PobjParams, null);
}
catch
{
return null;
}
}

public static object GetProperty(this object PobjItem, string PstrProperty)
{
return GetProperty(PobjItem, PstrProperty, null);
}

public static void SetProperty(this object PobjItem, string PstrProperty, object PobjValue)
{
try
{
object[] LobjVal = { PobjValue };
PobjItem.GetType().InvokeMember(PstrProperty, BindingFlags.SetProperty, null, PobjItem, LobjVal, null);
}
catch
{
return;
}
}

To use this code, you would do something like this:

LobjCell.InvokeMethod("Calculate");
object LobjVal = LobjCell.GetProperty("Validation");
object LobjFormula = LobjVal.GetProperty("Formula1");

How to determine if an Excel Workbook is Embedded… and more…

When working with a Visual Studio Tools for Office (VSTO) project, you may want to have your add-in behave differently when the Active Workbook is inside an embedding. More specifically, the user has inserted an Excel sheet into a Word Document, for example, and has just double-clicked on it and you find that you need to detect this scenario. The trick is to know when you are in an embedded scenario and as it turns out, this is not easy to do. If you are an OLE guru, you might check the Container property on the Workbook Object:

However, this property always throws an Exception for Office Document types. It specifically calls out Internet Explorer, for a reason. This property does not work with OLE Embeddings in other Office documents. You can check the path length and as I have seen in many solutions hardcoding for “Workbook in…” but this fails for multi-language solutions.

What I found you have to do is access a series of OLE properties through C# to get the information you need. And as an added bonus, not only can you determine if Excel is embedded, but you can also get the Name of the class object it is embedded inside of and the Moniker / Filename of the file it is embedded in. I created the following Extension methods for Excel:

/// <summary>
/// EXTENSION METHOD CLASS FOR EXCEL
/// </summary>
public static class ExcelExtensionMethods
{
    [DllImport("ole32.dll")]
    static extern int CreateBindCtx(uint reserved, out IBindCtx ppbc);

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// Checks to see if the Workbook is embeeded inside of 
    /// another ActiveX Document type, sy=uch as Word or Excel.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static bool IsEmbedded(this Excel.Workbook PobjWb)
    {
        if (PobjWb.Path == null || PobjWb.Path.Length == 0)
        {
            try
            {
                // requires using Microsoft.VisualStudio.OLE.Interop;
                // and you have to manually add this to reference from here:
                // C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.VisualStudio.OLE.Interop.dll
                IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
                IOleClientSite LobjPpClientSite;
                // get the client site
                LobjOleObject.GetClientSite(out LobjPpClientSite);
                // if there is one - we are embedded
                if (LobjPpClientSite != null)
                {
                    return true;
                }
                else
                {
                    // not embedded
                    return false;
                }
            }
            catch (Exception ex)
            {
                // exception
                Debug.Print(ex.ToString());
                return false;
            }
            finally { }
        }
        else
        {
            // not embedded
            return false;
        }
    }

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// This method return the name of the class that we
    /// are embedded inside of.
    /// If we are not embedded it return null.
    /// If there is any exception it return null.
    /// If the container cannot be accessed it returns UNKNOWN.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static string EmbedClassName(this Excel.Workbook PobjWb)
    {
        try
        {
            IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
            IOleClientSite LobjPpClientSite;
            // get the client site
            LobjOleObject.GetClientSite(out LobjPpClientSite);
            if (LobjPpClientSite != null)
            {
                IOleContainer LobjPpContainer;
                LobjPpClientSite.GetContainer(out LobjPpContainer);
                if (LobjPpContainer != null)
                {
                    return LobjPpContainer.GetType().Name;
                }
                else
                {
                    // something wrong - container is not valid
                    return "UNKNOWN";
                }
            }
            else
            {
                // not embedded
                return null;
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return null; // failed
        }
    }

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// Get the full path to the file that the workbook is embedded 
    /// inside of. 
    /// If we are not embeeded then this will return null.
    /// If we are embedded but there are issues with the container
    /// or an exception occurs, it will return null.
    /// Otherwise we get the full path and filename.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static string EmbedMoniker(this Excel.Workbook PobjWb)
    {
        try
        {
            IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
            IOleClientSite LobjPpClientSite;
            // get the client site
            LobjOleObject.GetClientSite(out LobjPpClientSite);
            if (LobjPpClientSite != null)
            {
                IOleContainer LobjPpContainer;
                LobjPpClientSite.GetContainer(out LobjPpContainer);
                if (LobjPpContainer != null)
                {
                    // get the moniker
                    IMoniker LobjMoniker;
                    LobjPpClientSite.GetMoniker((uint)OLEGETMONIKER.OLEGETMONIKER_FORCEASSIGN,
                                                (uint)OLEWHICHMK.OLEWHICHMK_OBJFULL,
                                                out LobjMoniker);
                    if (LobjMoniker != null)
                    {
                        // now pull the moniker display name
                        // this will be in the form of PATH!Context
                        string LstrDisplayName;
                        IBindCtx LobjCtx = null;
                        CreateBindCtx(0, out LobjCtx); // required (imported function)
                        LobjMoniker.GetDisplayName(LobjCtx, null, out LstrDisplayName);
                        // remove context is exists
                        if (LstrDisplayName.Contains("!"))
                        {
                            string[] LobjMonikerArray = LstrDisplayName.Split('!');
                            // return the first part - which should be the path
                            return LobjMonikerArray[0];
                        }
                        else
                        {
                            // return full display name
                            return LstrDisplayName;
                        }
                    }
                    else
                    {
                        // no moniker value
                        return null;
                    }
                }
                else
                {
                    // something wrong - container is not valid
                    return null;
                }
            }
            else
            {
                // not embedded
                return null;
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return null; // failed
        }
    }
}

Pay close attention to the comments, because you will not find the OLE reference you need in the References window. You will have to browse to the path given and manually select it.

Now, to use it, I hook to the WorkbookActivate event, like this:

/// <summary>
/// STARTUP
/// </summary>
/// <param name="PobjSender"></param>
/// <param name="pObjEventArgs"></param>
private void ThisAddIn_Startup(object PobjSender, System.EventArgs pObjEventArgs)
{
    Application.WorkbookActivate += new Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);
}

/// <summary>
/// Workbook Activate event fires when any workbook is activated.
/// </summary>
/// <param name="PobjWb"></param>
void Application_WorkbookActivate(Excel.Workbook PobjWb)
{
    // call the extension method created below
    if (PobjWb.IsEmbedded())
    {
        string LstrClass = PobjWb.EmbedClassName();
        string LstrPath = PobjWb.EmbedMoniker();
        MessageBox.Show("This workbook is embedded in a [" + LstrClass + "] " +
                        "with a path of: \n\n\t" + LstrPath);
    }
}

What you can see from the code above is that on WorkbookActivate, I check to see if the workbook is embedded and if it is, then I get the Class Name by calling my other Extension Method – EmbedClassName() – and then I get the path to the file it is embedded inside of (for example, the Word document path that contains the embedded Excel sheet) by calling yet another Extension Method – EmbedMoniker().