part 1 – part 2 – part 3

This is the second part of a three-part series, have a look at part 1 first if you haven’t read it yet.

Let’s just jump right in and continue the list of problems I have with VBA as a programming language for medium and larger MS Access projects.

Error Handling

The modern paradigm for error handling is exception handling, but to VBA that doesn’t apply for a simple reason: VBA doesn’t have exceptions. Instead, it has this:

On Error GoTo ErrorHandler

Yes, that’s a GoTo, and we all know those are evil. Even worse than GoTo though is this line:

On Error Resume Next

Basically, “just ignore all the errors that happen now, they’re not important”. Ah, right. We can still check for error codes, so even this evil construct (never ever use this as default!) is occasionally necessary to avoid the spaghetti code that the GoTo alternative would produce, while providing reasonable feedback instead of the techy default Access warnings:

1
2
3
4
5
6
7
8

DoCmd.SetWarnings False
On Error Resume Next
doSomethingUseful
If Not Err.Number = 0 Then
    MsgBox "Descriptive error message", vbCritical, "Error"
End If
On Error GoTo 0
DoCmd.SetWarnings True

What a silly contraption. In VB.NET, it’d look like this, and be way more flexible and expandable:

1
2
3
4
5

Try
    doSomethingUseful()
Catch ex As Exception
    MsgBox("Descriptive error message", MsgBoxStyle.Critical, "Error")
End Try

The current consensus among programmers is that GoTo isn’t necessarily evil, but it has to be used the right way. Myself, I have yet to see a case where I find GoTo to be better than its alternatives, but that’s subjective I guess. Excursus: I couldn’t resist solving the Last Example – Nontrivial Gotos from that last link, with less C code and no GoTo and, in my opinion, clearer:

1
2
3
4
5
6
7
8
9

int parse()
{
    while (Token tok = gettoken() != END) {
        while (!shift(tok)) {
            if (!reduce(tok)) {
                return ERROR;
    }}}
    return ACCEPT;
}

Anyway. VBA provides a GoTo, which is neutral, and it forces that GoTo upon programmers for error handling due to lack of alternatives, which is bad. Only C gets away with that.

The IDE

The Access VBA IDE basically consists of two parts (and thus technically isn’t really integrated, but we’ll let that slide): The in-Access editor (or layouter) for forms and reports, and the code editor for actual programming.

The layouter works quite well really, except that the one even in the free Visual Studio Express (which isn’t even the central part of that IDE, unlike in Access) is lightyears ahead, be it concerning auto-snaps or ease of use or number of clicks for anything. Microsoft doesn’t seem to use its internal synergies all that well.

The same then goes for the code editor, just worse: It hasn’t gotten an update in recent years, the one that shipped in Office 2007 is exactly the same as the one from Office 2003. Grievances are…

• Whenever you have a half-finished line, and switch line to write something somewhere else, you have an error message box pop up that you have to click away first – because it only checks errors when you leave a line, and has no other way to display them.
• Class lookup is shaky, with new custom classes it often it only works after you exit and restart Access.
• Even if it works it’s not completely reliable, working autocompletion is a rare positive surprise.
• Debugging is pretty limited, often when there’s an error the IDE doesn’t even stop at the point where the error actually occurred but at some point further up the call stack.

I can live with those shortcomings of course, but I really don’t see why Microsoft didn’t just use the Visual Studio IDE instead – it would not only have fixed those problems, but also have gained better searching capabilities, snippets, refactoring and wizards to go with it.

Object Orientation

VBA is object oriented! Yeah, to some extent: You can make classes, and there’s even properties. However, yet again there’s a bullet list of things that should be better:

• You can’t have parameters for your class constructors (the Class_Initialize Sub), so initializing a class must be either with default values or through a separate “init” function that’s called after instantiation with “New”.
• No real inheritance, you can just implement interfaces (and thus inherit method signatures, but no content or properties or anything) – considering there’s no compile time type checking anyway, I don’t see much purpose in that.
• No overloading. Yes, you can have optional parameters, but the same function name with different signatures won’t work (the language does support encapsulation though, so you can override functions in narrower namespaces, but that’s not really overloading).
• Objects are handled different from native data types. Not just “they are different”, but they have differing syntax too – an assignment to an object needs “Set”, one to a Variant must not have “Set”. So even though you can store an Object in a Variant, you’ll run into runtime errors if you do. Admittedly, Java shares similar problems, but at least it has autoboxing meanwhile.

Some people also have a problem with VBA having both modules and classes – myself, I see that more pragmatically: There are cases when you’d make just static functions in a class anyway, or work just with a singleton, so why not pack those in a module instead? You can always change to a class structure later if it becomes necessary.

The Community

Now this one is certainly controversial, and even more subjective than the rest of this post series. First off, there are quite a few great community sites full of dedicated and knowledgable authors, like the MS VB forums, databasedev.co.uk, VB Helper, The Access Web, FreeVBCode, the TekTips VBA section and the FMS tips section.

But what I’m talking about is the “Googlability” of problems.

If I don’t find a solution in a site I know (or sometimes while looking in one of those sites, with “site:foobar.com”), I just Google it (of course) – problems do occur, and others must have had the same ones before. Now what happens if I do that for VBA problems?

I find trash.

This is for these three reasons, I guess:

• There isn’t so much a VBA community as 4 VBA communities: ASP VBA, Excel VBA, Word VBA, and finally also Access VBA (about in that order of prominence) – and then there’s the VB6 and VB.NET community too, with solutions that often don’t apply to VBA.
  So the search queries have to get more and more elaborate, adding “-asp” and “-excel” is common if you want to find anything – thus it takes longer until you find your solutions.
• There are plenty of solution providers that might provide reasonable solutions, but cost money. Now, that’s a problem of mine I guess, but I don’t like giving my credit card info to some obscure website for a solution that might or might not work for me. Luckily, CustomizeGoogle filters those out quite reliably, after I tell it to.
• There are plenty of inexperienced users when it comes to VBA, way more so than with most other programming languages and other technologies I used so far (well, Python and Ruby tend to have geeky target audiences anyway, but the same applies to Java). This means two things:
  • Often, questions are answered less than optimal by only slightly more knowledgable users than the ones asking the question, meaning the solution is either just wrong or not generic and thus doesn’t work in a slightly altered context.
  • Even more often, questions aren’t answered at all. The favourites here are the people later writing “nevermind, I found the solution”, without providing what that solution was that I could use now as well…

Keep in mind that I don’t have any problems with inexperienced users whatsoever, I’m regularly a newbie in yet another programming language I’m learning myself. And asking questions is a good thing anyway – it’s just that it seems like more experienced VBA programmers move on to VB.NET or something and thus VBA queries often disappear in the void, because there’s nobody experienced around to answer them.

That’s all for now, stay tuned for part 3 with syntax, MS Access, other things and a conclusion.

part 1 – part 2 – part 3