By Stephanie Krieger, document production expert
|Microsoft Office System 2003 Editions
Have you ever spent too much time on a repetitive task in a Microsoft Office program, suspecting that there must be a better way? Perhaps you needed to reformat multiple tables in a long Word document or reorganize data in an Excel worksheet. Or maybe you wanted to make the same changes to several similar Visio pages or PowerPoint slides. If these or similar tasks are familiar, then you need to know about macros.
You have probably seen macro warnings when opening Office documents, and so macros make you think of scary terms such as virus or programming. Well, you should know that most macros are not only harmless but they can be tremendous timesavers. And, macros are easier to create and use than you might expect.
On this page:
What is a macro?
A macro is a collection of commands that you can apply with a single click. They can automate almost anything you can do in the program you’re using and even enable you to do things you might not have known were possible.
Are macros a type of programming?
Macros are programming, but you do not need to be a developer or even have programming knowledge to use them. Most macros you can create in the Office programs are written in a language called Microsoft Visual Basic for Applications, usually referred to as VBA. VBA macros are the type addressed in this article.
When and why should you use macros?
Macros save time and expand the capabilities of the programs you use every day. They can be used to automate repetitive document production tasks, streamline cumbersome tasks, or create solutions such as automating the creation of documents that you and your colleagues use regularly. Those experienced with VBA can use macros to create custom add-ins that include templates, dialog boxes, and even store information for repeated use.
Consider the example used in this article for formatting multiple tables in a Word document. Let’s say that there are 50 tables in your document that need to be reformatted. Even as a power user, if it takes you just five minutes to format each table, that is more than four hours for that one task. If you record a macro to format the tables and then edit that macro to repeat the changes throughout the document, you can complete that task in a matter of minutes rather than hours.
How are macros created?
In many Office programs, you can create a macro either by recording a series of actions or by writing the macro.
Record a macro
Recording a macro is almost as simple as turning a tape recorder on and off. When you use the macro recorder, a macro is automatically written for you based on the actions you take. In Office, you can record macros in Word, Excel, PowerPoint, Visio, and Project.
For example, let’s take a look at recording a macro for the first task mentioned at the start of this article—reformatting several tables in a long Word document. You can apply a table style to accomplish a lot of table formatting, but you might also need to apply formatting that can not be part of a style, such as the width of the tables or the height of table rows.
To record this macro, start with your insertion point in the first table you want to format, then do the following:
- On the Tools menu click Macro and then click Record New Macro.
- In the Record Macro dialog box, as shown here, you can name the macro, assign it to a toolbar or keyboard shortcut for easy access, customize where the macro is saved, and add a description of the macro for later reference. Or, if you prefer, you can skip all of those steps for now and just click OK to begin recording.
- You can rename the automatically assigned numeric name for your macro. Macro names can include letters and numbers, but no spaces.
- By default, new macros you record are saved in the global template named Normal.dot. You can also save the macro in the active document or template, or in another custom template.
- You can assign a macro to be accessible from a toolbar or with a keyboard shortcut. If you skip this part, you can make these assignments anytime after creating the macro.
- The macro description includes the date and name of the user who is recording the macro. You can edit this description as needed.
After you click OK, a small toolbar will open containing two buttons, Stop Recording and Pause Recording .
- Take each of the steps you need to format your table. For example, you might apply a table style, set the width of the table to be 50% of the available page width, select the table, and then remove row height settings from all table rows. When you have finished applying any formatting that will be the same for all of the tables that you need to format, click the Stop Recording button.
- You can now click into any table in your document and then run this macro to automatically repeat all of the actions you took in the first table.
Note If you have not assigned the macro to a toolbar or keyboard shortcut, you can access it through the Macros dialog box. To do this, on the Tools menu, click Macro and then click Macros. Select your macro from the Macro name list and then click Run.
Write a macro
In the Office programs in which you can record macros, as well as in Access, Outlook, FrontPage, and Publisher, you can write your own VBA macros. To write a macro, you do need to know a bit about VBA, but you might be surprised at how easy it is to pick up because of what you already know just by using the program. VBA is all about automating the features you use every day. For example, in Word, VBA terms include such things as documents, tables, paragraphs, or sections.
Writing a macro, or editing a macro that you have recorded, gives you the ability to add more power to your macros. For instance, with the macro recording example above, you could edit that recorded macro by adding what is called a loop—simple VBA code that can enable the macro to apply the formatting to all tables in your document at once, rather than you having to run the macro separately for each table.
One of the best things about writing or editing VBA macros is that once you know how to write just a little bit of VBA in one Office program, you can use much of that knowledge to write or edit macros in the other Office programs. Terminology differs for specific features, but the language you need to know to set up a macro, or add features such as loops, is identical for any program that uses VBA.
Tip A good way to begin learning VBA is to record a macro and then look at the macro in the Visual Basic Editor. To do this, on the Tools menu click Macro, and then click Macros. Select your recorded macro from the Macro name list and then click Edit.
What about macro security?
It is a fact that, while most macros are both harmless and helpful, macros are an important security issue. When created with malicious intentions, macros can contain destructive code that causes harm to your documents or your system.
To protect your system and your files, do not enable macros from unknown sources. In order to have the option to enable or disable macros, but still have access to any macros you want to use, set macro security in your Office programs to Medium. This will provide you with the option to enable or disable macros anytime you open a file that contains a macro, but will allow you to run any macros you choose.
To set macro security in any Office program that offers VBA macros, on the Tools menu click Macro, and then click Security. Select your preferred Security Level and then click OK. Note that setting security to Low is not recommended.
Introducing macros in...
You can record or write macros in Word.
If you are a Word power user and comfortable recording macros, Word is an excellent place to start learning how to edit and write your own macros because Word VBA uses so much of the terminology that you already know and use every day.
Macros are very popular in Excel because they can greatly expand the functionality you use all the time. Excel macros can be used for everything from automating common tasks to creating custom functions.
For great examples of Excel macros that are all ready for you to use, check out these two Microsoft Knowledge Base articles:
If you have experience with recording and using macros in Excel and would like to begin writing and editing Excel macros, try this training course on loops.
PowerPoint, Visio, and Project
Similar to Word and Excel, you can record or write macros in PowerPoint, Visio, and Project.
Macros are important in Access because they can greatly expand the functionality of your databases. Though you can not record macros in Access, you can write code in Access VBA or create macros using a tool called the Macro Builder. Access provides several options, of which VBA is just one, to help you create the automation you need.
FrontPage and Publisher
Similar to Word, Excel, PowerPoint, and Visio, you can write macros in FrontPage or Publisher to save time on repetitive tasks or, in FrontPage, to create and use add-ins that expand the program capabilities.
About the author
Stephanie Krieger is a document production expert who specializes in creating solutions with the Microsoft Office System and is the author of Microsoft Office Document Designer. Stephanie writes for several Microsoft Web sites, including Microsoft At Work and Office Online, and frequently delivers Office System webcasts. Visit her blog for more Office tips.