Back to previous pageReturn to Tutorials index

Speed up Microsoft Office
Free backup is finally here.  Mozy Remote Backup.
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 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.

Step-by-step: Create a macro to insert a watermark

Start recording
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.

Get ready made macros online

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 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.
Quick tips
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.

RadarSync BannerVIA_468_60.gif

Step-by-step: Transfer macros to another document or PC

All content copyright Support PCs and the original authors