VBA Primer

In this chapter, you will:

• Explore the benefits of working with VBA code

• Learn how to read and understand VBA code

• Discover the core basics of how to write your own macros

• Get tips and guidance on ways to apply VBA basics for accomplishing a wide range of tasks

If you have any concerns about venturing into this chapter, take a deep breath and relax. You’ll be perfectly comfortable here. This thorough primer on Microsoft Visual Basic for Applications (VBA) is written for advanced Microsoft Office users, 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 Office Open XML, as discussed in Chapter 24). Yes, I use VBA and Office Open XML to develop solutions for clients, but that just means I’m taking advantage of all the tools that Microsoft Word, PowerPoint, and Excel 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 how easy it is and how much you can do with VBA even with just a basic level of knowledge. But the most important discovery 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 a program are the names of features and tasks you already know from using that program. Keep in mind that VBA is just an additional way to work with, and expand the capabilities of, the programs you already know.

Beyond the program-specific feature and task names, most VBA language and structure is virtually identical across Word, PowerPoint, and Excel. 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 this chapter assumes that this is your first introduction to writing VBA (or writing any programming language, for that matter), it uses 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. Once you’re comfortable with Word VBA, you can apply all of the basics you learn to VBA tasks in PowerPoint and Excel as well.

Understanding When and Why to Use VBA

One of my favorite examples of 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. At just over seven minutes per table, that isn’t bad, but she wanted to know if there was a way she could have done it more quickly. She had created a few table styles and even recorded a macro for some of the formatting, but she 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 were 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 among her tables in one macro and then formatted all of them 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 to her.

Of course, this timesaving example is just one of several types of situations where you can benefit from VBA. As you saw in a couple of simple examples in the Excel chapters of this book, 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 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 at all. VBA expands the capabilities of Word, PowerPoint, and Excel, so you might find yourself with easy answers to tasks that you didn’t even know were possible.

In Office 2010 and Office for Mac 2011, however, it’s important to ask yourself if VBA is still the simplest solution before you embark on a complex project. With the Office Open XML Formats, you can do some things in today’s Microsoft Office more easily using Office Open 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 in Word 2010 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.