Carpe Datum: Knowing When to Build a Macro

Office's macro recording capabilities are impressive, but before you seize the data, you should make sure that Office doesn't already provide a built-in solution for your repetitive task. For example, if you routinely boldface your Excel column headings and increase their point size, you could record a macro that will automatically format the headings for you. But it would actually be faster for you to use the Style command on the Format menu to apply a heading style that accomplishes the same formatting effect. In other words, don't use macros unless the commands that you want to record are involved enough to require a macro.

This word to the wise doesn't mean that you shouldn't use macros to automate your Office documents. Actually, we're arguing just the opposite. But before you get started, it makes sense to take the time to become familiar with the majority of each Office application's features so that you know when Office offers a built-in solution and when to use macros to their greatest effect. The Visual Basic macro language in each Office application is sophisticated enough for many advanced tasks, such as communicating with other Windows-based applications or controlling an entire inventory management system. However, the most useful macros are often the ones that automate just four or five simple commands.

Choosing an Office Application

In Office 2000, you can record and run macros in three applications: Word, Excel, and Microsoft PowerPoint. You can also create macros in Microsoft Access, but the process is quite different, and we won't cover it in this book. (Rather than recording a macro, you build one by clicking the Macros tab in the Database window and specifying action arguments on a worksheet grid.)

In Word, Excel, and PowerPoint, you follow the same process to create macros. To record a new macro, you choose Record New Macro on the Macro submenu of the Tools menu, run the commands you want to record, and then click the Stop button. To create a macro from scratch using Visual Basic, you open the Visual Basic development environment using the Visual Basic Editor command on the Macro submenu of the Tools menu, and then you type the text of the macro in a text editor window and save it to disk.

Because the macro creation process is identical in Word, Excel, and PowerPoint, it doesn't really matter which application you use to practice building Visual Basic macros. We've chosen to show the process in Word, because word processing is the most popular activity in the corporate workplace and in many ways Word is the easiest application to understand conceptually. However, after you work through the tutorial sections in Part 8, you should feel free to create macros interchangeably in Word, Excel, and PowerPoint to suit your own needs and interests. Best of all, Office has a powerful integration technology known as Automation, which allows you to run commands in several different Office applications from within one macro. We devote Chapter 41 to this topic. See Chapter 41, "Working with Office Application Objects."