VBA Badness Explained with Car Analogies

Lately I've had revisit to VBA and Microsoft Excel via a job. It's about filling up few templates and interacting with other software. Kind of common and basic things. It's been said that VBA is a bad language. People have been comparing it to javascript. But VBA isn't same kind of bad as javascript. It is it's own kind of bad.

Usually it's very laborious to figure out what's wrong in the programming language you're studying. In case of VBA that's easy. To add some challenge, I've tried to build up car analogies that match the explanation accurately as possible.

But I'm starting with some good things.

Dialog building editor

Visual Basic have always had perhaps the most working editor for building dialogs. It's strict, primitive and doesn't scale to different screens. But it works and as the result it takes less effort to make dialogs. It is one of the things I actually used to value and still put value to.

Analogy: The car has nice air conditioning.

Access to Excel sheets and other apps

Since it's Excel VBA, I can access the excel sheets. I can even open other applications in windows and put them do things, in limited manner sort of. It made it easy to fill up a Word template.

Analogy: The car runs with many unusual fuels.

Now to the bad things...

Excel sheets aren't very clearly structured

As a programmer I would like to filter out rows and analyse the data in the sheets. Perhaps show results of my script in read-only columns. Excel provides nothing like this. I'm getting cells and raw ranges. Got indexible headers with some more effort. Anyway it takes enormous effort to do simple things for an excel sheet using a macro.

Analogy: The automobile is a horse carriage, with a live horse.

Arbitrary error messaging

I spent quite lot of time solving an error. The IDE shown error at this kind of line:

UserForm5.Show

The error was inside the actual form, not in where it was invoked.

Analogy: The horse has gastrointestinal disease, but it pretends it's limb is broken.

Immediate error feedback

The editor tells immediately when you do an error. It throws a dialog box and throws your keyboard focus to that box.

Analogy: The car has a modern lane departure warning. But instead of warning it brakes to a full stop before it tells you what's wrong.

Many different ideas of a value

There seem to be primitive types that behave one way, and objects that behave other way. Sometimes comparison with a number happens normally, other times it doesn't. Sometimes an error is raised, other times not.

Analogy: The car explodes into pieces if you sit in a wrong angle.

Setting a variable

For setting a variable. There are two forms:

Set x =
x =

I still do not have idea which one is used when. I'm just guessing. Getting it wrong never seems to trigger a malfunction. The IDE just yells at me.

Analogy: There's both the steering wheel and bridles to control the automobile-horse carriage.

No class constructors

There are classes in VBA. Though they always cover a file module. This may discourage use of small classes. But the classes do not have constructors. You're doing this to instantiate them:

foo = new ClassName
foo.Init things

Analogy: The automobile-horse car has a trunk, but you can't open it entirely because the spoilers are blocking the way.

No case sensitivity

Variables aren't case sensitive, so L is l. I've messed on this few times now. The IDE seems to match case of variables.

Analogy: The horse has been trained to drink gasoline.

Volatile documentation

Pressing for the help -button in the IDE returns the bing search on the office software guide, which never seem to return anything on query. Searching for documentation from web seems to provide me documentation that occassionally or by part doesn't hold in my version of excel VBA. The documentation differentiates between product name, but not between language's or API's version.

Analogy: The instruction manual covers the every version and model of the manufacturer, but doesn't tell which instructions belong to which model and version.

Peer-to-peer documentation

The search often ended up to forum posts, where end-users told other end-users how to solve particular issues in VBA. It was mostly experiment to see what works and what doesn't. Many of the people guiding others were themselves in need of guidance about the subject they taught.

Analogy: The neighbour tells you how to saw off the spoilers from your automobile-horse carriage. He proposes to use a hammer.

No guide for event handlers

I left to guessing how to write a particular event handler for a dialog button.

Analogy: The manual doesn't tell where the gas pedal is, but you'd need to find that out.

Limited formation of event handlers

You cannot assign event handlers to event buttons. Instead every button has a name, and you need to write unique event handler for each button. I haven't found a mechanism to programmatically assign the event handlers, although I have searched for it. I assume one doesn't exist.

Analogy: There's only one gear.

Arbitrary automatic naming of things

There isn't an idea of unnamed form element in VBA. Since my noncoder peers generally do not seem to name every single button in their form, I find myself operating with TextBox1, ComboBox5 or an exciting ListBox10. Likewise there is a Module1, Module4, Module5 in the project hierarchy.

A simpleton would blame my peers. But why should you name every button in the first place? Seems like an arbitrary restriction to me, and arbitrary behavior to name the buttons arbitrarily.

Analogy: The horse in your automobile loves to run over pedestrians.

Inconsistency between abstract data types and types in general

ListBoxes support giving them tables. They allow you to add a header and such, but this happens through magic by sourcing the data from an excel sheet. I were unable to programmatically set the headers or data without having to create a new worksheet into the excel document. Eventually I ended up to ditching the headers as I could otherwise use the table formation mode as long as there weren't headers.

Analogy: You have a horse strapped into your automobile.

Fear and Uncertainty

Observing at the documentation I've gotten the idea that this thing changes every month or so. Something so unclearly documented cannot be very stable. I can perhaps trust that I'm porting this thing to a new flavor of VBA within a year. Perhaps even already next winter.

Analogy: Every once in a while, the gas pedal changes place with the brake pedal.

Arbitrary behavior between machines

It seems to be entirely possible to break a badly written script by changing the settings of the excel program. For example consider working with date strings.

Analogy: The car stops running if you adjust the mirror.

Lack of version control capabilities (no git)

Half of the stuff is text, half is not. But even the text is buried within an excel document, which I probably can't stuff into git and expect it to diff it correctly. That means I'm effectively coding without version control.

Analogy: There's only one seat in the car.

Conclusion

To finish, I found few gems in the internet:

Everything about the links I found are sort of connected to the peer-to-peer documentation -problem. If there's no professionals writing code in the language, it's a recipe for a catastrophy. The peer-to-peer documentation problem alone interesting subject worthy of it's own article.

Maybe I'll return to this subject some week...