Monday, October 06, 2008

That VBA Rant

So in addition to my regularly-scheduled job, I somehow agreed to do some training for the group of grads who joined the grad scheme the year after me. In Visual Basic for Applications. Which I loathe.

Now it's easy for me to say I hate Microsoft products. I'm a Linux user and a civil liberties geek, so it's fairly natural for me to hate those monopolistic bastards and their locked-down software. But this goes waaaay beyond that.

Visual Basic was the first programming language I ever learned. A friend introduced me to it when I was fourteen, and I was soon able to produce a cute little quiz program that got me really good marks in IT class. At the time I thought VBA was the bee's knees.

The process of disillusionment took a while. It probably started when I took an internship at a software company that did all their work in Python. The people were really nice, but it was immediately obvious how bad my programming style was. Stuff that should be completely intuitive for any programmer just wouldn't fit into my skull. I left with a much expanded repertoire of concepts, and a very strong sense of what makes code easier or harder to maintain.

My education continued over the years. A key resource was the comedy site The Daily WTF which, in addition to being hilarious, is a brilliant primer on how not to write code. There's a strong dose of the scientific community to professional programming: most coding conventions have arisen from years or even decades of gradual refinement by large numbers of skilled practitioners.

The icing on the cake was probably my university years. I did maths, and the programming modules were by no means focused on best practice, but I got very very good at elegantly expressing difficult algorithms in code. Software is the true language of mathematics; standard mathematical notation is just a shorthand.

And there's something beautiful about writing code for a well-designed programming language. When concepts and implementation tie together neatly, it brings a tear to your eye. Python is very good on this point: if you can think it, you can probably express it in Python code.

And then I took on a summer job with a telecoms consultancy. Who did all their programming in VBA for Excel. And I realised how very far I'd moved on. By week 4 I was gnawing at tables trying to get this kludgy toy language to do what I asked it to.

It doesn't have proper error handling. It doesn't have a proper object layer (try passing a function as an argument and call me a liar). It's not even remotely self-consistent in approach or content. Most of the Excel component is a wrapper round the Excel UI, which means that things like text search aren't at all programmer-friendly. Source control? Don't be silly.

Almost every language has something going for it. Java is slow and ugly, but at least it's portable. Lisp is impractical, but so very elegant. C++ is time-consuming, but good grief it's powerful. VBA is ugly, platform-specific, hard to use, lacking in basic and advanced functionality, but... there is no but.

As languages go, VBA stinks.

And yet, despite knowing this, I've managed to land myself in an industry with the highest concentration of VBA users on the planet. The vast majority* of actuarial "tools" are just spreadsheets with a VBA layer to do the messy stuff (batch-processing, goal seeks, etc).

I really can't take much more of this. It's as if I was a semi-pro mountain biker and the company forced me to ride around on one of those tiny clown bikes. It's as if I was a fencer and the only weapon they'd give me was a feather duster.

What makes it worse is that most people don't think this way. They've never even spoken to programmers, they show no awareness of software best practices or concepts like separation of concerns. Their idea of version control is taking a backup copy every few days (and that's only if they're particularly on-the-ball). Even commenting the damn code is seen as a bit avant-garde.

Most of them have had no formal training. Now I freely admit that neither have I - I'm a software dilettante. But I know my limitations, and I put a fair amount of effort into discovering what lies beyond them. By contrast, most people in the finance industry are alchemists: they take what they've learned from one or two tutors** and elevate it into divine knowledge.

I worry that, if I stay too long, I'll turn into one of them.

So I'm producing this VBA training material, and hating every minute of it. It's like swimming through treacle. The only good thing I can say is that, once the training is over, the company will have someone else to lumber with VBA-related tasks.

Rant over.

* The only alternative appears to be COBOL. This kinda says it all.

** Who learned it from their tutors, who learned it from their tutors. The chain generally terminates with a bloke who read "VBA for Dummies".


Giles said...

Hey there - have you taken a look at Resolver One? It's a spreadsheet that uses Python for scripting, and integrates it better (IMO) than Excel integrates VBA. We've been working on it as a way to integrate the ideas that software developers take for granted into the spreadsheet development process. There's a free version, too :-)

Here's the website:



Anonymous said...

You are not alone . . .
and I'm in the mining industry, far from any metropolitan area.
I've been chastised for writing something in Python rather than in "something everybody knows like VBA."
My story mirrors yours otherwise, except I wasn't 15; I was in my mid 30's when exposed to the devil's script.

Keep the faith on programming best practices. And take heart - Giles is right - Resolver is wicked cool as far as spreadsheets go.

Dunc said...

Oh God, not VBA! Even Perl is better than VBA. Have you ever encountered the Verity Stob classic "13 Ways To Loathe VB"?

I'm a "proper" programmer (of sorts) and it continually amazes me that so many massive multinationals run basically their entire core business off of a bunch of rickety spreadsheets. See, for example, How to lose $7.2bn with just a few Basic skills

The problem with proposing swanky new tools and approaches is that as far as the business is concerned, good programming has no business benefit. They think their existing systems work just fine, and they're not about to pay vast sums of money to have them re-implemented properly. If fact, they frequently get really pissed off when you build them a "proper" system and they find that they can't hack it to replicate all the stupid little exceptions, non-standard cases, and additional business rules that they never bothered to tell you about in the first place: "Oh, in Excel we just put the gross return value in the net profit field and change the weighting formula, but only for VHS sales in Slovakia on every second Tuesday, then we send it to Accounting and we have no idea what happens to it..." Half the time they don't even know what they're trying to achieve or why.

Lifewish said...

OK, so I've taken a preliminary look at the documentation for Resolver One and my initial reaction is WANT!!!

Unfortunately I'm a Linux user at home, so I can't install it for now. I saw in the FAQ that you're planning a Linux version - please do let me know when it comes out.

(As a FOSSer, I can only hope that OpenOffice is someday able to replicate this level of elegance. As a lover of good software, I can only hope you guys make a mint in the interim.)

Dunc: I regularly recommend that article to people who are learning VBA. It once singlehandedly saved me several hours of debugging. Long live the Stob!

Thanks for the Reg link btw, I've been looking for evidence of expensive VBA foulups. Will be useful for the tuition.

And don't get me started on the perils of system design. Favourite discussion:

Me: "So is it ever possible, under any circumstances, that a transaction could be closed without being paid off? Bear in mind that, if it is possible and you tell me it isn't, the system I'm building will have to be completely redesigned from the ground up."

User 1 (after apparent deep thought): "No, there is no way whatsoever that that could happen."

User 2 (a couple of weeks later): "So what do I do if I need to close the transaction before it's been paid off?"


Actually I get the feeling that this issue is akin to the problems people have implementing security properly. Most humans are just naturally not good at testing rules by means of exceptional cases. Contrast with this psychological experiment.

JP said...

VBA is a tool to get things done. Nothing more. It doesn't do everything a full blown programming language does, and that's the way it should be. It has its place, and where appropriate, there is no tool better suited. Unfortunately, it is very easy to write bad (but working) VBA code. But you choose to blame your initial bad programming style on the tool. That's a shame. Your post starts out with a critique of VB, but somehow you end up hating VBA (not VB). It isn't VBA's fault that it doesn't work like you want it to; it's a specialized tool with a specific bailiwick.

Take care,

Giles said...

@lifewish - looks like we need to keep working on the Linux version, then!

@jp - VBA is a tool designed to get a specific job done, yes, but the syntax and capabilities of the language are IMO pretty poor. There's no real reason why Excel's default scripting language has to be be a toy - especially given how broken a toy it is. A spreadsheet with a decent, solid, reliable language behind it is a better spreadsheet.

Of course, I've spent the last three years building such a spreadsheet, so as Ms Stob would say, you might have cause to remember the wisdom of the great prophet M'andee-rice Davies...

Lifewish said...

@jp: There's two answers to that. Firstly, of course you're right that VB/VBA* is a perfectly appropriate tool for $THINGS_THAT_VBA_IS_GOOD_AT. There are many such things.

Secondly, however, it's apparent that with a bit more forethought VBA could have been good at $MANY_OTHER_THINGS. These other things include most of the ways VBA is actually used. They also inevitably include whatever project I happen to be working on.

For example: error handling. Procedural error handling only works well in situations where the primary flow control tool is the GOTO statement. If you're using (for example) lots of loop statements, it gets very messy very fast. Also, some Excel errors don't respect VBA error handling.

The result is that most people who write VBA casually don't bother with error handling. This does not make the user's life easier.

For example: arrays. Ever tried testing whether an array is zero-length in VBA? The only way I've found to do it relies on error-trapping (about which, see previous comment).

This is not the sort of thing a programmer should have to deal with unless she's programming in C or asm (in which case she knew the risks when she took the job). The list of nasty hacks I've seen people use to get round this issue would fill a rant of its own.

By the way, I didn't mean to blame VB/VBA for my bad coding style, and I apologise if I gave that impression. My early coding style is entirely my own fault.

What isn't my own fault is that habits that are generally considered good programming practice (such as error trapping) are rendered extremely difficult by the VB syntax. So, once I'd "recovered" from crappy programming, using VBA was a bloody nightmare.

* I've been treating the two as largely synonymous - I learned VB6 at school, which IIRC has compatible syntax with VBA.