VBA and VSTO

(originally posted 5/2/07)
_____________________________________________
From: SD-Jim Sawyer
Sent: Wednesday, May 02, 2007 2:23 PM
To: SD-Dave Brann; SD-Kevin Bennett; NS-Geoff Skipton; SD-Emily Ligotti
Cc: NS-Jess Bardin; CD-Jason Moline; SD-Brian Mascarenhas; SD-Ian Anderson
Subject: RE: VB6 retirement

(broadening the audience…)

Eliminate VBA? You can do it already!

As Kevin pointed out, "Visual Studio Tools for Office" has been around since 2003, with a major upgrade in 2005, and it's the recommended way to create document-specific solutions (like our Excel UI). In Office 2007, VBA is still supported for convenience, but VSTO is where Microsoft is investing for the future.

Why would we want to do this? Here's 9 "compelling" reasons:

1. Managed code beats unmanaged code
2. Object-oriented programming beats procedural programming
3. .NET Framework beats… not having the .NET Framework
4. A better development environment
5. Code reusability beats rewriting every time
6. Web Services is the best way to integrate
7. Security - To protect your users and your code
8. Ease of deployment - No more emailing patches
9. Robust and programmable error handling

To me, #3, #4, and especially #8 are key for our work. It's been so nice not to have to deal with the DLL hell that we used to on installs.

Here's a detailed example of migrating from VBA to VSTO.

And here's a less rosy look at the VBA -> VSTO migration, for equal time (the comments are pretty good too).

Sure, there are a lot of "yes, but…" For example, you could say it's pretty convenient to have a code editor and compiler (interpreter, technically) within Excel itself so you don't have to work "outside" of the application. Personally, I think Visual Studio is great and wish I could write every application or simulation model within that IDE.

For the Excel UI specifically, there is an open question if it is worth spending any time migrating the platform when we are really only down to a couple of users (literally. 2 project managers). I ran the upgrade wizard on it a couple years ago, and let's just say more work would be needed. I wrote a development plan and roadmap for doing this work, but never convinced myself of the business need. Modeling Studio is just a better place for us to spend our time.


James T. Sawyer
Senior Analyst
TranSystems | Automation Associates, Inc.