If you have any concerns about venturing into this chapter, take a deep breath and relax. You’ll be perfectly comfortable here. This is the first thorough primer on Microsoft Visual Basic for Applications (VBA) written for advanced Microsoft Office users, and not for programmers.
I’m not a programmer, so I won’t treat you like one. The fact is that you don’t have to be a programmer to make effective use of VBA (or XML, as discussed in the next chapter). Yes, I use VBA and XML to develop solutions for clients, but that just means I’m taking advantage of all the tools that Microsoft Word, Microsoft Excel, and Microsoft PowerPoint have to offer for creating documents. If you can learn to format a table, create styles, or create fields in Word; to write formulas or generate charts in Excel; or to customize masters in PowerPoint, you can learn VBA.
After years of avoiding VBA because it seemed technical and scary, I fell head over heels one day after I had no choice but to venture into the Visual Basic Editor for a client. I discovered both how easy it is and how much you can do with VBA even with just a basic level of knowledge. But, the most important thing I discovered was how much of the VBA language I already knew just from being an advanced Microsoft Office user. Nearly all elements of VBA that are specific to each program are the names of features and tasks you already know from using the program. Keep in mind that VBA is just an additional way to work with, and expand the capabilities of, the programs you already know.
Outside of the program-specific features and task names, most VBA language and structure is virtually identical across Word, Excel, and PowerPoint. So, the majority of what you’ll learn in this primer will apply to macros you may want to write in any of these programs. However, because I assume that this is your first introduction to writing VBA (or writing any programming language, for that matter), I use one program for most examples, to avoid the confusion of trying to cover too much too fast. Because Word is the primary document production program for Microsoft Office, most examples throughout this primer use Word VBA. Just keep in mind that, once you’re comfortable with Word VBA, you can apply all of the basics you learn to VBA tasks in Excel and PowerPoint as well.
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.