When is VBA not the right choice?
I’ve been developing with VBA for over eight years and have been involved with hundreds of VBA projects. Over this time I’ve been exposed to just about every type of project you can imagine, from invoicing solutions, CRM systems, sales proposal automation solutions, back-end and front-end systems for financial clients, brand compliance solutions, as well as a few commercial add-ins.
While at the end of the day all projects got completed, the majority of them have been completed without a single line of VBA. Rather, I chose to use one of the newer frameworks, such as Visual Studio Tools for Office, Add-in Express and Mono.
What is VBA?
Many people mistakenly equate VBA with a full-scale programming language. While this may have been accurate in the very early days, it has not been so for quite a while. VBA is a small subset of the more feature-rich Visual Basic (VB6). It can be used to build applications, too, but out of the box it is nothing more than a toolset with some basic functionality – code editor with some user controls. Because it’s tightly integrated into Microsoft Office, it can be used to automate it.
With VBA’s vast adoption in the 90’s and general growth, it can be tempting to use it for everything, especially if that’s your (or your company’s) strength and comfort zone. An experienced VBA developer can chop up the system, turn it sideways, and make it do things it was never even remotely intended for, but the tricky part is looking at every project objectively and deciding not whether you can but whether you should.
The Strengths of Using VBA
Because of the built-in macro recorder, VBA has a very short learning curve. VBA tools are fantastic for simple things, such as automating lengthy tasks. The language is easy enough so that even novices are able to find their way around.
On the user interface side of things we’ve got forms and user controls, which provide the most basic options that anyone is likely to need. If your project’s primary use cases are around repeating steps or tasks, with an occasional simple user form, all this will enable what you need to do.
The Weaknesses of Using VBA
Among of myriad of small gripes and complaints, there are several large, serious reasons to not build an application in VBA.
Performance. VBA has never been a fast performer; the philosophy behind it being an interpreted language. Because it uses background compilation, it runs much more slowly as compared to fully compiled code.
The usual approach to dealing with VBA’s performance implications is to use a few commonly known optimizations, such as turning off screen refresh, or turning off automatic calculation in Excel. If you are dealing with a large codebase, this can provide some benefit. But even for code that is optimized, it still runs slow when compared to compiled languages, such as C or C++.
Connectivity. VBA came out of the 90’s and as such was not meant for environments that are used today. Increasingly, today we need to connect to API’s that consume and exchange information in XML or JSON. VBA was built in a time when such formats didn’t exist. So even today this presents a challenge which is better solved using newer languages, such as VB.NET or C#.
User Interface. VBA user forms look and act pretty Jurassic when compared to newer presentation frameworks, such as Windows Forms or Windows Presentation Foundation (WPF). These newer frameworks have been built a wide range of devices and input methods in mind, such as high-DPI monitors, tablets, and pens. As a result, they make for a much visually pleasing, aesthetic and robust user experience.
If you’re the sole and only user of your VBA application, probably you can get by. However, if you’re keen on producing user interfaces that work across a broad range of screens, you’ll probably want to look beyond VBA.
Testing. There are no automated tests in VBA. Because of this, all testing is done manually. Those that are not affected by this are small teams with a few stakeholders and/or a single developer. If however your product is utilized by many departments, or is sold to end-users via the Internet, this is not acceptable.
Doesn’t scale well on Mac. With the newer releases of Office, it seems that VBA support on the Mac has been deprecated. The code editor is practically impossible to use, so your productivity as a developer is significantly reducted.
UserForms vs. Windows Forms
If you have a little bit of VBA under your belt, a logical step is to upgrade your skills to Visual Basic .NET. This is the least steep learning curve. Moving from UserForms to Windows Forms is not as hard as learning an entire new language, such as C#. Windows Forms are more robust, look better, have more controls, and is 100% compatible with Mac. This means you can still use a single-codebase approach that works on both platforms. Particularly when using the free mono framework. So why not give it a try?
A form that was created with Windows Forms (on Windows), runs on Mac without any modification.
Summary
At the end of the day, it’s in your and your client’s best interest to scope out the projects properly, and pick the best technology and tool for the job, which may not be the one you’re most experienced with. Maybe you haven’t considered all the options, and they’re definitely worth taking a look.