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.

UpgradeAppForSP_fig01

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.

UpgradeAppForSP_fig02

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.

UpgradeAppForSP_fig03

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:

                             

Comparison Dimensions

Apps for Office

 

VSTO

 

VBA

Automation and interaction with the host computer

 

a

 

a

Interaction with the web

a

 

a

 

 

User Interface customization

partial *

 

a

 

partial

Interaction with the Office client object models

partial *

 

a

 

a

Offline Availability

partial **

 

a

 

a

Support for latest tools and technologies

a

 

a

 

 

Support for team development and source-control

a

 

a

 

 

Ability to target multiple host applications with one codebase

a

 

   
Ability to run code at application-level, across documents  

a

partial

Security and sandboxed environment

a

 

   

Ease of distribution, lifecycle, and telemetry

a

 

   

Built-in monetization opportunity

a

 

   

Cloud and Desktop Compatibility

Office 2013+, and web-based O365 clients

 

Office 2007+ (desktop only)

 

Office 2000+ (desktop only)

*: 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.