Why VBA is so rubbish

The last VBA post may have given the impression I’m some kind of VBA fanboi. I think it has its place, but it’s far from perfect. I put my definition in that post so I wont repeat it here.

I think its important to keep in context of what is out there live in the real world now, so I’m not going to suggest another language is better. In pure terms python (or your personal favourite language) maybe a better language for users to do basic automation etc. The reality is there is a lot of VBA code around, and more gets written everyday. I think the language would benefit from a few modern features, perhaps to support OO more fully, but not at the cost of backwards compatibility.

I’m also going to skirt around the issue of user competence. You get crappy code in every language, some languages may encourage it more, or may be more attractive, or more accessible, to developers of limited skill and experience.

It has been suggested that giving something as powerful as Excel/VBA to an unskilled user is the equivalent of giving a loaded rifle to a child. The reality, I would suggest, is a little less dramatic, as far as I know, no one has ever been killed by a poor quality spreadsheet, and very few companies have gone bust because of one. Yes SocGen just lost a stack of cash, but a person did that by trading badly, not a spreadsheet.

VBAs biggest fault?

I’d say its too easy to go beyond its design envelope.

For simple automating Excel and standalone complex worksheet functions (eg: the stuff Mike talks about) I think it is great. Its easy to slip from there to 10/12/14 KLOC systems, at which point I think VBA can work against you.

The other side to that argument is perhaps there is no realistic migration path, or no realistic alternative. That may or may not be true, but either way is not a fault of VBA.

Second – The editor. Frankly its so arcane I think Integrated Development Environment is a bit rich. If this just got updated to the VB6 one (which has more visible object model) add-in devs would fix everything else. Ideally though I’d like a VSTA style editor that writes proper VBA, or something completely compatible, in Excel 14.

That would also fix my third biggest fault – Microsofts lack of public love for VBA. Many devs are worried about its future, and hollow verbal assurances don’t cut it. A significant investment in upgrading the IDE, or replacing it with the VSTA one would settle the uncertainties for many.

Speeding up the call interface would be excellent too. As would allowing VBA UDFs to participate in multi-threaded calculation.

VBA’s reputation as a meddlers monster creation system (/toy language) hardly makes it the sort of thing you want to own up to at a party. The political correctness bureau solution to that is the change the name (eg ‘disabled’ went to ‘people with disabilities’ now moving to ‘differently abled people’) in general the ‘name’ gets longer as they use more circuitous language. So perhaps renaming VBA to something like AAS (Application Automation System) would fix any stigma? (of course it would have to be MAAS or MOCAAS (Microsoft Office Client AAS)). You get the idea.

Any other suggestions? the ruder the better.

What would be your top 2 or 3 weaknesses of VBA?

cheers

Simon