Roadmap for Apps for Office, VSTO, and VBA

With all the buzz around the new apps for Office programming model, developers might be wondering: What benefits does the new app model bring, and which of the three technology choices―apps for Office, managed VSTO add-ins, or VBA macros―are best suited for particular scenarios?

Apps for Office is a recognition that the technology landscape around us is changing: that ubiquitous connectivity, mobile devices, powerful and personalized cloud services, real time collaboration, and social are fast becoming the norm, and that a new app model must be brought forth to capture these evolving needs. At the same time, however, it’s important to note that all three technologies will be supported in the foreseeable future. This means that if you have an existing VBA or VSTO project, and if you’re satisfied with the capabilities, tooling, and marketability of your existing solution, you can be confident that your investments are safe. However, if you’re looking to expand the exposure of your product or service to a larger audience and allow for greater monetization opportunities, apps for Office can be a great option to start looking into. This post will guide you through the strengths of each of the three technologies, and suggest some factors by which to choose which Office customization approach is right for you.

Apps for Office

The newest addition to the Office developer toolbox, apps for Office are a new way for users to interact with Office customizations. Instead of having to install add-ins or enable macros, users can download, install, and use apps alongside a document, message, or appointment straight from the Office Store or from an internal corporate app catalog, and those apps run in a protected sandbox environment. Built with web technologies, apps for Office are particularly well suited for creating web mash-ups and using Office as a surface for exposing existing web functionality. Apps also offer lifecycle management capabilities, such as distributing or removing apps for the users in your corporation, managing updates, and tracking telemetry.

Figure 1 shows a content app and a task pane app embedded within an Excel document. The apps expose the existing web services of Bing Maps and Merriam-Webster, respectively, surfacing these through a convenient app that interacts with the user’s Excel environment.

Figure 1. Apps for Office: A content app and a task pane app embedded within an Excel document

The friction-free ease of distribution is a huge draw for apps for Office. Additionally, the Office Store is designed to make it easy for you to monetize your apps. Another neat aspect is that apps for Office can be used both in the Office 2013 and in a growing selection of Office Web Apps as well. For example, a mail app can use the same JavaScript API calls both for the desktop Outlook client and for Outlook Web App, spanning a breadth of devices. Most importantly, due to the web-based approach of the apps for Office model, developers can use their existing coding skills to develop apps with virtually any web programming technology, and apps can connect to virtually any backend data store. This makes the transition to the apps for Office model easier and smoother for web developers, allowing folks to re-use their existing web services and web development skills.

The sweet spot for apps―connecting rich web content and services contextually with Office―offers great public exposure and a host of deployment and web-technology benefits. The additional benefit that is exclusively available with apps for Office is the potential to monetize your app by making it available for purchase in the Office Store. Additionally, companies can lock down access to any internally-built apps by using a corporate app catalog that is only accessible to internal users. These two features offer great potential for both app distribution and IT management that has not been available to Office developers and enterprises in the past.

Managed add-ins built with Visual Studio Tools for Office (VSTO)

Visual Studio Tools for Office uses the .NET Framework to customize and extend Office. VSTO offers the full language support of C# and Visual Basic, and can therefore leverage the same frameworks, tools, and programming paradigms as the rest of the .NET Framework. VSTO also provides very tight integration with the Office client applications, both in terms of the rich sets of APIs, and the customizability of the user interface.

The following figure shows a VSTO solution running in PowerPoint. Notice the customized ribbon, the custom task pane (built with Windows Presentation Foundation), and a free-floating Windows Forms window.

Figure 2. A VSTO solution running in PowerPoint

For developers and businesses that need to leverage more extensive customizability of Office, or that need to target Office 2007 or 2010 (apps are only available starting in Office 2013), VSTO is the primary option. In addition to UI customization, VSTO excels at automation scenarios, such as adding or modifying shapes or charts, manipulating or merging documents on the user’s behalf, and interacting with other programs or resources on the host computer. Of course, not all automation-like tasks require automation: inserting a new paragraph with formatting, for example, could be done with VSTO through automation, or it could be done with apps for Office by writing out an Open-XML formatted string. The exact technology choice, and the suitability of using apps for automation, would depend on carefully analyzing the business requirements and the user workflow for the application.

VSTO is not lightweight, however. Deploying a VSTO add-in requires users to install the application, much as they would install any other desktop application. This often requires that the IT organization be involved in determining how safe the add-in is to the existing corporate environment, as well as often requiring that IT determine the upgrade path of this add-in for future releases of Office, and other application and environmental dependencies. This can lengthen time-to-deploy and create maintenance dependencies for the IT and business organizations. It is also important to note that VSTO add-ins run with the same security privileges as any other desktop program. This is a double-edged sword: it allows VSTO to use the power of the host computer and interact with the file system, which might be necessary for working across documents or interacting with external programs; but it also means that a malicious add-in could compromise the computer security. Even a well-intentioned add-in might affect the performance or stability of the host Office application, leading to slower startup or application failures. Ultimately, it’s up to the user (or IT admin) to ensure that an add-in is trustworthy before installing it. At the end of the day, VSTO is a very powerful tool, but this power does come at a cost.

Visual Basic for Applications (VBA) macros

VBA and the Visual Basic Editor is a tool that offers an in-product experience for automating Office client applications. Rooted in “classic” Visual Basic 6, and with support for a macro recorder in Word and Excel, VBA offers a simple onramp for writing an Office automation task to get the job done. However, as a decade-old technology, VBA is also more limited in terms of UI customizability and the overall tooling and framework support.

Figure 3. The VBA programming environment (built on “classic” Visual Basic 6) that is included with Office

While application-level solutions are possible in VBA, VBA code is often stored and distributed directly in a document. This makes initial deployment and distribution very easy, but makes updating code within any existing documents much more difficult. Office documents are often emailed and duplicated, so there may have hundreds or thousands of documents based on the same VBA macro. Updating the code to improve features or to fix bugs would require that the Office artifact be re-emailed, re-structured, and re-worked by every single user and in every single file that has been using the customization. Within a corporate environment, this can create IT management and governance issues, as there is really no way to manage the proliferation of copies of the documents among users.   VBA application-level solutions can avoid this issue, since they are not tied to a particular document; but, in so doing they also lose out on the ease of initial distribution as they need to be installed to a particular folder, which can be a challenge for some users. (Note that in a business environment, the IT department can automate the installation via Group Policy).

In summary, VBA is the “classic” option for creating Office solutions, usually aimed at an internal/departmental set of audience. Writing a 10,000-line program in VBA, or selling the program as a standalone product, is more of a stretch.

Here is a comprehensive look at the choice of tools, across a broad set of categories:

*: Indicates a dimension where support might be partial today, but where future investments are expected to be made.

**: Relies on standard web offlining capabilities (for example, AppCache, Web Storage, page caching) supported by the browser.

Summary

Given the increasing array of choices―apps for Office vs. add-ins vs. macros―it is important for developers to know the value of each tool. Add-ins and macros are existing forms of solutions, aimed at solving existing needs for existing users, and will continue to be supported on the desktop for the foreseeable future. Apps for Office, meanwhile, come with a forward-looking platform, aimed at bringing web services and web technologies closer to Office developers. For add-ins and macros, Office is a highly-customizable, but siloed platform; for apps, Office is more of an integrated surface for exposing loosely-coupled web functionality using the web technology of your choice, with the potential for monetization, IT governance, and ease of lifecycle management and deployment. Both approaches have their merits, with add-ins and macros providing robust interaction and automation of existing Office client applications, and with apps for Office better-suited for creating easily-deployable, web-based solutions that work across a growing base of platforms including desktop, web and mobile. In cases where multiple approaches are equally possible, apps for Office are a natural choice for new projects.

Just like comparing Windows 8 apps with traditional Win32 desktop applications, or redesigning traditional websites for mobile devices, apps for Office represent a paradigm shift from add-ins and macros. In many cases, converting an existing managed add-in to the new apps for Office model would require significant re-imagining of the application. Thus, instead of thinking of a 1-for-1 conversion, it might be more fruitful to enable a few key scenarios using apps for Office, and see where that leads. Perhaps those key scenarios are enough to gain business on their own; or perhaps the re-imagined app can lead to entirely new scenarios and services, based on the cloud-optimized and multi-platform nature and the huge potential of the new app model. The capabilities of the new app model will continue to evolve, so even if your scenario does not fit the scope of the new app model today, keep an eye out for the new features of tomorrow.

Happy coding―with whatever Office technology(ies) you choose! Please leave a comment if you have any questions.

[July 15, 2013] “Visual Basic for Applications (VBA) macros” section updated

―Michael Zlatkovsky | Program Manager, Visual Studio Tools for Office and Apps for Office

Special thanks to Sonya Koptyev, Jim Nakashima, Rolando Jimenez Salgado, Sean Laberee, Angela Chu-Hatoun, and Sudheer Maremanda for their input into this post.

Comments

• Anonymous
  July 09, 2013
  Hi Michael, FYI, there is no macro recorder in Access.  Never has been.  There are a few wizards that generate code, but all other code we just write ourselves. Also, VBA "macros" shouldn't be confused with Access Macros, which represent a completely different syntax and editor.

• Anonymous
  July 09, 2013
  Good blog, nice synopsis. However, where is this "macro recorder" in Access? And macros and VBA are different in Access, unlike Word and Excel.

• Anonymous
  July 17, 2013
  @Armen and Scottgem, thanks for pointing this out -- we have corrected the VBA section of the article.

• Anonymous
  October 14, 2013
  You've missed out the VBA Add-in. Yes, document level macros have problems with versioning and are suitable for trivial macros such as print buttons. But all the power of the Excel object model is available to XLAM addins without the heavy overhead of learning VSTO and new languages.

• Anonymous
  October 28, 2013
  Nice write up. It will be interesting to see IRM dimension of Office Apps vs VSTO in the conext of 2010 and 2013 docs especially with Azure AD RMS

• Anonymous
  November 14, 2013
  The comment has been removed

• Anonymous
  January 06, 2014
  The comment has been removed

• Anonymous
  January 08, 2014
  @ab - Thanks for the feedback.  You're right, there are definitely various objects that are accessible through various technologies that are used to extend Office.  As we continue on the journey in the extensibility model for Office, we'll see more changes, but more opportunities also opened up in other areas of the products that make new scenarios and integrations possible. @mike - Thanks for the suggestion.  I'll pass it along to the engineering team and see if it sticks.  However, in the meantime, we are going to have lots of great training sessions during a pre-day at SharePoint Conference this March (sharepointconference.com) where we'll be going through all of the latest and greatest languages and tools to give developers a jump start into the app model.  

• Anonymous
  January 22, 2014
  will microsoft buy ExcelDNA eventually ?

• Anonymous
  January 22, 2014
  What is the simplest way to make a UDF along with a tab panel, have access to the Powerpivot datamodel, and have light deployment story ?

• Anonymous
  January 30, 2014
  I have a similar concern to that of Nicolas. I would like to use Office development tools (other than VBA) to create User-Defined Functions for Excel, preferably using C#. For all the improvements Microsoft has made in development tools for Office products, Excel UDFs still remain a challenge. The existing choices to create one include: using VSTO (which requires some extra work and is still a challenge to install on other computers), using old-style C or C++ to create an XLL (which requires quite a great deal of extra work), creating a DLL that is called by VBA, or using a third-party library such as Add-In-Express or ExcelDNA. Are we ever going to see any improvements in the area of Excel UDFs?

• Anonymous
  March 15, 2014
  Nice

• Anonymous
  April 01, 2014
  Just like with VB6, Microsoft seem to be moving away from the technology people wish to use - VBA- to incompatible technologies - C#, the incompatible VB.Net or JavaScript. Keep VBA, add a VBScript like language to apps for office, and bring back an updated VB6. visualstudio.uservoice.com/.../3440221-bring-back-classic-visual-basic-an-improved-versi

• Anonymous
  June 21, 2014
  I agree with mike and Trust VB... VBA still very popular for thousands developers

• Anonymous
  October 16, 2014
  MS Access seems to be now split in 2: -Web app mode where we can hardly do anything (macros that cannot do anything serious) -Client App that has no access to App model (so no possibility to transition to a newer programming model Is Microsoft aim to discard Access and send programmers to Visual Studio only?

• Anonymous
  February 25, 2015
  The comment has been removed

• Anonymous
  March 29, 2015
  Yeah, VBA is still hugely popular - and not just for financial institutions who use Excel VBA. It is often a non-programmer's entry into the programming world. At the very least, make it a better/modern experience. It's not going away, despite MSFT's neglect. Embrace it.

• Anonymous
  April 14, 2015
  The comment has been removed

• Anonymous
  April 30, 2015
  At last, the Microsoft Office will be the same with Google Office (spreadsheet ....)? Without the powerful VBA or .Net's support, I think the office will lose its advantage to face the challenges from google, open office, wps .... For office, web is really not so important. The cloud is everywhere now, we can share the document everywhere, why should we edit the document online? Can I access a local database using the office app?

• Anonymous
  August 01, 2015
  The statement below is simply false, and I was surprised to read it in an MS product brief.  I've managed VBA apps for years, and governance/currency is achieved by including startup code that checks versioning against information from a secure read-only public share.  If the code is out-of-date, simply stop processing and initiate some type of update procedure. You really need to vette these kind of topics with the development community before publishing. Thanks - Dave " This makes initial deployment and distribution very easy, but makes updating code within any existing documents much more difficult. Office documents are often emailed and duplicated, so there may have hundreds or thousands of documents based on the same VBA macro. Updating the code to improve features or to fix bugs would require that the Office artifact be re-emailed, re-structured, and re-worked by every single user and in every single file that has been using the customization. Within a corporate environment, this can create IT management and governance issues, as there is really no way to manage the proliferation of copies of the documents among users. "