Scripting Excel: Can It Really Be This Horrible?

OK, I have managed to avoid this for a long time, but I guess it was inevitable: here comes Martin’s cheap, nonconstructively sarcastic I-hate-Microsoft post.

So I was visiting my parents this week-end, and my Dad asked me to help him with a little macro job on an Excel spreadsheet. It sounded simple enough. However, I had forgotten just how astonishingly horrible Visual Basic For Applications, the sorry excuse for a programming language built into Excel (and the other Office applications), can be.

As far as I remember, the last time I did anything with VBA was probably somewhere in the late nineties. Even by the standards of back then, VBA is a really shitty programming language. By the standards of 2009, it’s spectacularly bad. The only explanation I can think of is that somewhere high up in Microsoft Strategic Command, somebody decided to spend a lot of effort on making it as useless and infuriating as possible, while still keeping it just barely functional enough to be able to do the things you want to do with it, if you’re willing to go through a lot of pain. God only knows why they made that decision, but surely a language as bad as this cannot be created by accident.

What surprised me most is that it was still exactly as bad as I remembered. We’re talking about Office 2007 here, the all-new singing and dancing one with the cool magic fluffy ribbons and shit. Surely they could have made some minor improvements to the scripting language while they were hard at work at hiding the ‘File’ menu behind a big round decorative window-corner ornament? In fact, I would have expected them to have simply integrated the .NET framework into Office by now, so that you could write your Excel macros into F# if you felt like it?

Apparently, that is not the case. The language is still filled with lots of little inconvenience such as the fact that, where every other programming language on Earth lets you return a value from a function by doing something like

def thisFunctionReturnsThree()
    return 3
end

VBA, for reasons which I’m sure seemed like a good idea to somebody at some point, expect you to do it like this:

Function ThisFunctionReturnsThree()
  ThisFunctionReturnsThree = 3
  Exit Function
End Function

But minor inconveniences like that are just a scratch on a gaping shotgun wound. Where VBA really starts driving the red-hot splinters of aggravation under your fingernails, is when you try to use the Collection object. The VBA Collection is some kind of schizophrenic bastard datatype with a horrible identity crisis, which can never quite decide whether it wants to be a fancy-schmancy associative array (a.k.a. hash or map, for those of my readers who are used to non-braindead programming environments) or just an ordinary variable-sized array. Anyway, back in 1997 or whatever, the Collection type supported a grand total of four methods:

Add
Remove
Count
Item

And here in 2009, it still supports the same four methods! Note that there is no way to enumerate all the keys in the collection (when using it as a hash), nor is there a way to ask the collection object if it contains a given value (when using it as a resizeable array). Doing myCollection.Item("foo") will throw an error if foo is not present in the collection. So there is no way, at least without grossly abusing the exception-catching mechanism as a flow control technique, which is evil, to access a value of which you are not certain whether it exists.

Nor does VBA seem to natively offer any more powerful container types. The standard trick, apparently, is to bring in the Scripting.Dictionary from the Windows Scripting Library, which is not a standard part of either VBA or MS Office, but which can kinda-sorta generally be expected to probably be present on most Windows systems which have been installed and occasionally updated during the past five years. (The ones which have not been regularly updated are, of course, so bogged down with malware by now that there won’t be any CPU power left to run Excel anyway, so no need to worry about them.) Scripting.Dictionary is hardly the equal of, say, the Hash class from Ruby’s standard library, but at least it has a contains method and it allows you to enumerate its keys and values. Welcome to the 1990’s, Microsoft!

By the way, have I already mentioned that VBA, again unlike every other programming language invented after the death of Blaise Pascal, does not do lazy evaluation on Boolean expressions? So if you want to write something along the lines of, say

If myDict.Contains(“foo”) And myDict(“foo”) = “bar” Then
  ' Do something cool
Else
  ' Do something not-quite-as-cool
End Ifi

then your code will break because, after having cleverly determined that myDictionary does not contain an item named “foo”, VBA will then shrewdly try to access it anyway, and promply die.

Oh, and neither Collection nor Dictionary has a built-in ability to sort its contents. If you search for e.g. “vba sort container” you will find a lot of people who have helpfully written their own functions to perform this rather basic task.

We did eventually get my Dad’s spreadsheet to do what we wanted it to. I’d estimate that I spent about 10% of that time on actually writing the functionality we were after, and the other 90% of the time swearing at the absurd, arbitrary limitations of VBA and trying to come up with stupid workarounds for things which should be absolutely trivial, and which would be absolutely trivial in any other language, and which anybody who wants to write a non-trivial piece of code is going to need so why isn’t it present by default in this supposedly mature product? Gah.

Now, it is very possible, even quite likely, that many of the things I ran up against were simply a result of my unfamiliarity with the programming environment. I am more than happy to admit that I am not an Excel guru or a VBA guru. Probably then, there are much better solutions to each of the problems mentioned above. Please feel free to teach me about them and laugh at my ignorance!

However, I do know how to use Google, and what I found were not solutions but just a lot of other people having the same problems and complaining about how incredibly limited the language is. So, Microsoft, could we perhaps ask you to take perhaps one-half of a developer off the task of making Office 2010’s ribbons even sparklier, and bringing the facilities of VBA up to the level of an average scripting language from ten years ago? Thanks in advance.