Visual Basic for Applications – the legacy language that Microsoft apparently still won’t drop even in future Office updates, although they’re working on it, and certainly hasn’t dropped in Office 2007. The plague with a lobby. Why is it so bad, is it so bad at all?
This very subject here was, in fact, one of the things that made me think “I have to get a blog again” over those years when I didn’t have one – I so wanted to write it whenever I was forced to work with applications coded inside MS Access, or when I coded them myself from scratch. I decided to make what started as a single post into a three part series, simply because there was so much content that one blog post couldn’t hold it all.
I won’t go into VBA for the rest of the Office suite too much, for tiny macro things it might even fit, what I’m talking about is medium (and larger) applications. And I will use the term “database” for the course of this article series for something it technically doesn’t strictly apply to, which is a MS Access application and actually an entire DBMS, plus GUI stuff and application logic, all mashed together.
So, what are all those things that bug me so much?
Language Updates
That’s possibly the biggest gripe of them all. Quoting the very Microsoft VBA site, at least MS makes their stance towards VBA perfectly clear:
[...] As previously announced, Microsoft does not expect to make significant enhancements to VBA. [...]
As of July 1, 2007, Microsoft will no longer offer VBA distribution licenses to new customers. Existing VBA customers can still purchase additional VBA licenses from Summit Software and Microsoft for existing solutions.
In other words, “we’re desparately trying to get rid of this mess”. Well, why wouldn’t they: VBA wasn’t updated for nearly 10 years now – while programming languages evolved around it, some very well-designed behemoths (for the time they were designed at) like C++ still stand their ground, many others have vanished and were replaced.
Yes, of course there’s always a demand for coders for older languages, just look at COBOL. If you like VBA, there’s plenty of legacy Access applications, and places where Access makes sense, so you won’t be out of a job any time soon.
That doesn’t change that the language would be in desparate need of an update, wouldn’t the update already exist in the form of VB.NET and, to a lesser extent, in C#. Why Microsoft hasn’t at least added support for those inside MS Access in the course of introducing .NET is beyond me.
The API
What clearly does belong to the VBA API, particularly in our context of developing MS Access applications, is the windowing framework. While it has its very own problems that I’ll describe in the IDE chapter, it does what it’s supposed to do rather well: Forms, reports and components, with a nice drag-and-drop interface for tinkering interfaces. It doesn’t have the best performance out there, but for database applications that work, who makes that their top priority.
Beyond those widgets and happy drag-and-drop things, for the most part, the Application Programmer Interface of VBA can be described with a few simple words: It doesn’t exist. While VBA does make use of DAO and ADO (not the way better ADO.NET, mind you), and the Office object model (which is actually more comfortable to use in VBA than in C#), everything that goes beyond that is out of bounds.
Let me illustrate by means of a symptomatic example.
Let’s assume you want a dynamically sized array, a Vector or ArrayList, that grows and shrinks as you add and remove items, preferrably useable as a stack or queue too by means of simple procedures. Now, in every other language that’s as simple as importing a bit of the API and then using said dynamic array. Not so in VBA, that bit of the API doesn’t exist (except if you count the Collection that has whooping 4 methods). There are “dynamically sized arrays” through lines like this one:
ReDim Preserve myArray(0 To arraySize)
This does mean though that all the automatic growing and shrinking has to be added by hand, and push and pop don’t work at all. In short, you have to do everything yourself.
Which I did by the way, you can download my classes here.
Mangled Tiers
Every non-trivial application, particularly multi-user applications, is best handled with multiple tiers: A database tier, a client/presentation tier, maybe an application server in between, that’s usually enough but can be extended. Less is bad, more is more complicated but depending on the application, necessary.
In MS Access, everything is “in the database” by default. Now,what does this mean? Basically, it’s a mess, and sorting things out properly is nearly impossible, whenever you update the database you also update the application and extending the user circle beyond, say, 1-2 users inevitably leads to problems (more on this later).
It is worth noting that these tiers can be somewhat simulated by using multiple databases for different purposes: You can easily have an “application” database that just does frontend work, and a “data” database. You can then create links from the “application” database to the “data” database, and have the frontend update independendly of the backend.
That’s common practice and makes sense, and comes close to true multi tier programming, although it still makes a mess of things because those links are hardcoded file paths and aren’t as easy to manage as they should be; the “data” database’s location has to be known on a file share, and every user needs write access to that share, which leads to obvious security inconsistencies and further problems that we’ll explore in the course of part 3.
The problem about paths being hardcoded can of course be solved by writing libraries that update paths, and the tier problem as a whole can to some extent be resolved by having data reside in an MS SQL (or other, through the rather dated ODBC) database.
What’s MVC?
This problem is very similar to the last one concerning mangled tiers. Modern applications often use, for maintenance and development reasons, a Model-View-Controller approach. Some languages and frameworks, like Ruby on Rails, even make it really hard not to (which is good, considering the approach makes sense). Now, if you want to create a program that’s cleanly using that paradigm in VBA, you basically have to ignore all the structure there is and make a new one. The good news is that you can actually do that, of course, and VBA doesn’t stop you from doing it – it just doesn’t help you a bit.
It all starts out so nicely:
- The model is the database, tables and views can be accessed via DAO or ADO
- The view is WYSIWYG-edited and stashed in Reports and Forms
- Every Frame has its own class, akin to its very own controller
Regarding models: Yes, in theory it’s nice like that. However, as soon as we start having things that go beyond tables and views, we’re screwed. That goes for simple things like tree view models (that are stored inside the tree view and not really accessible from the outside). You can make stand-alone recordsets (aka RAM tables), you can even disconnect recordsets from their source, that’s good. But overall, the model is in the view and vice versa, splitting the two is hard and in some places impossible.
The view only in the WYSIWYG editor is a nice thought for simple things, but when you want to start exchanging components on the fly, you have to write your own view classes (or end in chaos, as many VBA projects do) anyway. Or, the alternative many VBA projects take, have invisible components that you make visible or visible depending on the mode the form currently is in – a maintenance nightmare. The best approach here is having subforms and exchanging those, that actually works really well too and is my favourite approach. It’s just not the way VBA and Access make easy, and it can yet again very easily lead to duplicated bits (code or UI components).
Finally, the controller – you can avoid duplicated code and make pretty good controllers if you completely ignore the Frame’s default classes and only use those for proxying your own controller classes that you write in addition to them. Works well, it’s just, again, not the way VBA and Access makes easy.
That’s all for now, we’re warming up. Stay tuned for…
- Part 2 with error handling, IDE, object orientation and the community
- Part 3 with syntax, MS Access, other things and a conclusion
