Develop Office Client Applications using Visual Studio
Note: This post is simultaneously published both to the VSTO blog and to the apps for Office blog. To learn more about apps for Office, visit http://dev.office.com.
Note: This post is simultaneously published both to the VSTO blog and to the apps for Office blog. To learn more about apps for Office, visit http://dev.office.com.
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.
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 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.
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.
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:
Apps for Office
Automation and interaction with the host computer
Interaction with the web
User Interface customization
Interaction with the Office client object models
Support for latest tools and technologies
Support for team development and source-control
Ability to target multiple host applications with one codebase
Security and sandboxed environment
Ease of distribution, lifecycle, and telemetry
Built-in monetization opportunity
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.
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. (You may also want to check the apps for Office cross-post to see other users’ comments).
―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.
[July 15, 2013]: Updated VBA section.
Concerning the table item "Ability to run code at application-level, across documents" for VBA: Word, Excel and PowerPoint all support add-ins (documents with VBA loaded as add-ins) that run at the application level, across multiple documents.
Good news !
What's about Excel SDK for XLL ?
It's indeed good news that Microsoft now officially announce its long term support of VBA and VSTO. For the last couple of years we have been living in the darkness about Microsoft's stand point. So it's great that You shed some lights to it.
I like the idea to see the different technologies as complement to each other and with Apps for Office another tool can be added to the toolbox.
But what a shame that Microsoft depreciated VB6 for 7 years ago. It was the perfect RAD to build unmanaged COM add-ins with. But done is done and I hope more office developers start to use VSTO and Apps for Office.
I am happy to stick with VBA for now but... Any chance you could update the look of the basic Forms 2.0 controls at some point? The rather dated and ugly controls look very out of place, especially in Office 2013.
I have wondered for a while why a new version of Classic VB was not created based on VBA7 with 64-bit support.
BTW, correction: Cloud and Desktop Compatibility should be Office 2003+ for VSTO and Office 97+ for VBA.
@Yuhong: VSTO was indeed available in Office 2003, but to a much more limited extent than in 2007 and above. Also, solutions targeted at Office 2003 could *only* run on 2003 -- whereas solutions targeting Office 2007 can run on Office 2007, 2010, and 2013 with no code modifications. So while VSTO *could* run on Office 2003, by and large its support and its traction has been in Office 2007 and above.
What *is* an app ? How is it encapsulated and if I write c# and compile it, how to i distribute it
directly like an .xll or a .dll ?
Silverlight is encapsulated as an .xap.
what is an app file ?
all this abstraction makes me dizzy.
@ dr d b karron,
A VBA customization is typically code that's embedded in a document, and so gets distributed as part of the document. (Note, however, that VBA can also create application-level add-ins).
A VSTO Add-In is something that can either be packaged as a setup.exe file, or distributed via ClickOnce. But in either case, the underlying mechanism is still a ".vsto" manifest and a localy-installed .dll that runs the code.
An app for Office is just an XML manifest that gets embedded in the document when the user inserts the app. The manifest points to a website, where the app's web content is hosted. See msdn.microsoft.com/.../jj220082(v=office.15) for more information.
Hope this helps,
Not sure what you mean by "VBA application-level solutions need to be installed to a particular folder" ?
For Excel Application-level addins that is not the way I install them.
With the advent of Office.js and no major changes to VSTO in a while, does this mean that VSTO smells like Silverlight?
"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"
Nonsense. an add-in can be placed in any folder the user has access to.
Jan Karel Pieterse
@Jan Karel Pieterse
Jan, thanks for your comment. Let me clarify what we meant: there would need to be some up-front configuration to specify that the add-in needs to get loaded, correct? And on subsequent updates to the add-in, the updated add-in would need to be re-deployed to the same folder. The process is certainly simpler than it would be if each document held its own copy of the code (that can become quite difficult to manage), but it is not as seamless as, say, updates to apps for Office (which are essentially a website, so any updates are immediately dispatched to the user, with no action required on the user's part).
Does that make sense?
This description of the drawbacks of distributing VBA solutions:
"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. "
- seems off the mark with regard to both Word and Excel. In Word the preferred medium for deploying a macro-based solution would be a template, not a document. So the template and code would only need to be deployed to one location, and any subsequent updates to the code need only be made to the template; once an updated version of the template is deployed, the updated code then automatically becomes available to all documents that were created based on the template.
In Excel, the preferred medium for deploying a macro-based solution would be an add-in, as Charles and Jan Karel have alluded.
I hadn't fully read your reply to Jan Karel (in which you do address some of the issues raised in my first note) before I posted. But still worthwhile pointing out that Word templates were left out of the original discussion.
Even if updates for apps for Office are easier to deploy, isn't there some degree of apples and oranges here?: apps for Office aren't a substitute for many of the kinds of automation and customization that are available via VSTO or VBA. And at least in terms of the Office web apps, customization isn't an option anyway.
For the many businesses that make use of customized features in Office, use of the full desktop version of Office, and developing solutions using VSTO and VBA, seems likely to remain in demand for some time to come.