Revolutionise the way you use Microsoft Office and save time with the help of macros
hould you find yourself performing the same tasks again and again you'll be glad to know that there are ways and means of reducing a series of repetitive commands to a single click. Microsoft Office offers a system called macros whereby you can record a series of commands you perform - such as selecting an option from a dialog box and then formatting a piece of text - and store them as a single option.
Once created, the macro can be placed on a toolbar, in a menu or given a keyboard shortcut - activate it and the commands you've recorded will be automatically applied as if you'd performed them separately.
Three Office applications support the use of macros: Word, Excel and PowerPoint. In this tutorial we'll show you how to record macros from scratch, make them easily accessible through your Microsoft Office ribbon, menus or toolbars, and reveal some useful tips for managing them.
Before you start
When you start recording a macro, everything you do in the Office application in question will be stored as part of that macro, so it make sense to plan your macro out in advance. Familiarise yourself with the commands you wish to assign to the macro and make sure you don't do anything else during the recording process - it's worth practising a few dry runs now before you get started.
If you want to create a macro that you can perform on a selected part of your document - whether it's text, cells or whatever - make sure you've highlighted whatever it is you want to change before you start recording the macro. This ensures that the macro will work on any selected part of future documents.
Set up your first macro
Once you're ready to commit your actions to a single macro, choose Tools > Macro > Record New Macro (Office 2007 users will find the Macros dialog on the View ribbon). The dialog you see will vary depending on whether you're running Word, Excel or PowerPoint, but the principles are the same.
First, you need to give your macro an easily recognisable name for future reference - note that you can only include letters and numbers, not spaces or special characters. Macro names are case sensitive, so one way to get around this is to start each new word with a capital letter: for example, InsertLogoRotateCrop.
Macros are actually stored within your documents themselves, and the major difference between Microsoft Office applications is which document you can link the macro to. In PowerPoint, you can choose to apply the macro to any presentation that's currently open. The same applies to Excel and Word, but they have an extra option. If you choose to assign the macro to your Personal Macro Workbook (Excel) or normal.dot template file (Word), then it becomes part of the application itself, enabling you to use the macro on any document you're working on.
Only Word gives you the option of placing your macro on a toolbar or menu before you start recording it - click the Toolbars or Button button to do so. As you can do this after you've recorded the macro in Excel and PowerPoint, we'll explain how this works later.
Both Excel and Word also give you the opportunity of assigning a keyboard shortcut to your macro for easy access in the future. In Excel you're limited to picking shortcut using the [Ctrl] key (for example, [Ctrl] + [K]), but in Word you're given a wider choice by clicking the Keyboard button.
The last thing you can do before recording your macro is to add a description to it. You're free to type anything into this box, and it may be useful to explain how your macro works for future reference.
I'm trying to run a macro, but Excel tells me that my security levels are set to high, and so it's been disabled. Why is this and how do I get around it?
The reason macros are considered potentially dangerous is because it's possible to code macro viruses, which will obviously wreak havoc if they're allowed to run. It's possible to lower Office's security settings so that you're prompted whenever a macro attempts to run, letting you decide whether or not it will be allowed to do so.
To do this, choose Tools > Options > Security tab. Click Macro Security and set the level to Medium.
Create a macro to insert a watermark
1. INSERT THE IMAGE
Once you've set up your macro, click OK to start recording. Insert your chosen image from the Insert > Picture menu. Press the left cursor key followed by [Ctrl] + [Shift] + right cursor key to select the image with your keyboard.
2. CONVERT TO WATERMARK
Choose Format > Picture. Select Washout (or Watermark) from the Color: drop-down box and click OK. Now select Insert > Text Box followed by Format > Text Box > Colors and Lines tab. Select No Line from the Color drop-down menu.
3. PLACE WATERMARK
Switch to the Layout tab. Select Behind Text and click Advanced. Untick Move object with text and use the alignment controls to position the picture in the centre of the page. Click OK followed by the Stop button to finish your recording.
4. MACRO CHANGES (WORD 2007)
Format the picture by right-clicking it. Choose Insert ribbon > Text Box > Draw Text Box to create the text box. Remove the line by clicking Shape Outline > No Outline, and format the box by clicking Text Wrapping > More Layout Options.
You're now ready to record your macro, so click OK. A tiny toolbar will appear with one or two buttons: the stop button is universal, while Excel has a relative reference button, and Word a pause recording button. The latter is accompanied by your cursor changing to a small cassette while recording is in progress - press pause, and the recording stops momentarily until you're ready to continue. When you've finished recording your macro, press the Stop button and it'll stop automatically.
Macros record mouse clicks such as selecting an option from a menu or clicking an item on a toolbar and key presses only; they don't record mouse movement. If you want to move the cursor around your document, you'll need to do so using the keyboard - for example, pressing [Ctrl] + the left cursor key moves the cursor to the beginning of the previous word; pressing [Ctrl] + [Shift] + the right cursor key would then select that word. Check the Office help file for a complete list of keyboard shortcuts.
You may find some commands won't work: for example, if you attempt to use the Layout tab of the Format Picture dialog while formatting a graphic you'll find its options are greyed out and unavailable. This is why the step-by-step guide to creating a watermark above places the image inside a text box.
Once you've finished recording, you can test the macro from the Tools > Macro > Macros menu - select your macro and click Run. If it doesn't perform as you expect, delete it and start again. It's possible to edit macros using the built-in Visual Basic tool, but this is very complicated and beyond the scope of this article. It's actually quicker to start again from scratch.
Are there any circumstances in which I shouldn't create a macro?
There's nothing to stop you from creating any kind of macro that you like, but before you do so, ask yourself if creating a macro is the best way of achieving what you want to do. For example, if you want to quickly insert a block of text into a document, why not create an AutoText entry instead using the Insert menu? Similarly, select Format > Styles and Formatting to quickly apply different styles to blocks of text.
On the other hand, if you wanted to create a specific block of text such as an address that was always formatted a particular way, you could create a macro to simplify the process for yourself - either by doing it manually, or by using the AutoText and Styles and Formatting menu in tandem with each other.
Get ready made macros online
Macros can be as simple or as complicated as you like. More complicated macros add new features to Microsoft Office and are also known as add-ins, although if truth be known they're just glorified, complicated macros that have been painstakingly developed using Office's built-in Visual Basic programming language.
You'll find that many add-ins and macros are available online for download, and while many are shareware, there are quite a few excellent freebies you should take a look at.
Excel users should start their search at here, where you'll find ASAP Utilities currently offers over 300 tools and utilities in one handy package, all accessible from a new menu that's added to Excel itself. It's free for personal use.
Word Boosters (click here) comes in free and shareware versions - the free version gives you eight macros (30 are available if you pay the US$12 fee), including a formatting clipboard, window manager and quick-find toolbar.
Finally, PowerPoint users should look at the PPTools StarterSet (click here), which bundles together seven tools, including zoom, text and property info tools, plus diagnostic and font repair utilities. PowerPoint 2007 users should read more about bugs and incompatibilities before plunging in.
Access your macro
While it's possible to access your macros from the Macros dialog, it's more convenient to place your macros on a toolbar or inside a menu so you can run them with a single click. Office 2007 users should click the Customize Quick Access Toolbar button next to the toolbar above the ribbon and choose More Commands. Select Macros from the Choose commands from drop-down box, select your chosen macro and click Add.
In earlier versions of Office, choose Tools > Commands. Select Macros from the Categories: box and you'll see your macros appear in the right-hand menu. If you want to add one to a toolbar or menu, just drag it into place on the toolbar or menu in question. When you drag it on to a menu title, the menu will pop up, enabling you to place it exactly where you wish on the menu itself.
If you want to rename the display name of the macro on the menu, just right-click it and type in the new name - use the & character in front of a key you wish to assign as a shortcut when the menu is open (for example, Insert &Clipart would assign the C key to the menu command as its shortcut).
You can move your macro around the toolbars or menus at any time from this dialog - just click and drag. And should you wish to remove a macro from the menu or toolbar, just right-click it and select Delete - this won't delete the macro itself, but merely removes it from the menu.
Organising your macros
The Macros dialog is best used for managing for macros. Press [Alt] + [F8] to quickly access it. From here you can delete unwanted macros or edit them in Visual Basic if you're proficient in that language.
Once again, Word's superior handling of macros is epitomised by an additional option: the Organizer button. Click this and you're able to transfer macros between documents, which is essential if your macros are stored in your normal.dot template file and you wish to edit a document on another computer with the help of a macro or two. The step-by-step guide below reveals how.
If you wish to undo the effects of a macro you need to perform a little more work than just selecting Edit > Undo or pressing [Ctrl] + [Z]. This is because your macro is not treated as a single command, so you need to undo each step the macro performed to completely remove its effects.
The quickest way to undo the effects of a macro is to click the drop-down arrow next to the undo button on the standard toolbar - you can select all of the steps performed by the macro and remove them with one click.
Microsoft Word and Excel users can also assign keyboard shortcuts to macros. In Word, select Tools > Command, choose Macros from the Categories list and highlight your macro before clicking the Keyboard button. Click inside the Press new shortcut key box and hold down the keys you wish to assign to this shortcut - you'll be warned if they've already been assigned to another command, but click Assign and they'll be routed to your macro instead. In Excel, select Tools > Macro > Macros. Select your macro and click Options to assign it a keyboard shortcut.
Transfer macros to another document or PC
1. OPEN THE MACRO ORGANIZER
Press the [Alt] + [F8] keys together and click the Organizer button. You'll see two panes appear - If the file you wish to transfer the macro to is already open then it'll be accessible from the left-hand drop-down menu, so select it and jump to step C.
2. SELECT THE TARGET FILE
If the target file isn't already open, click Close File followed by Open File. Browse to and open your target document (you'll need to select your document type from the Files of type drop-down menu before you can see the file you wish to access).
3. TRANSFER MACRO ACROSS
Pick your target file from the left-hand drop-down menu. Select NewMacros in the right-hand list and click Copy <<. Finally, click Close and select Yes. The macros will be transferred to the file in question - copy or email this to your target machine.
4. COPY MACRO TO TARGET MACHINE
Open the file in Microsoft Word on your target machine. Open the Macros Organizer and follow steps two and three to transfer the macro from your open file to the normal.dot template file on your target machine to make it available to all new documents.