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.
Thank you for your explanation. You are correct that VBA Application-level addins need to be installed: like any other program.
This can be done in many different ways ranging from a full Setup/Install package exe to a simple Excel workbook that contains the install code. A good solution is to install the application-level addin to a network share for a group of users. If required a simple stub loader can be all thats installed on the users systems to add more sophistication and control and to simplify things like easy roll-backable reversioning, network share isolation and load-on-demand.
None of this requires that application-level VBA addins be installed to a particular location or need present a challenge for users.
@Charles: Thanks for your comment, and yes, I absolutely agree -- there are many ways to distribute an application-level VBA macro. All I tried to convey, and perhaps I could have chosen better wording, in the article is just that there is a tradeoff in installation complexity between doc-level and app-level. The document-level case is obviously the simplest one (no installer required), but then the code is not in one centralized location; alternatively, you can get the code centralized via an installer or network share, but it does take explicit action on the part of a user/admin (along with complexity like what to do for users who do not have an always-on network connectivity, how do you distribute updates, etc.) This is no more complex that with VSTO, but it is more complex than the doc-level approach or the apps for Office approach.
Also, in the case of installers (unless it’s just an departmental add-in created & managed by IT), you have to take a leap of faith in running the installer and trusting that the code won’t -- intentionally or unintentionally -- compromise the security of your computer. Again, this is no different than VSTO (or normal desktop applications, for that matter), but the sandboxed security model *is* one of the benefits of apps for Office.
@Gary, thanks for your comment. Fair enough, a template is a good medium, though again, it requires distribution – it’s not as easy as just emailing someone a document. See my reply to Charles where I tried to elaborate on this a bit more.
You are absolutely correct than many existing scenarios are (for now, anyway) are best served by VSTO/VBA. These technologies are mature, well-established, and run the business & automation needs of countless companies around the world – they are not going away anywhere. We understand the value of these tools, and I assure you that your investments in VSTO/VBA are quite safe, even as we seek to enable additional new scenarios and new web-based capabilities with apps for Office. In cases where, for your scenario, apps for Office versus VSTO/VBA seems like an apples-to-oranges comparison, there is nothing wrong with going for oranges!
Hope this helps,
Thank you so much for your article. Its indeed very helpful. In terms of "*" partial support, where can I find how much support is there for a specific functionality for Office Apps. Specifically, is it possible to customize ribbon using office apps? Thanks much.
"But what a shame that Microsoft depreciated VB6 for 7 years ago. It was the perfect RAD to build unmanaged COM add-ins with."
A shame indeed. And now, in April 2014, VB6 has risen to the #6 most popular language in the Tiobe index.
At least VB6 has "It just works" support until 2023 and runs fine on Windows 7 and Windows 8.x
"I have wondered for a while why a new version of Classic VB was not created based on VBA7 with 64-bit support."
Exactly so, VB6 actually hosts VBA6 in its' IDE. So why not update it to use VBA7 ?
Vote for an updated VB6:
I think the part that is missing from this entire discussion is, what do end users and companies that buy the product want? Great I can now put a map in my spreadsheet. How does that help me improve the data entry, calculations, reporting, and analysis. My boss does not care about a map while looking at a monthly report. What makes Excel a great product is the ease at which users can modify and improve things to meet their own needs. I can use VBA to save time, improve accuracy, and expand capabilities. This adds value and instills confidence with management.
Rather than tell the customer what we want (Windows 8), carefully consider what the customer is telling you and then figure out a way to deliver that. The end goal should be to attract new customers while keeping existing ones. Add the new Office App features if that makes sense but it should be in addition to not as an eventual replace of the tools and methods that have made Office a winner.
I realize the Apps are in response to Apple and Android stores but why not expand the store to sell other products created with Visual Studio or VBA? Rather than stop improving VBA and VSTO (dictating what users want), expand these and even make them easier for the average person to use. This should build user loyalty instead of making us feel that Microsoft has changed up or abandoned yet another product.
:) Bastante bien
Thank you for the details. In our case, we extensively used VSTO and created a framework which creates workbooks for different processes. All the common functionalities/code are in the framework and on individual workbooks we write less custom code depends on the individual business process requirements. Our finding is that, VSTO provides powerful programming control to manage Excel automation very well from .NET. Please help me understand how VSTO is going to evolve OR let me know are there plans to get the apps for office equivalent to VSTO in terms of programming capabilities? Your guidance will be helpful. Thank you in advance.
I have a problem with a app.
This app is done with vsto and c# and openxml 2.0, this App works fine with MS Word 2010, but with MS Word 2013 not.
When Open the Word Document in Office 2013, i select a text and change me the language in spelling check, From Spanish to English.
Please help with that, already i have done:
- In code behind, i assigned <<Object>>.Range.LanguageID = WdLanguageID.wdMexicanSpanish; and
<<Object>>.Range.NoProofing = true;
- In the template, i assigned the language to the spelling check
Thanks in advance
The best place for these sorts of questions is on the VSTO forum: social.msdn.microsoft.com/.../home
Re: VBA distribution and code life cycle maintenance addressed
"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".
It doesn't have to.
We (IT) create XL workbooks that reside in read only directories with BeforeSave canceled (mainly to prevent annoying messages on close). The workbook contains what amounts to "Presentation Layer" and "Business Rules" but no data. Data is stored in databases. Thus, there is nothing for users to save with the workbook even if they could.
This means we have one and only one place where production code resides. When we promote versions to production we move the production workbook to "Previous Versions" directory and "QA Passed" workbook to production directory. If we have to we can promote versions to production while users have them open because there are no locks on the read only object. No locks also negates "Workbook open by another user" messages when multiple users access the same workbook.
This is nearly identical to how all code, regardless of language or platform, is handled within our organization. It seems simple and works well for us.
What does a mean in the table?