Whats so good about VBA?

A (very valid) question from Johan.

Here is why I think VBA is so great, but first lets try and define VBA.

A tightly integrated application automation system that allows professionals from non software development disciplines to enhance their organisations use of the host application. With justifiable levels of training costs and tooling costs.

And secondly, lets add some context – we don’t develop in a vacuum so we should not review our tools in one.

Its 2008, VBA was integrated into Excel in v5 in 1995, last notable update was 97 really when we got the current IDE. Prior to VBA many orgs has dipped a toe in the macro waters with XLM and Lotus macros. Many organisations have over 10 years worth of live VBA code. And coders with 10 or more years experience solving that orgs problems with that toolset.

Software vendors desperate to sell us their latest version have almost convinced us that ‘legacy’ means ‘old useless shite that should be thrown out’. But legacy also means ‘something of value handed down from ancestors’. What better legacy than tested, proven, working code, no matter how out of fashion the language might be? or how ‘not to your style’ the code might be?

I’ll be frank, I don’t much care for BASIC derived languages, I prefer C based ones. VBA has a few language features missing, and a few backwards compatibility ones I’d happily see removed. It also has the odd inconsistency, and a few awkward twists (If not (x is nothing) then... springs to mind). VBA is more, much more than a language though, its a complete system in partnership with the host app, with history.

‘Do you like the whole VBA story or not?’ boils down to do you savour proven, working code? or do you like new stuff? Do you like safe and certain? or do you like unnecessary risk and waste?

VBA has a major entry barrier for someone from a user background (I mean business here, but also science, engineering etc – anything except sw dev I guess). But once over that barrier is a gentle learning curve through to extremely high levels of productivity. Object Oriented approaches represent an even bigger barrier to that target group in my opinion, with limited payback in that problem domain.

Excel is a functional tool, its users understand functions – they write them in almost every cell. Very few use classes, objects make no sense to Excel users. Classes are considered an advanced technique in Excel VBA, not because they are difficult, because they are an unusual way for a spreadsheeter to go about solving the sorts of problems they solve with spreadsheets.

Spreadsheeters email their spreadsheets around, this approach is absolutely proven to work in (and out of) organisations of all sizes all over the world in all industries. VBA supports this zero deployment in almost all cases. Most spreadsheet users have never heard the expression ‘deployment’ that is how ‘zero touch’ VBA is. (many have never heard of testing either but that is a separate discussion)

The Excel COM object model is an ideal target for VBA, optional parameters, parameterised properties etc are all fine. Unlike C# for example, which is from a whole other era, designed from the ground up to solve a whole other type of problem. Should Excel get a C#/manged code friendly object model then that would even things up a little. But by then there might be 15 or 20 years (or more??) of VBA legacy code to compete with.

Whats so good about VBA?, is like saying whats so good about driving on the left? honestly? it would be better for a whole host of reasons if we swapped and got in line with all the new world countries. But who, when, how and where could this be done? and who will pay, and when would they get payback (hint: never ever ever).

Whats so good about VBA? its here, now, in widespread use. Perhaps the correct question is what should an organisation that is already heavily invested in VBA (most are) do now in 2008?

Of course theres lots to not like about it too, but thats a topic for another post. The point of this post is that I don’t see anything currently better than VBA for most of the stuff that VBA is used for, and for the folks that use it.

Maybe the VBA argument comes down to: SW devs will always hate it because its an impure bastardisation of a beginners language. Business users will prefer it to the other available options because they can leverage existing investments, so it represents the best value for money.

Try this test: ribbon v commandbars? .net v VBA? either you like new stuff or you like existing proven stuff.

What have I missed? anything to add? anything you disagree with?

cheers

Simon