VBA Vs VSTO – An interesting study

I started developing add-ins with VSTO 2005 SE and have developed my first add-in for Outlook 2003. Our old add-ins are developed using VBA. Now, my job is to add extra functionality and enhancements to it. For doing this, I have chosen VSTO for doing this project and planned to redesign it. After informing this to my TL and started the project, Some one asked me – What is the difference between VBA and VSTO? Why did you choose VSTO for developing the add-in? 

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. 

Just came across an article on the same in  Andri’s Blog which tryies to answer many common questions about VBA and VSTO. I would like to share it with you. You can find the original post here.  

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
Microsoft Office Security Checks

Runtime and ClickOnce Security Checks
Runtime and ClickOnce Security Checks

Runtime inclusion list 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 all!

Advertisements
This entry was posted in Concepts and tagged , , , by Pranav. Bookmark the permalink.

About Pranav

A Software Engineer by profession and a Geek at heart. I love exploring new technologies and working on algorithmic problems. As of now, I write code for bread, butter and satisfaction. I like to develop applications, and generally that involves much more than writing code. It’s not just about hurling code, but producing something meaningful that has a measurable and positive impact, helps others and solves interesting problems.

10 thoughts on “VBA Vs VSTO – An interesting study

  1. Pingback: Sruthi

  2. Pingback: La mort du VBA - Excel Downloads Forums

  3. Thank you for the thorough and helpful write up. I’ve been thinking I need to move my VBA development into VSTO for some time simply because I keep hearing “VBA will go away.” But I could never get clear on what the actual advantages of VSTO were.

    I do question the macro virus part though – in almost 20 years of working with MS Word I have never run into one of these. Are they really that common?

    Regardless, thanks so much for the great column. Very helpful.

  4. Hey there

    Nice article, but I thought I should point out something. You made the comment “VBA. It’s the only way you can do some automations or customizations to Microsoft Office products, well at least until 2004.”

    That’s not quite true.

    Addins were available starting with Office 2000, which came out back in 1999 or so. IExtensibility2 (and hence Shared addins) have been available ever since. And for certain addins (like you pointed out in the article) IExtensibility is the only way to go (for instance, if you have an addin that you need to target all the main players in Office, Word, Excel, Outlook etc), or if you need to target multiple versions (VSTO has that nasty habit of being version specific).

    Anyway, good article. The security stuff esp was a good read.

    Thanks!

  5. Pingback: Blog de Mario Meyrelles » Iniciando desenvolvimento para Excel com VBA e VSTO

  6. Pingback: Mario Meyrelles on VSTO and .NET » Starting Excel Development with VBA and VSTO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s