Office Add-Ins for Excel - New Features for VBA and VSTO Developers

    Background


    It all started about four years ago. Working on another project to automate business processes for a large Russian retail chain, I became interested in developing add-ons for office applications, in particular, for Excel. It took me several days to see how the employees of the customer company spend a lot of time on routine repetitive operations, I had a lot of ideas about how I could simplify their life.

    At that time, as developers, we had two ways to “expand” Excel for atypical tasks:
    • VBA (Visual Basic for Applications);
    • VSTO (Visual Studio Tools for Office).



    I think all the add-in developers for Excel are well aware of the advantages and disadvantages of both approaches. The big advantage of both is the very rich API that allows you to automate almost any task. The disadvantages include the difficulty in installing such extensions. This is especially true for add-ons based on VSTO, where, often, installation requires administrative rights, the receipt of which can be problematic for end users.

    For a number of reasons, the discussion of which is beyond the scope of this article, I chose the option with VSTO for myself. Thus was born our first add-in for Microsoft Excel - XLTools . The first version of the product includes tools that allow:

    • perform data cleaning in Excel cells (remove extra spaces and unprintable characters, bring the register to a single view, etc.);
    • convert tables from “two-dimensional view” to “flat” (unpivot);
    • Compare data in columns
    • tool to automate all of the above actions.

    Office Store Appearance


    Literally a year after the release of the first version of the XLTools add-in, we learned that Microsoft is launching a new platform for promoting extensions for Office - the Office Store. My first thought is - can we publish our new XLTools add-on there? Maybe unfortunately, but maybe fortunately, but the answer to this question is NO. Neither VBA nor VSTO add-ins can be published to the Office Store. But is it worth it to get upset? Fortunately, here is the answer - NO, not worth it. Next I will explain why.

    New Add-Ins for Office


    What is the Office Store and why do we need it? In short, this is a platform that helps users and developers search, download, sell and buy add-ons that extend the standard functionality of Office programs, whether Excel , Word , Outlook , OneNote or PowerPoint . Previously, end users had to look for add-ons they need in search engines, but now for this a single place has been created - the Office Store, access to which is possible directly from the interface of office programs. The menu item "Insert" -> "My Add-ons":



    As we already found out, it’s impossible to publish add-ins developed using VBA or VSTO in the Office Store. With the release of Office 365 and the Office Store , Microsoft has offered us a new way to develop add-ins using the JavaScript API for Office, which involves developing applications using web technologies such as HTML5, CSS, JavaScript and Web Services .

    The new approach has both advantages and disadvantages. The benefits include:

    • Easy to install add-ons from the Office Store;
    • Cross-platform out of the box (Excel 2013/2016, Excel Online , Excel for iPad );
    • The ability to use the accumulated experience of web development (there is no need to learn new technologies if the team already has web developers);
    • Ready-made infrastructure that allows you to sell add-ons at a fixed price or by subscription.

    Of the shortcomings of the new approach, I can single out only one, though so far, quite significant:
    • A less rich API compared to VSTO and VBA (I hope this problem will become less and less relevant with the release of new versions of the API).

    Development of add-ins for Excel "according to the new rules"



    So, where to start if we want to keep up with the times and not miss a new wave of applications for Office?

    There are two options. At the moment, we can develop applications based on the JavaScript API in:

    • Napa is a lightweight web version of the development environment for a quick start. It will be useful to developers who do not have Visual Studio, or those who want to develop under an operating system other than Windows;
    • Visual Studio , starting with version 2012, with the installed Office Developer Tools - a more powerful and functional development environment. Those who previously developed for VSTO can immediately start with this option, because They already have Visual Studio.

    In this article, we will consider development using Visual Studio, because I myself use it. If you are interested in trying Napa, then familiarize yourself with this tool and start working with it here .

    Before starting development, you should also pay attention to a couple of significant differences between VBA / VSTO add-ins from add-ins for the Office Store:
    • The first difference is that when developing add-ons on VBA or VSTO, we could create so-called “package” products, which included a number of functions. XLTools is a great example - the add-in includes many options for working with cells, tables, columns, etc. When developing add-ins for the Office Store, you'll have to forget about this approach. When planning development, we should think about what kind of complete, isolated from each other functions we want to provide to end users. In the case of XLTools, those features that were originally implemented in one add-on are now represented by five separate applications in the Office Store. This approach allows you to make decisions more narrowly focused and increase the number of downloads of add-ons by targeted users;
    • The second difference is the difference between the JavaScript API and the VSTO / VBA API. It is worth exploring in detail the features provided by the JavaScript API. To do this, I advise you to use the Microsoft Tutorial (Task Pane) and Microsoft Tutorial (Content) API applications.

    Excel Add-in Development Using Visual Studio and JavaScript API


    By default, Visual Studio has predefined project templates for developing add-ins for the Office Store, so creating a new project takes literally seconds.



    The project itself consists of a manifest file and a website. The manifest file looks like this:
    11111111-1111-1111-1111-1111111111111.0.1WavePoint Co. Ltd.en-USReadWriteDocument


    The main things to note in this file are:

    • Id - must be unique for each application;
    • Version - must match the version specified when the add-in is published via Seller Dashboard (the personal account of the vendor / developer through which all the add-ons published in the Office Store are managed);
    • IconUrl and SupportUrl - links should be working and indicate the location of the logo image and the page with the description of the add-in functionality. If the links are incorrect, the manifest will not pass validation when publishing through Seller Dashboard;
    • Permissions - determines the access level of the add-in to the document data. It can take such values ​​as Restricted, Read document, Read all document, Write document, Read write document;
    • SourceLocation - the path to the "home" page of the application on the website.

    The website consists of the minimum set of HTML, JavaScript and CSS files necessary for the application to work, and by default provides a basic UI based on which we can build a UI for a new solution. It is worth noting that one of the requirements for the site is working on HTTPS . This means that if you publish the site on your own servers or on your own domain, you will need an SSL certificate. If you plan to use, for example, Azure Website, this problem can be avoided, because all sites deployed on the azurewebsites.net subdomain are accessible by default using both HTTP and HTTPS.

    The JavaScript API provides a standard set of methods for interacting with Excel data. I’ll give examples of the use of only a few, among those that we used when developing the XLTools.net Data Cleanup add- in :

    • Adding a “binding” to a cell range selected by the user in Excel for further work with them:

    Office.context.document.bindings.addFromPromptAsync(Office.BindingType.Matrix, {
                id: "RangeToClean"
            }, function (asyncResult) {
                if (asyncResult.status == "failed") {
                        // Some specific code here
                }
                else {
                        // Some specific code here
                }
            });
    

    • Retrieving data from a range of cells using a previously created “binding”:

    Office.select("bindings#RangeToClean", onBindingNotFound).getDataAsync(
                {},
                doDataCleaning
            );
    

    • Updating data in a range of cells using the previously created “binding”:

    Office.select("bindings#RangeToClean").setDataAsync(range,function (asyncResult) {
                if (asyncResult.status == "failed") {
                        // Some specific code here
                }
                else {
                    app.showNotification(UIText.ExecutedSuccessfully, '', 'success');
                }
            }).
    

    All JavaScript API methods are well-documented; their detailed description can be found on the MSDN website .

    Depending on the scenario, data processing can occur directly on the client, i.e. in JavaScript code and on the server. To process data on the server, you can add the necessary services directly to the site, for example, using the Web API. The client (add-in) communicates with web services in the same way as we used to do it on any other site - using AJAX requests. The only thing to consider is if you plan to use third-party services located on foreign domains, you will certainly encounter the same-origin policy problem.

    Publish an add-in to the Office Store


    To publish an add-in to the Office Store, you need to register on the Microsoft Seller Dashboard website . After registration, you will get access to your personal account, where you can download the manifest of your application and fill out all the necessary information about it. Based on personal experience, I can say that checking the application after sending it for approval usually takes from one to three business days. After Microsoft has tested and approved the application, it becomes available for download to millions of users around the world through the Office Store:



    conclusions


    In conclusion, it’s worth saying that the XLTools add-ons are a great example of how you can transform existing solutions based on VBA / VSTO technologies into cross-platform solutions for Office 365. In our case, we were able to transfer a good half of the functions from the Desktop version of XLTools to the Office Store, by implementing six separate applications.

    All of them are currently available for download through the Office Store:


    I would also like to note that in addition to the usual scenarios, with the advent of the Office Store and Office 365, we, as developers, have new opportunities for developing extensions using the Office 365 API , which allows access to data from services such as Mails , Calendars , SharePoint Online , OneDrive for Business , etc. Who knows what we can build tomorrow using these opportunities. Time will tell!




    about the author


    Petr Lyapin - Technical Director of Wave Point LLC

    Over 10 years of experience in implementing projects to automate
    business processes. He worked with many Russian and
    foreign companies. Founder of the XLTools.net project.

    Also popular now: