Several weeks ago, a client asked my company to be involved in a project to develop some kind of customizations for Microsoft Excel using VBA. Visual Basic for Applications or VBA? Hmmm…sounds like an old stuff. I remembered playing with VBA when I was in college. Watching my computational physics professor cracking RSA encryption by only using VBA was impressive. He also visualized Kinematics translations, fractal, and electrical field using VBA. Oh...I missed old days. Just for my nostalgia, here are some captures of physics fun stuffs using VBA.
Fractal
Electrical Field
VB veterans or Microsoft Office advanced users should know VBA. It’s the only way you can do some automations or customizations to Microsoft Office products, well at least until 2004. Along came Visual Studio Tools for Office (VSTO) v1 at 2004, introduced another option for doing stuffs like VBA did.
Back to my client story, I asked them why we don’t use VSTO instead of VBA. Then they asked back, why we should use VSTO. What are VSTO advantages compared to VBA. OK, this blog posting is all about it. Trying to answer many questions to me about VBA vs VSTO, I try to compare them here.
After reading and compiling from many references, and by adding some experiences using those both technologies, I come up with this list. Since this comparison are meant for everybody, I try to explain as detail and general as possible.
1. Leverage .NET Framework
The most fundamental difference between VSTO and VBA is that VSTO based on Microsoft .NET Framework. All .NET developer should know the advantages that .NET framework offers. It includes a large library that cover a large range of programming needs in a number of areas. As part of .NET technology, VSTO inherits that richness. At the very basic level, VSTO has access to .NET Base Class Library (BCL) that includes basic functionality, including Collection, Input Output (IO), Globalization, etc. VSTO also has access to some .NET Framework Class Libraries (FCL), such as XML processing, ADO.NET, LINQ, Windows Forms, WCF, etc.
The rest of the list are the consequence of this point. If you don’t have time to read, you can stop here :)
2. Development Productivity
Despite I’m a coder and my passion about technologies, at the end I’m also an businessman. When it come to business, productivity must be taken into account. Mostly, productivity is all about tool.
Why on earth using VSTO is more productive that VBA? Well, as its name, developing using VSTO means using world-class IDE, Visual Studio. Compare it to VBA Editor within Microsoft Excel or Word, I think it's just not fair to compare them. More over, Visual Studio seems will be more and more improved by Microsoft in the future, while VBA Editor is not. I’ll talk more about productivity as I go along with this list.
3. Language features
Since VSTO is based on .NET framework, it has access to all .NET language features. As we know, coding using .NET means coding using .NET compatible languages, such as VB.NET and C#. Unlike VBA that should be coded using Visual Basic language (which is a procedural language), coding VSTO allows us to code using VB.NET or C# (which are pure Object-oriented Programming language).
I won’t tell much about OOP advantages compared to procedural language here. Some points I’d like to highlight here are reusability and error/exception handling. As we know, OOP language allows us to achieve reusability through its features, including: inheritance, polymorphism, abstract classes, and interfaces. VBA is failed to perform those stuffs (at least not pure) so it’s quite hard to achieve reusability. In term of error handling, VB.NET and C# also have language construct called try…catch…finally that makes a nice, elegant way to prove your code's infallibility. In VBA, error handling is performed using GoSub, GoTo, or On Error statement, that will lead to frustration as your code grows.
The latest VB.NET 9.0 and C# 3.0 features are accessible from VSTO, including: Automatically Implemented Properties, Implicitly Typed Local Variables, Extension Methods, Lambda Expressions, Object Initializers, Collection Initializers, Anonymous Types, and Query Expressions. All those new features are the basis of the great technology like Languange-integrated Query (LINQ), that adds SQL-like query capabilities to .NET language for querying array, collection, XML, and RDBMS.
4. Application level hooks
VSTO can access many application level functionality, such as Ribbon, Custom Task Panes, etc. Using VSTO, you can develop application level add-in that will always be available regardless opened document. By contrast, VBA customization is document level.
5. Customization Code Separation
Unlike VBA, where customization code is stored in the documents file themselves, code written for VSTO are compiled and store in separate .NET Assembly which is associated with the documents by means of custom properties. At runtime, that assembly is loaded on the fly. This separation allows us to easily maintain the customization and apply security constraints (I talk more about this later).
6. UI Customization
When it comes to user interface, VSTO provides more complete functionality compared to VBA. VSTO allows us to customize Task Panes that is impossible in VBA. Task Pane is an specialized pane, typically shown on the left, introduced in Office 2003 that can display additional controls or functionality you can do on document. You can drag and drop all Windows Forms controls into Task Panes and use them as you can do in Windows Forms development. You can also use Data-bound controls and Data Sources to easily bind data from DBMS into Task Panes. If you have controls built using Windows Presentation Foundation (WPF) technology, you can host them on Task Panes. All you can do on Task Panes, can also be done on Outlook Form Region, which is a custom form developed to extend Outlook UI.
It's also easy to customize Ribbon, an UI element introduced in Office 2007. You can use VBA to customize the Ribbon using RibbonX, but VSTO makes it even easier by providing a Ribbon designer that makes adding new tabs, groups, and controls to the Ribbon as intuitive as adding a control to a Windows Form.
7. Source control
Like any .NET projects, you can use Source Control server like Team Foundation Server (TFS) to facilitate collaborative development for VSTO-based project. All VSTO project artifacts can be checked in/out to/from TFS server, including Office document (xls, xlsx, doc, docx, etc). As far as I know, there's still no add-in or provider in Excel or Word to control VBA project source code using TFS. However, there does exist some add-in for
Access/Excel to work with Source Safe to version control the VBA project. For example, here's the add-in for Access 2003: http://www.microsoft.com/downloads/details.aspx?familyid=2ea45ff4-a916-48c5-8f84-44b91fa774bc&displaylang=en
8. Distributed System
When it's first introduced, one of .NET buzzwords is XML Web Services. It introduced the very easy way to develop web services-based application, more generally, service-oriented or distributed application. .NET also introduced Remoting for more native way to implement Remote Procedure Call (RPC). Start from .NET 3.0, all those technologies now converge into one technology called Windows Communication Foundation (WCF).
Since VSTO is a part of .NET, it inherits all cool technologies like WCF. In other words, it's easy to develop VSTO Add-In to access XML Web Services, for example. It's same as like you do when you access XML Web Services from ASP.NET or Windows Forms, just Add Web Reference (or Add Service in VS 2008) to create proxy classes, and use those classes. And all that functionality is built-in. Compare to VBA that you need certain add-on to be able to access Web Services. You can read more about Calling XML Web Services from VBA.
9. Deployment
No matter how well an application runs while it's on development, deployment can be an issue. Deployment is not only about installation on users' computer, it's also about patching, version update, and maintainability. Since VBA application is embedded in the document itself, it's easy to install the application on users' computer by only copying the document. But when it comes to update the code, you have to open the document and change the code manually. Just imagine how that can be done for many users environment.
On the other hand, as the nature of VSTO, the customization code is stored separately in .NET assembly (*.dll file) that actually linked to the document (for document-level customization) or Office application (for application-level customization). That .NET assembly can be deployed to a central location and all the documents just link to it, that will make updating quite easy. Updating can be configured so it can be checked every time the document is opened, or by specific period (7 days, one month, etc).
Furthermore, VSTO supports two deployment models
Also refer to this blog about VSTO v3 Deployment Session.
10. Security
Lets face it, it's difficult to trust VBA code, as evidenced by the high number of VBA macro viruses those have been developed. In VBA, there are three basic security options:
- Set the security settings to high on user machines and digitally sign your code.
- Let the user decide whether or not to trust the code when presented with the macro virus warning.
- Set the security to low to allow all code to run (including malicious code). Note that this third option should never be used
Those security options must be selected and configured by user manually.
Lets compare to VSTO security model. The VSTO security model involves several technologies: the Visual Studio Tools for Office runtime, ClickOnce, the Trust Center in Microsoft Office, and the Internet Explorer restricted sites zone. During installation, here is the sequence of security check (taken from MSDN Documentation).
- Microsoft Office security checks
- Runtime and ClickOnce security checks
- Runtime inclusion list checks
If you still don't trust it, then you're so paranoid :)
I think the comparison list is enough for now. But there's possibility that the list will grow as the VSTO will be improved more and more along with .NET update. Currently, VSTO is version 3 or VSTO v3. I assume there're will be v4, v5, etc, following the new release of .NET framework. On the other hand, VBA will no longer be improved, although there's still no plan from Microsoft to retire it.
By this post, I'm not trying to make a war between VBA and VSTO. It's just a matter of choice. When it comes to choice, it's fair to compare them. Actually, VBA and VSTO can become friend. Many blog posts and articles those talk about interoperability between those two, for example: Extending VBA with VSTO 3.0. The interoperability is only possible by using VSTO v3.
That's it. Enjoy.