When and Why to Use VBA

One of my favorite examples of both when and why to use VBA if you’re not a programmer came up one evening at dinner with a friend. She had been up until 3 A.M. the night before cleaning up tables for a report that was due that day. It was a Word document containing 50 tables copied from Excel that needed to be cleaned up and reformatted. The task took her, a power user, about six hours-which, at just over seven minutes per table, isn’t bad.

But, she wanted to know if there was a quicker way for her to have gotten it done. She had created a few table styles and even recorded a macro for some of the formatting, but still had click into each table to apply them and then manually take care of any unique elements for each table.

In reply to her question, I asked if she knew any VBA, and she looked at me as if I had to be insane. But, then I told her that if she had known some basic VBA (just part of what you’ll learn in this primer, by the way) she could have accounted for most of the differences between her tables in one macro and then formatted all of those tables at once. The task would have taken about six minutes instead of six hours. As you can imagine, learning VBA no longer seemed like a crazy idea.

Of course, this timesaving example is just one of several types of situations where you can benefit from VBA. You can often use a single line of code to save substantial time or even do things you can’t do through the features in the user interface. Or, to take things further, you might also use VBA to create customizations or automation for your documents and templates, such as with custom dialog boxes that can help users complete form documents.

In general, the answer to the question of when to use VBA is the same as when to use any feature in the Microsoft Office programs-use it when it’s the simplest solution for the task at hand. In the case of VBA, however, you may also be able to use it when there doesn’t appear to be a solution for the task. VBA expands the capabilities of Word, Excel, and PowerPoint, so that you might find yourself with easy answers to tasks that you didn’t even know were possible.

In the 2007 Microsoft Office system, however, it’s important to ask yourself if VBA is still the simplest solution before you embark on a complex project. With the introduction of the Office Open XML Formats, you can do some things in the 2007 release more easily today with XML-such as automatically populating document content with data from other sources. Also, some functionality that would have required automation in the past can now be done with built-in features, such as using a Content Control to display a custom Building Block gallery when you need a selection of boilerplate text options that can’t be deleted. However, VBA macros are still almost exclusively the way to go when you want to use automation to save time on repetitive or cumbersome tasks.