Office Scripts: The future is here

Microsoft has been working hard to make Office available on every platform. One of the biggest changes has been the ability to run Office the a browser or Office online. However, one drawback has been that automation in Office online has been limited to Web Add-ins which are aimed solidly at professional developers. However, Visual Basic for Applications (VBA) macros are only supported in Windows and Mac clients. They are not supported on the web platform, the iOS or Android platforms. And, well, they still are not.  wlEmoticon-disappointedsmile.png  However, something new has arrived which will allow you to create “macros” anywhere, on any platform (eventually).

Microsoft recently announced Office Scripts. It is an end-user approachable, web and collaborative supported scripting language. It is still in preview and only supported in Excel online, for now.

First, to access this you must have your administrator enable it in the Office 365 Portal:

turn_on_scripts

For citizen programmers this line from the above page says it all:

Scripts allow you to record and replay your Excel actions on different workbooks and worksheets. If you find yourself doing the same things over and over again, an Office Script can help you by reducing your whole workflow to a single button press.

So, I enabled it in my test tenant and created the quintessential “Hello Wold” Action Recording. When your administrator enables this on your tenant, you will get this new Automation tab:

AutomationTab

From there, I clicked on Record Actions, typed “Hello World!” in cell A1, and then stopped the macro. It then asked me to name it and give a description (optional), and then Save. From there it opened the Code Editor and here is what I see:

Once you open a new workbook, you can then click on the Code Editor button and you will see all your recorded scripts:

recordings

The future is here. Check it out now. You can learn more about Office Scripts from here: https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-tutorial.

 

What is Office 365, really?

In my day-to-day dealings with people and organizations, there is a common confusion on the term “Office 365.” And that confusion is fed by a few common myths that I have heard repeated by some very smart people in those organizations. So, first, let me dismiss the myths…

  • The first one is that Office 365 is online, in the browser applications only… NO! Office 365 is NOT just Excel Online, Word Online, PowerPoint Online, Outlook Online, and OneNote Online.
  • And the second myth is that the desktop applications are getting deprecated… NO! Microsoft is NOT doing away with the installed desktop applications on Windows and Mac in favor of web based versions.

So let’s get to the question:

If Microsoft is not replacing Office with web versions, then what the heck is all this Office 365 and cloud stuff then?

Primarily, Office 365 is the name of the subscription. You no longer buy the Office applications on disks and install them, then buy the next version, etc. With the subscription you always get the newest version (and much more). As you will read from the link above, there are lots of FAQ’s and other information that explain how this works.

So Excel Online, ET AL. is PART of the Office 365 subscription based platform. Office 365 still includes the traditional Microsoft Office desktop applications on the PC/Windows or the Mac. And they are not going anywhere. When you hear that your organization is moving to Office 365 it does not mean you will ONLY be accessing Office from a web browser.

Office 365 is really Office anywhere, on any device. For example, Office 365 also includes applications on iPhone, iPad, and Android. And it is not just the traditional applications. There are incredible tools such as Forms, Sway, Teams, and for developers the Graph API. It also shifts workloads like Exchange Server for mail and SharePoint server for document management from local IT servers to servers run by Microsoft in the cloud.

So when you hear Office 365, DO NOT THINK GOOGLE DOCS. wlEmoticon-smile.png The online versions of Office are only there to provide an additional avenues to access Office from anywhere, on any device. Per the Office 365 website:

Works across multiple devices

Get the fully installed Office apps on multiple PCs, Macs, tablets, and mobile devices (including Windows, iOS, and Android).

They are not as fully featured and as such you really need to consider when you might use them over using the traditional Microsoft Office desktop applications. Per this site (my emphasis added):

Office for the web (formerly Office Web Apps) opens Word, Excel, OneNote, and PowerPoint documents in your web browser. Office for the web makes it easier to work and share Office files from anywhere with an internet connection, from almost any device. Microsoft Office 365 customers with Word, Excel, OneNote, or PowerPoint can view, create, and edit files on the go. …

The following tables compare Office for the web feature capabilities to feature-rich Microsoft Office desktop apps.

So, if you like your Excel just the way it is, installed on your scientific workstation with a bazillion gigabytes of RAM, you can still run it there if you move to Office 365. What you get with the traditional Office applications is more frequent, seamless and automatic updates from the web. Per the Office 365 website:

Monthly Updates

Get the latest features and capabilities with fully installed and always up-to-date versions of Outlook, Word, Excel, PowerPoint for Windows or Mac, OneNote (features vary), Teams, and Access and Publisher (PC only).

Hopefully, this has helped clear up and dispel the myths. And when you go back to the smart person that has told you otherwise, and continues to insist and tell you otherwise, please send them here. wlEmoticon-hotsmile.png

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: