Back to VBA

  • Article

Today I needed to write a fairly simple piece of code to manipulate some Excel documents, and I chose to do it in VBA. That might sound like heresy for someone who used to work on Visual Studio Tools for Office, but since I switched teams I feel no obligation to use that stuff any more ;-)

Just kidding, but I chose to use VBA for a few reasons:

·My main machine has the Whidbey CLR but only the Everett Visual Studio, which makes debugging a pain

·I wanted to deploy this to a small set of users without worrying about them having the CLR or updating policy correctly (yes, the security guy didn't want to worry about security)

·I was hacking this code as I went (ie, there was no design!) so in order to ease development I wanted to take advantage of Edit and Continue, "unfrozen host" debugging, etc.

·It was a quick-and-dirty solution and I didn't want to spend a lot of time messing around with a "heavy-weight" tool like VSTO

I'm actually pleased to say that whilst the first two reasons (infrastructure / logistics) were valid, the second two (developer productivity) weren't -- I would have been much better off using VSTO.

Using the VBA editor is painful. The IntelliSense is primitive, the mouse scroll wheel doesn't work, the forms designer is sooooo 20th century, the Forms3 controls don't make much sense, and so on. I had to deal with silly VB6-isms like the Set statement and the lack of a Return statement. When I was debugging, I never actually got to use EnC -- all the edits I needed to make ended up being "rude" edits that forced a project re-set. And my code turned out to have very limited interaction with the Excel OM itself, so being able to access the "unfrozen host" wasn't a killer feature.

So all in all a big thumbs up for using VSTO; you just can't beat all the cool productivity features in Visual Studio :-). Deployment is an issue with VSTO, so it's good to know that VBA is still around for the times when you need it.

One day I'll re-write the tool "properly" in managed code and take advantage of all the cool new Whidbey features... in my copious spare time!