Developing For Office – Options and Trade-Offs
Some of you have been asked to develop applications to develop on and extend Excel, Word, etc. What is the best approach given the tools Microsoft has to offer? Read this blog entry and find out.
Notice that you have three main choices. Each of these choices has pros and cons. I’d like to see you make the best choice. The devil is in the details and I will attempt to address the tradeoffs.
Traditional VBA has been around since day one. This dates back to all the way to the early nineties.
VSTO really hit its stride and office 2007. It probably represents the best option moving forward today. It tries to be all things to all people.
The COM Add-In is probably the most powerful, yet the most difficult option of the three. All three options are addressed here.
Traditional VBA relies on the built-in tooling of the Office products. If you are running an Office application, you have access to some built-in tooling to create form based applications. This tooling is not terribly sophisticated, but does offer some basic capabilities.
One of the limitations is that your code and user interface is locked in a specific document, like an Excel spreadsheet or a Word document. In other words, it has to be embedded into every document in which the user will interact with. Your programming logic and user interface is not easily shared among multiple documents.
Another limitation is that the VB language is very simplistic. The power of c# and VB .NET is not available to you.
As stated previously, the developer tools are built and directly into the office application, whether we are talking about Excel, Word, or PowerPoint. Also, there is no need to install the .NET framework 3.5. You can create stand-alone forms, but not the task panes, which is explained shortly. Database connectivity is still possible by leveraging standard ADO.NET constructs.
However, a huge limitation is communicating with SharePoint. You will be unable to read SharePoint lists.
You will also be limited in your graphics and charting capabilities. Finally, all your coding and user interface code is locked into specific documents and cannot be shared with other Office documents.
The graphic below illustrates task panes, which are not available with traditional VBA, but is available within VSTO.
You can read more about task panes here.
Creating Custom Task Panes in the 2007 Office System
https://msdn.microsoft.com/en-us/library/aa338197.aspx
One of the big advantages of VSTO is the ability to leverage WPF, which gives you almost unlimited power when it comes to rich graphics and media.
Note the link below to learn more about windows presentation foundation. Windows Presentation Foundation (WPF) |
Be aware, however, that you need to deploy the .NET framework 3.5. Luckily, this can be done easily using group policy. You can read more about the group policy here:
https://technet.microsoft.com/en-us/library/cc725828(WS.10).aspx
Once you are in the realm of VSTO, you have access to powerful programming languages that go way beyond what is possible with VBA.
If you are a .NET developer, you know what I am talking about here.
If your goal is to leverage SharePoint, VSTO is an ideal path to take. Moving forward, Microsoft is committed to providing an excellent programming model to interact with a SharePoint site from within an Office application. VSTO is the defacto standard to integrating SharePoint with Office applications.
Many of you are already aware of the ribbon as being the ideal interface for your users to interact with your applications. The ribbon is highly programmable and you can do virtually anything from within it, including adding buttons, text boxes, or any other control that suits your fancy.
Another advantage of using VSTO is that your code and user interface can be shared among multiple Office documents. You have the flexibility of choosing whether your code is tied to a document or whether it is tied to an application, such as Excel. Moreover, your application can be deployed using ClickOnce technologies.
ClickOnce deployment allows you to publish Windows-based applications to a Web server or network file share for simplified installation. Visual Studio provides full support for publishing and updating applications deployed with ClickOnce. ClickOnce deployment is available for projects created with Visual Basic, Visual C#, and Visual J#, but not for Visual C++.
You can read more about ClickOnce here:
https://msdn.microsoft.com/en-us/library/t71a733d(VS.80).aspx
Because of all the capabilities within VSTO, you will likely end up with more code than your VBA equivalent, perhaps as much as three times more. But you will probably have four times more capabilities than VBA.
Now if you love C++ and you want to enjoy the ultimate power and control, then developing a C++ COM Add-In might be the path you should take. At this point, you would be talking directly to the Office DLLs and bypassing the need for the .NET framework. So the advantages are clear. You can do almost anything without the need for the .NET framework. So, if you are in the business to sell your Office Automation component, this might be the correct option.
However, nothing is free and you are likely to write a ton of C++ code.
Which option makes the most sense to you depends on your usage scenario. The guidance above is an excellent starting point so you can consider the best option for your problem domain. VSTO and represents the middle ground and succeeds at offering the most functionality for your development efforts. Moving forward this technology will continue to integrate deeply with SharePoint and provide you with an excellent roadmap.
For further resources note of the links below. It also makes sense to consider some basic prototypes before making your final decision. Remember what grandpa I used to say, “measure twice but cut once.”
Thanks for reading and good luck!