Microsoft Project: 16 – Macros

In September we had another look at customizing fields. This month we’ll have a look at customising your projects by use of macros. A macro is simply a coded program that runs through a series of commands to the computer to achieve a desired result without input from the user. A dictionary definition is:

mac·ro [mákrō]

Microsoft® Encarta® Reference Library 2003. © 1993-2002 Microsoft Corporation. All rights reserved.

No, we’re are not going into the aspects of vba coding, though what we do is, in fact, vba code!

Setting

You can try macros on any project you have, but again, to give me something to work on, I’ll use the file that we used in September: “Bridge 15 Indicators.mpp”, or clicking on this link:

http://www.mousetrax.com/pub/Bridge15.zip

Planning

Like all programming, a good plan will speed up and improve the accuracy and efficacy of any coding input. So, before attempting to write any macro, make a list of where you are starting from and what you want to achieve. Then run through the steps yourself making note of what you did. In this simple example we’re going to create a macro to change the font of the text in the table in the Gantt Chart view

1. View/Gantt Chart and View/Table: Entry.

2. Start with the cursor in the top left-hand corner of the table (always a good starting point: Row ID 0, Task name cell, which is the project summary “Bridge 15 Costed Indicators”).

3. Select All, Format/Font and select Comic Sans MS, Italic, Size 12, coloured green.

4. Re-size the column widths to best fit (necessary as that larger font will take up more space and the dates will be ###).

5. Finish with the cursor in the top left-hand cell (this will remove any highlighting so you can see the result).

Recording the Macro

Thankfully, Microsoft has provided a recorder for us that will produce the vba code for the keystrokes and mouse clicks we make. So, to start with, click in the top left cell and then select Tools/Macro/Record New Macro…

The Record Macro dialog will open. You can give it a Macro Name, a Shortcut key and a Description. I always advise simple word(s) for the macro name so that you can find it easily in the list of macros that you may write. Similarly, it is very easy to forget what a macro is for and what it does when you have recorded many of them. So write a comprehensive description in the box.

I’ve called it “Font 12” and written in a description.

On clicking OK, an error message will show the limitations of a valid macro name.

Simply rename the macro to “Font_12” .

Clicking OK will now start the macro recorder. Be advised that key strokes or mouse clicks will be automatically recorded. Whilst it doesn’t matter if you make a mistake and then correct it, but the error will be recorded! However, as you can see later, you will be able to edit the macro to remove any errors. Much better to create a detailed plan of what you want and then follow it!

Record Macro

Click the All button to highlight the whole table.

Then Format/Font and select Comic Sans MS, Italic, Size 12, Green.

Click OK. Click top left cell to remove the highlighting and you will see the font change accordingly. Because of the enlargement, there is insufficient column width to show the Durations and Dates. Double click the boundary between Duration and Start to best fit the data in the column, and repeat with the Start and Finish boundaries.

To finish, Tools/Macro/Stop Recorder.

Now in this example we will have to reset the font back before we test the macro otherwise we will not see it operate. Select All and then Format/Font and chose Arial, Regular, 8, Automatic (colour), OK, and click the top left cell to clear the highlighting.

Test

To test our macro, Tools Macro/Macros… and in the Macros dialog, select the macro you’ve just recorded: Font_12. Notice that the Description includes the text we added above and helps to identify the correct macro and what it does. Now click the Run button.

The macro should produce the result we’ve planned – neat eh? If it doesn’t, then you can edit or delete it and start again. In any event, let’s have a look at the macro code. Repeat the above steps: Tools/Macro/Macros… and in the Macros dialog, select Font_12 but click the Edit button this time. You should see the Microsoft Visual Basic editor (maximize the window) with the macro code showing.

All macro code is between the words “Sub” and “End Sub” and the initial Sub gives the macro name – Font_12 () in our case. Then some lines in green. Note that each green line begins with an apostrophe – this is the VB way of showing any notes that you’ve written that will not be executed as code, and you can edit this at will and add further statements throughout the code to explain what’s going on. Again, in this case it shows the description of the macro. The black lines are the executable code, which are, I hope self evident. If your macro didn’t run properly, here’s the place to edit it to look EXCATLY as in the picture – make sure all punctuation is precise too. If you wish to experiment, as an example, edit the Size:=”12” by changing the 12 to 30, then close the window and re-run the macro, then edit it back to 12 again. Whilst you’re experimenting, try creating another macro to put the font back to the original Arial, Normal, 8, Automatic. You can then switch between one and another.

Customize Tool Button

For our final trick, as it requires several keystrokes to activate the macro, let’s create a tool button to do the activation. Tools/Customize/Toolbars…/Commands tab and select All Macros on the left and Font_12 on the right.

Now click and drag on Font_12 pulling it up onto the Standard toolbar – but don’t go away yet.

Whilst you’re there with the Customize dialog open, right click on our new tool button and select Edit Button Image…

This will open up the button editor. Note the box where you draw the button’s picture, the move buttons to centralize what you’ve drawn, the Colour selector and the little button that shows a preview of your artistic efforts.

So, click on a colour, in this case, perhaps, choose the green of the font colour. Click in the Picture to colour the pixel boxes, creating the number 12. You can click and drag to speed the process of filling the pixels. If you make a mistake, click Erase and then the incorrect pixel to revert it back, or more simply, just click the wrong pixel to reverse the colouring.

OK will put the new image on the button. If you don’t want the text to show, right click the button again and select Default Style.

After clicking the Close button, you will now see a tool button with 12 on it. Click that and your macro will run – even more cool eh?

Conclusion

This very simple macro shows the principles behind automating common tasks. Writing macros directly into the VB editor is perfectly possible and can make Project do almost anything you want. If you don’t know the code, try recording the macro with all the keystrokes and mouse clicks to see what code that produces. Edit it if necessary to get what you want – don’t forget to add in frequent notes to explain what is going on and why. With macros, the sky it the limit! If you want help with writing macros, try posting to the newsgroup: miscosoft.public.project.developer, where the experts hang out. Also, you might like to visit the Project MVP site at: Project MVP VBA pages.

Have fun!

Next Issue

In the next issue I plan to have a look some of the ways you can manage multiple projects.