Creating a Macro | Mastering Microsoft Project 2002

A macro is a series of operations that have been automated; the operations are executed when you click a button or press a specified key. As you can imagine, there are three primary reasons why users create macros: to automate infrequent and/or complex tasks, to efficiently execute frequent tasks, and to make applications accessible for less experienced users.

Managing infrequent and/or complex tasks There are many tasks within Project that aren’t performed frequently, but that still need to be performed. Some of these tasks include a series of steps that aren’t intuitive. If it were a physical task such as replacing your car’s timing belt or making tapioca pudding, you’d have a manual or recipe to follow. While some computer users might type out the list of steps in a Word document and then open or print the document each time they need to perform the task, that type of attention to detail isn’t common. A macro is a superior alternative to the Word document because the macro stores and executes the instructions themselves—it doesn’t just tell you what to do; it goes ahead and does it for you.

Efficiently completing frequent tasks As you use Project over time, it’s likely that you’ll find yourself changing the same settings in each new project file. For example, you might change the text on the Gantt chart bars to resource initials rather than resource names to give your Gantt charts a consistent, easy-to-read layout. It might take very little time to change the settings each time you create a new file, but that time adds up—fast. A better use of your time is the minute or so it takes to record a macro that you can run each time you start a new project file.

Making Project easier to use If you’re a project manager, you’re a busy person— and some of the tasks you set for yourself in Project could easily be assigned to your administrative staff. For example, there’s no reason why you have to print all those end-of-month reports; that’s a task your assistant could do. The problem is, your assistant doesn’t know how to use Project. You could send your assistant to Project training classes, or even buy him his own copy of this excellent book, but learning Project isn’t your assistant’s top priority. A better solution is to add a few macros to the program, and put them on a toolbar so your assistant can do what he needs to do by clicking a few buttons. This is how the use of macros can make Project more accessible for many users.

You create Project macros by using the Macro Recorder. The Macro Recorder is similar to a VCR, but easier to use. You record a series of actions and then play back the recording at some future time. To record the macro, you turn on the Macro Recorder, complete the steps you want to record, and save the macro. The next time you need to execute the same steps, you run the macro. It’s not much harder than that.

Preparing to Record a Macro

Before recording a macro, you should practice the steps you want to record. That’s because after you begin recording, all your actions are recorded—mistakes included.

Take note of the conditions under which your macro will operate, and set up those conditions beforehand. For example, will you always use the macro in a specific document? If so, open the document. Will the macro be used to change or format selected text or numbers? If so, you should have the text or numbers selected before you begin recording the macro, just as you will when you run the macro at a later time.

In other words, set up the environment in advance; then practice the operation.

When all is ready and well rehearsed, select Tools Ø Macro Ø Record New Macro to open the Record New Macro dialog box, shown in Figure 24.1.

click to expand
Figure 24.1: Project’s Record Macro dialog box

The suggested name for your new macro is “Macro1.” (Microsoft didn’t use a lot of imagination here.) You should enter a name for the macro that’s more descriptive than functional. For example, CorporateHeaderFooter is better than ChangeHeaderFooter because the name provides a clue as to what gets changed in the header and footer.

Visual Basic names, including macro names, can be up to 255 characters long. The names can contain numbers, letters, and underscores, but not spaces or other punctuation, and they must begin with either an upper- or lowercase letter. Visual Basic preserves your capitalization style; it is not case-sensitive—that is, FormatGanttChartWithInitials and formatganttchartwithinitials are the same name, but one is more difficult to read.

Note

If you miss having spaces between words, you can use underscores; but the Initial Caps case used in the previous examples is preferred by many programmers.

If your organization uses a naming convention, you’ll probably prefix the macro name with mcr for macro or bas for Visual Basic code. (Some organizations use only the bas prefix, whether or not you create the macro with a macro recorder. Check your in-house standards if you’re not sure.)

Next, you should enter a description for your macro. If other users will have access to the macro—which ultimately happens unless it you add code to have the macro self-destruct if you go on vacation—you should also include your name and contact information (extension or e-mail address) in the description.

To be able to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut Key box. You can use the Ctrl key in combination with either upper- or lowercase letters, although you can’t use numbers or punctuation marks, or any key combination that is already used in Microsoft Project. If, for example, you try to assign the letter P (as in Ctrl+P), Project will notify you that the letter is already used for another function (printing).

Actually, most of the “best” letters are already used, and the Ctrl+Shift combinations (for uppercase letters) invite problems. Users easily forget to hold down the Shift key, so they end up printing the Gantt chart instead of formatting Gantt chart bars. Later in this chapter, we’ll show you how to attach macros to menus and toolbars, which is our preferred method. You can include shortcut keys for menu selections if you or the people you’re creating macros for prefer the keyboard to the mouse.

Storing a Macro

Still in the Record Macro dialog box, pull down the Store Macro In drop-down list and then select the document in which you want the macro stored. A macro’s storage location determines how you’ll be able to access and run it later.

If you select the current project, the macro will be available only in this project. If you want the same macro available in another project, you have to copy it to the other project (in the Visual Basic editor or the Organizer), or re-create it from within the other project. Macros that are stored in a project, including a template, are called local macros.

A better alternative might be to make the macro available to all your Project files. You can do this by selecting Global File from the Store Macro In list. This stores the macro in the Global template, which creates a global macro that is available to all your Project files.

From the description of global macros, you might think that you should make every macro global—but don’t overuse this option. All the global macros you create are loaded each time you launch Project, which takes up valuable memory space. In addition, any macro names you use globally can’t be reused in individual projects. Unless a macro is going to receive wide usage, it’s best to store it in the current project.

Mastering the Opportunities: Macro Storage and the User Interface

The decisions about where to store Project macros and where to place macro commands on toolbars and menus are directly related. Users should always be able to find the buttons for macros that they need to run. And they should be able to see buttons only if the macros are available. Clicking a button for a macro that Project can’t find results in an error message.

For example, let’s say you’re creating macros to automate a project file that will be distributed to workgroup members in a variety of locations. In this case, you should store the macros locally in the project file you’ll distribute. The buttons that users click to run the macros should be displayed in the distributed project, and they should not be visible if your client switches from your project to another open project file. To achieve this, you can create a custom toolbar that will be turned on when the workbook opens and off when it closes. (See Chapter 27 for information on displaying and hiding custom toolbars with VBA.)

You may create other macros that everyone in your workgroup uses for project files. For example, you might create a macro that adds a custom footer including the date, time, filename, and the text “© All rights reserved” with the year and your department name. This is a good candidate for a global macro. For information on customizing or creating Project toolbars, see Chapter 23.

Setting Absolute and Relative References

Macros are not necessarily smart programs. They tend to follow your instructions exactly, so you have to be precise about what you want them to do, and where. This creates some issues when you’re working with rows and columns, as you might when selecting data from a timesheet.

If you select a cell while recording your macro, when you run the macro, the macro selects the same column (field) each time it is run, regardless of which cell is first selected. The Macro Recorder records an absolute reference to the column (“go to column 4,” as opposed to “move one column to the right”).

That’s the good news.

It would be nice if it worked the same way for rows, but it doesn’t. By default, the Macro Recorder records movement between rows as a relative reference (“move down 2 rows” rather than “go to row 3”).

Fortunately, you can specify whether you want absolute or relative references (separately for rows and columns) from within the Macro Recorder dialog box. All you have to do is select the appropriate options from within the Row References and Column References section of the dialog box.

If you want a macro to select columns or move to a column relative to the position of the active cell, choose the Relative option in the Column References section. If you want your macro to select the same row, regardless of the position of the cell pointer when you run the macro (for example, row 3), choose the Absolute (ID) option in the Row References section.

You don’t have the same relative/absolute flexibility here that you have in a spreadsheet program such as Excel; the entire macro is recorded with the settings you choose in the Record Macro dialog box. You can, however, change the settings for the next macro you record. The default settings are restored each time you start Project.

Recording Your Actions

After you set the options in the Macro Recorder dialog box, it’s time to start recording.

To start recording, click the OK button. A Recording message displays at the left end of the status bar to show that you are recording a macro. The Stop Recording toolbar also opens. Now it’s time to start performing the actions wanted to record.

Fortunately for us with fumble fingers, the macro recorder records all the actions you take, but not the delay between actions. This means you can go slowly, take your time, and get it right. If you want the macro to enter text, enter the text now. To include menu commands in the macro, just make menu selections as you normally would. The macro records whatever you do, whether you use the keyboard or the mouse, in the order that you do it.

When you’re done performing the desired actions, click the Stop Now button. The macro stops recording and saves the macro as previously instructed.

Recording a Sample Macro

Here’s a quick and easy macro to get you started. The macro is titled mcrFormatGanttWithResInitials, and you use it to switch the standard display of resource names on the Gantt chart to a display of resource initials only. To record the mcrFormatGanttWithResInitials macro, follow these steps:

Open a blank new project.
Select Tools Ø Macro Ø Record New Macro to open the Record New Macro dialog box.
Enter mcrFormatGanttWithResInitials in the Macro Name field.
Pull down the Store Macro In list and then select Global File.
Select the Relative option in the Row References section and the Absolute (Field) option in the Column References section.
Enter any descriptive text in the Description section.
Take a deep breath, and click the OK button to start recording. Everything you do from here out will be recorded.
Right-click in an empty area of the Gantt chart and then choose Bar Styles from the shortcut menu:
When the Bar Styles dialog box appears, click the Text tab at the bottom of the dialog box.
Choose the Resource Initials from the Right drop-down list:
Click OK to close the Bar Styles dialog box.
Choose Tools Ø Macros to open the Macros menu. The Stop Recorder button on the menu is enabled:
Choose Stop Recorder to stop recording.

You don’t need to do anything special to save the macro now. Local macros are saved when you save the current project. Global macros are automatically saved when you close Project.

Tip

When you’re recording a macro and want to record text formatting, use the formatting options in the Format Font dialog box rather than clicking toolbar buttons to select font, font style, size, and alignment. If you use the buttons, the results when you run the macro will be unpredictable because the toolbar buttons are toggles. For example, if selected text is already italicized, clicking the Italic button turns Italic off—and you might want to turn Italic on. The options recorded from the Format Font dialog box are absolute.