Create macros for Office applications

Macros are stored lists of instructions that can be executed by clicking a button or using a shortcut key, and they’re available in every version of Microsoft Office.

In their simplest form, macros can be created by recording keystrokes and mouse clicks while performing a set of actions, but with the help of Visual Basic for Applications (VBA) ­ a programming language built into recent versions of Office ­ there is no limit to what macros can be told to do.

This Masterclass demonstrates how to use both recorded macros and VBA to automate everyday Office tasks. Although we’ve used Office 2003 to demonstrate how macros can be created, edited and implemented, the same techniques can be applied to earlier versions of Office and to Office 2007 ­ see ‘Macros in Office 2007’ at the foot this page.

All the programs in Microsoft’s Office 2003 family contain a simple macro recorder for keystrokes and mouse clicks, and this works fine if all you want to do is automate a repetitive process to save time, but the weakness of macros recorded in this way is that they can only do things that are possible using the standard commands and menus built into the program that created them. Macros created using Visual Basic for Applications don’t suffer from this constraint, so they can be used to create new commands and features that Microsoft itself didn’t include.

Macros created by recording actions are automatically saved as VBA programs, so they can be adapted and improved using the same Visual Basic Editor that is used to create VBA programs from scratch. This editor works identically in every Office application, so by learning to use it in one of them you’ll be able to use it in all the others.

Recording a macro
Using Word, here’s how to record a macro that sorts any document that’s in the form of a list. Start by typing a short list of items into a new document, then save it for later use. As always when recording macros, it’s a good idea to first work out the required sequence of actions and write them down. It’s also better to use keyboard shortcuts whenever possible, saving mouse clicks and movements only for those actions that absolutely require them. This limits the chances of mouse actions being misinterpreted when replayed.

The keystrokes for sorting a list and placing the cursor at the beginning of the document are: Ctrl and Home, Shift and Ctrl and End, Alt and A, S, Enter, Ctrl and Home. Try these out before recording the macro to see what each action does. To create the macro, open the Tools menu and select Macro, Record New Macro.

Give the macro a name with no spaces in it and optionally assign it to either a toolbar or a keyboard shortcut. Word macros can either be stored for use only within the current document or placed in the global template (Normal.dot). In this case, use Normal.dot to make the macro universally available. Click OK, then perform the list of commands. In the tiny toolbar that is displayed while this is going on, click the Stop Recording icon (it’s the one on the left) when finished.

The list has now been sorted and the macro has been created and saved. Close the document without saving the changes to the list, then reopen it. The list can now be re-sorted by invoking the macro you just created from its toolbar or by using its keyboard shortcut. If neither of these was assigned, press the shortcut key combination of Alt and F8 to display the Macros dialogue box, then highlight the new macro and click Run.

Macros in Office 2007
The sample macros used in this Masterclass work with Office 2007 as well as earlier versions of Office. Although Office 2007 has no Tools menu, the Macros dialogue box can be summoned from the Developer ribbon. If this is not available, click the Office button and select Word Options (or the options for whatever program you’re using), and in the Options dialogue box tick the Show Developer tab in the ribbon. Click OK. To save Office 2007 documents complete with macros for use with earlier versions of Office, use the 97-2003 format instead of the default.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!