How Macros Work

When you get right down to it, a macro is nothing more than a computer program. Macros can be surprisingly short—even a one-line macro can perform helpful tasks—or they can run for hundreds of lines, with loops and variables and input boxes, and other elements you normally associate with a full-fledged programming language. You don't need to be a programmer to automate much of your work with macros. All you need is a basic understanding of the underlying application and a willingness to step through a few lines of code.

With rare exceptions, you can create a macro to automate any task you can do manually in Word, Excel, PowerPoint, Outlook, FrontPage, or Access. Macros are ideal for automating routine drudge work—those everyday tasks that normally require multiple menu selections and mouse clicks.

For example, you can use macros to

• Print company letters and companion envelopes—routing the printout to the correct network printer, and selecting the correct paper trays for letterhead, additional pages, and envelopes—and then printing file copies on yet another printer, or with a different tray.

• Apply complex formatting rules—everything from scanning corporate reports to ensuring that all "Level 1" headings start with a number, to validating the searchable keywords in a memo, to correcting common typographical mistakes such as two spaces following a period.

• Collate and aggregate budgets, at any organizational level, complete with charts and custom pivot tables, based on Excel spreadsheets submitted by each work unit. When changes come, roll the new numbers into the divisional or corporate report in minutes.

• Retrieve data from an Access customer database and generate collection letters in Word for all customers whose accounts are 90 days or more past due.

All the major Office applications share a common programming language, Visual Basic for Applications 6.0 (VBA). VBA is discussed in more detail in Chapter 39, "Working with Visual Basic for Applications" ; in this chapter, the focus is on how you can record and edit macros to automate simple tasks. And if you don't think of yourself as a programmer, think again: When you record a macro, you are creating a full-fledged VBA program.

What Can You Do with Macros?

Say your branch offices post their sales reports over the weekend on your company's intranet. Your boss expects you to download each report, format it according to company style, add your analysis, and then turn it into a memo that can go out under the boss's name to everyone else in the department. You can write a macro to automate much of the task. The sequence might go something like this:

• On Monday morning, you start Word; choose File, New; select the Memo based on sales statistics; and click OK. Then you go get a latte.

• Word creates the new memo, complete with distribution list and a Word table at the top of the memo. Then the macro kicks in and opens the intranet Web page with the sales information, viewing it as a Word document. The macro steps through the document, pulling data from predefined locations on the page.

• The macro takes the data from the Web page, performs whatever mathematical gymnastics might be required, and puts the resulting numbers in predefined locations in the table. It leaves the Web page open, so you can see that the correct data has been posted over the weekend.

• The macro then hops down to the bottom of the memo, attaches your boss's signature block, and then backs up a few lines—leaving room for your analysis—and exits. You return with your latte; verify the data is correct; type your analysis; choose File, Send To; and send it to your boss for approval.

If others in your company perform similar duties, you can easily distribute the memo template and the macro. Those using the macro only need to know that clicking the button, or choosing the menu item, creates the report. As long as the format of the Web page doesn't change, they needn't know a thing about macros or VBA, or even how to modify their toolbars or menus. You can do it all for them, easily, with a macro.

→ For more information about templates, see "Managing Styles and Templates".

Macros aren't confined to a single Office application. A good programmer with a solid knowledge of Word, Excel, and Access could write a sophisticated program for generating reminder letters based on values in an Access database. Then the programmer could update the database, print the envelopes, send e-mail reports to the sales force with summaries of the actions taken, and crank up Excel to have it generate aged balances and a Web-based PivotChart based on the same data.

What Shouldn't You Do with Macros?

Each individual Office application includes features that help you automate tasks without having to use macros. When there's a good alternative to writing a custom macro, the alternative is almost always preferable:

• For inserting boilerplate text into documents, workbooks, or slides, it's usually more efficient to use AutoCorrect or AutoText entries.

• Before you write a custom macro to find and replace characters, try Word's extremely capable Find feature.

• If you want to add complex paragraph numbering to a Word document, try custom fields first.

• Excel's automatic data-entry and list-management features can help you accomplish many complex tasks without having to work with VBA code.

→ For hints on how to bring in boilerplate text, see "Using AutoCorrect to Automate Documents".

→ To tailor a Find in Word, see "Finding and Replacing Text and Other Parts of a Document".

→ For details on Word's overpowered and underappreciated {ListNum} field, see "Managing Custom Numeric Sequences".

Eliminating Macros Entirely

Office XP allows system administrators to install Office so it can't run any macros, ever, under any circumstances. If the Visual Basic for Applications check box in Custom Setup is unchecked when Office gets installed, none of the VBA files—which are necessary for running macros—will be available on the machine.

This "scorched earth" approach might be appropriate for installations in which the threat of macro viruses outweighs the needs of users. In most cases, it severely limits Office's capabilities. In any case, neutralizing Visual Basic for Applications will not prohibit other (nonmacro) types of viruses from spreading.

If you can't get macros to work under any circumstances, check with your system administrator to ensure that VBA is operating properly on your machine. You might have to re-run setup and ensure that the Visual Basic for Applications box is checked in Custom Setup.

How Office Applications Store Macros

No two Office applications handle macros the same way. Although the precise details are complex, here's a quick summary of how each Office application stores macros:

• Word— Word can store macros in documents, templates, or the global template known as Normal.dot. When you open a document, macros in its associated template become available. If you store templates in the Startup folder, Word gives you access to macros stored in those templates whenever you start Word.

→ For details on template locations, see "Where Does Word Store Templates?,".

• Excel— Excel stores macros in workbooks or templates. Unlike Word, Excel does not maintain a link between a workbook and the template you use to create it; if you add or edit a macro in a template, that macro is available only in new workbooks you create with that template. Excel automatically opens all workbooks in the Xlstart folder when it starts, including the hidden workbook Personal.xls. Thus, all macros in Personal.xls are available all the time.

→ For details on templates, see "Customizing Excel".

• PowerPoint— PowerPoint stores macros in presentations and templates. Like Excel, PowerPoint uses templates only to create new files, so adding or editing macros in a template will not affect existing presentations based on that template. PowerPoint does not have a Startup folder or anything resembling a global template.

→ To understand the role of templates in PowerPoint, see "PowerPoint File Types".

• Outlook— Outlook stores all of its macros in one place, and all macros are available all the time. VBA/Outlook is a "version 2.0" product, and although there have been significant improvements made to its object model since Outlook 2000, you should anticipate significant problems working with it in this state.

• FrontPage— Similarly, FrontPage stores all of its macros globally, and they're all available, all the time. VBA/FrontPage is also a "version 2.0" product, and suitable precautions are in order.

• Publisher— New with Office XP, Publisher can store macros in each document. Oddly, the Visual Basic Editor only allows you to work with one document's macros at a time. This is definitely a "version 1.0" product—not for the faint of heart.

• Access— Access stores VBA programs in modules for the database itself (visible in the Modules pane of Database view) and in forms or reports. You can also collect VBA routines in a library database (*.mda file).

VBA/Access programs work with the Access interface: forms, reports, tables, and the like. You can also run old-fashioned "macros" by using the DoCmd command. Oddly, if you want to get at data stored by Access, you need to work directly with an Access database; for example, to bring live data from an Access database into an Excel worksheet or a Web page, use Data Access Objects (DAO).

Using Object Models

The fundamental building blocks of VBA remain the same, no matter which application you're using. An IF statement in VBA/Word, for example, works like an IF statement in VBA/Excel. That's one of VBA's great strengths, for as soon as you learn VBA with one Office application, you can apply much of what you know to the other applications—or even to non-Microsoft applications, such as Visio and AutoCAD, which use VBA as their macro language.

Still, VBA has to accommodate the differences in each application. You work with words, sentences, and bookmarks in Word, you use formulas, cells, and ranges in Excel, and you work with tables and reports in Access. Those parts of VBA that differ between applications are embodied in the object model for that application. The object model provides the means for working with an application.

Word's object model, for example, includes objects that let you create and change documents, paragraphs, and footnotes. Excel's object model works with workbooks, charts, and pivot fields. PowerPoint's object model has presentations, slides, and sound effects. Outlook's object model includes contacts and e-mail messages. FrontPage actually has two object models, one for Web pages themselves (with tags, themes, Webs, and the like) and the other for the FrontPage editor (with styles, text, and tables). The Access object model has reports, forms, and images.

The object model is important because it defines precisely how VBA can interact with an application. That, in turn, imposes limitations on how the macro recorder can work, because the recorder must generate a valid VBA program.