EXCEL: Auto-Run Macros Disabled without Warning

This entry applies if your security settings in Excel are set to “Disable all macros except digitally signed macros” like this:

disabledmacros

In this scenario, when you open a workbook that is digitally signed, you will get a yellow bar across the top of the screen, that says: “SECURITY WARNING Macros have been disabled”, like this:

macrowarning

But if the workbook is NOT signed and you attempt to open the workbook with macros, you will not see anything, no warning at all. The workbook will appear to open, but all macros will be disabled. If you try to run a macro, you will get the following warning:

macrowarning2

However, the insidious thing is your AutoRun macros will also not execute and you will get no warning. If your code performs some type of data refresh of the spreadsheet, you might not realize this did not happen and what you will be left with is stale data. For many financial institutions this can be a scary prospect.

To workaround this problem, I have created an XLAM that can warn your users that they opened an untrusted workbook. You can access the source and the add-in both on GitHub: VBAProjectNotSignedWarning-Addin

What this add-in does is look for XLSM file to be opened, it then checks to see if the VBAProjectSigned property is false. If the workbook code is not signed, macros are assumed to be in the file, and the user will get the following warning:

certwarningaddin

The SOURCE is provided in the depot because for it to run properly in your environment, you might want to open the XLSM sign the VBA Project with your corporate code certificate, secure the project with a password, and then Save A Copy as an XLAM (Excel Add-in). Once created, you can then place the add-in in the XLSTART folder in Program Files for each user in your organization. Then when they try to open an unsigned macro workbook, they will get a handy warning message.

The code for this is fairly simple. I will not go into each module, just the main function that performs the check of each Workbook that is opened via the Application.Workbook_Open event:

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");