Excel Development - VBA vs VSTO

This article summarizes why one has to switch to VSTO while listing reasons why many developers still feel VBA as a better choice.

Before getting into the discussion of if VBA or VSTO are better, Let's have a look into the popular alternatives to VBA for Office Development.

1. VSTO: Set of tools to simplify development of MS Office based solutions. Works with Visual Studio Professional Edition and above.
2. Office PIAs: Fundamental COM Assemblies for Office for .Net programs to be able to communicate and contro office applications.
3. OpenXML SDK: Read, Write and Create documents in their native XML format and is good if you are just creating documents. This is the preferred API to create and deliver Excel or any office document on the DotNet based server side. Downside is that developer has to think in a different object model.
4. ExcelDNA: An Open source SDK originally aimed at developing Excel UDFs using C#. This is now being developed as an open alternative to VSTO which doesn't require Visual studio.
5. ExcelPackage: An Opensource API. Looks like this is not being maintained anymore.
6. ADD-In Express: Commercial alternative to VSTO and XLL. You still need Visual Studio and is sold as a solution which supports all versions of office in a single project. I am NOT sure of its advantages over Visual Studio itself though.
7. There are several other commercial APIs one can buy from the market, like Aspose, SpreadsheetGear, Infragistics, etc, offers some additional functions over VSTO, OpenXML SDK, Office PIAs. One should be careful when purchasing any commercial solution as most of the functionality is already provided by Microsoft for free and you might end up into a vendor lock-in. (We cannot eliminate Microsoft any way).

Getting into the actual topic of why VBA or VSTO, Let's start with what VBA offers:

1. Very easy to use programming language, visual basic. No modern language can beat the simplicity of Visual Basic (classic), may be there are fewer language constructs at the time, to confuse new learners.
2. Integrated development environment. All Office applications already have an integrated development environment which is very simple to use (hit Alt+F11 to invoke). No need to purchase any other tools to start programming. This comes with powerful features like live debugging, and inline modifications.
3. Macro Recording: This is the single most powerful feature of VBA integrated environment as one can learn the office API my recording actions. I personally use this to spare some Google searches. This is NOT available with any other options of office development.
4. Access to the the entire Object Model of Excel (and other office applications).
5. Provides option to use COM components installed on the system.

While these features are extremely powerful and by far irreplaceable by using any other development option, here are several reasons why you should not considering VBA as an option for your new project.

1. Microsoft has stopped developing Visual Basic (classic) in 1999 and has spent huge effort on building .Net framework. since then Microsoft is promoting .Net Framework and C# as the primary language of choice. Visual Basic .Net is equally good and can used if you are already an expert in classic VB.
2. Microsoft is planning to retire VBA for while. Office 2003 came with Scripting Engine based on Javascript (not successful) and since then promoting the use of VSTO and Office PIAs in .Net. 
3. Starting Office 2007, standard formats of Excel doesn't support embedding macros. Formats that support Macros and VBA projects only run of located at Trusted locations or when security is lowered below recommended levels.
4. Starting Office 2007, the VBA environment runs in the compatibility mode and having several issues when dynamically invoking methods from the embedded VBA projects.
5. It is very clear future versions of Office will not support VBA. However, we don't know which will be the last version supporting VBA (2010, 2012, or 2015?).
6. One has to seriously think about investing their time in learning VBA, when it is going away soon. It is getting very difficult to find junior VBA developers.

Microsoft .Net Framework: Microsoft has made serious investments into .Net framework over the past 14 years and today it is undoubtedly the best software development platform available. (Disclaimer: I am not a big fan of Microsoft, however, I am being honest about this considering the sophistication available in VS2010 and rich set of libraries available, both from MS and from other Opensource communities). In the context of this discussion, let me list the advantages of using VSTO over VBA.

1. Leverage .Net Framework: VSTO is based on .Net Framework. By using VSTO you will have almost entire .Net framework at your disposal, and can use rich set of  additional libraries from Microsoft and Open Source community.
2. Datasets, EnityFramework, WCF and LinQ: There is no substitute for the simplicity of these data connectivity/management tools. No tool in VBA comes anywhere close. If you are a .Net developer, you know how difficult to perform basic tasks on data in classic VB.
3. Winforms and/or WPF: It's your choice on which one to use, only your imagination will be the limit, on how you can provide custom UI for your solutions.
4. Enhanced security Model, which is a standard benefit of the .Net Framework passed on to Office development.
5. Visual Studio: Modern and Flexible Development environment of Visual Studio makes very complex tasks looks simple. Good collection of Project templates, ability to group multiple projects as a solutions, etc., all in praise of the sophistication available with Visual Studio.
6. It is the Microsoft recommended approach for new Office solutions development.
7. One can leverage skills across office development and general programming. For VBA, office is the last remnant and this aspect clearly I a winning attribute of VSTO.
8. One can use any programming language (VB.Net, C#, VC++, IronPython, IronRuby, F#, JavaScript) of your choice. (Note: Microsoft provides project templates for VB.Net and C#, and open source templates available for other languages).
9. VSTO, like any other project in the Visual Studio, provides Integration with Source control. To get this in VBA, we need to go thru some caveats.

Like any platform or system, we can praise to any level, still comes with several drawbacks or downsides. Similarly, there are several reasons why developers still prefer VBA over VSTO.

1. VSTO do not support macro recording. While we get very good intellisense support, lot of times developers has to rely on documentation, which in most cases is very difficult to get.
2. Developers need to purchase Visual Studio Professional or higher. Free versions (Express editions) does not support VSTO. One has to make some serious investment to get started with working with VSTO. (This can be overcome for certain types of Office solutions by using ExcelDNA for Excel development. However, you need Visual Studio if you want to work with other office applications). On the positive note, fewer people will have access to the code and fewer questions.
3. Deployment of the developed solution sometimes can be really painful. While VSTO offers various deployment and trust options, just having the "various" confuses the hell out of me. Now I need to consider what is best for my scenario and everyone thinks differently. I wish there is a one simple approach.
4. You cannot develop worksheet functions with VSTO. However, one can use ExcelDNa for this as part of the same VSTO project.
5. There is no tool available to convert existing VBA solutions to VSTO. If you already have VBA projects, there no way but to develop all of them in VSTO from the scratch. There is no clear guidance on how to properly migrate them. Most likely one will end up with composite environment and has to deal with maintaining the legacy code for a while.
6. Working with .Net is not as simple as classic VB. For a beginner, there will be a steep learning curve to get started. For a an experienced VBA (only) developer, this curve will be much steeper and suddenly (s)he has to think in completely different paradigm. This was a serious challenge in my team, and I don't blame the developers. They absolutely hated it.

Microsoft .Net adds 14 years of advancement and sophistication over classic VB. This advancement and sophistication also introduced complexity to the programming environment, several orders of magnitude. A developer who started their programming career 15 years ago have started with a fairly simple environment and upgraded over time don't feel this. But anyone who start today will suddenly see this enormous complexity at once and has to deal with the ever steepening learning curve. Same thing applies for VBA developers who are working in environmental frozen in 1998 and suddenly trying to work in an environment evolved during this time, without realizing that these environments have evolved so much.

While VSTO has its share of downsides, a progressive developer must start working on VSTO sooner or later. Sooner one makes the switch, less learning curve (s)he need to go thru.

I welcome readers to add to any missing points.