Create macros for Office applications

Fed up with wasting time in Word and the other Microsoft Office applications? Then make your mark with macros

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.

Introducing the Visual Basic Editor
To view the sort macro in the Visual Basic Editor, press Alt and F11. This is a lot faster than the alternative of using Tools, Macro, Macros to launch the Editor from within the Macros dialogue box.

Like all macros, this one begins with a Sub line in which the name of the macro is declared and ends with an End Sub line.

What lies in between is the macro itself, which may also contain explanatory text that is ignored by Visual Basic provided it is preceded by a single quote on each line on which it occurs. In programming terms a macro is a subroutine, which can be invoked by a human operator (by means of a keystroke, for example) or from within other macros.

When typing in lines of code, the Visual Basic Editor adds spaces and capitalisation where necessary to maintain a uniform layout. To quit the Editor and return to Word use Alt and Q or the Close option from under the File menu.

Creating an Excel macro using Visual Basic
One of the most tedious tasks you can be faced with in Excel is having to change the format of imported data to match the rest of the workbook.

Let’s say there are hundreds of names in the format “Gates, Bill” and “Minogue, Kylie” and you’d like them to read “Bill Gates” and “Kylie Minogue”. This can be done by using Excel’s string functions to extract the surnames and forenames into temporary columns, recombining them with spaces between the names and then pasting them over the original data, but this takes a lot of setting up and it’s easy to make mistakes. A much better option is using a Visual Basic macro that goes through the names and corrects them without affecting any other parts of the worksheet. Here’s how to type in the code for the suitable macros:

For Each cell In Application.Selection
contents = cell.Address
If Len(Range(contents).Text) > 0 Then
temp = Range(contents).Value
x = InStr(temp, “,”)
If x Then
temp = Mid(temp, x + 2) & “ “ & Left(temp, x - 1)
Range(contents).Value = temp
End If
End If
Next cell

If you’ve had any experience in programming you’ll recognise this as a For/Next loop that goes through each selected cell and examines its contents looking for a comma. If it finds one, it takes the name following the comma, adds a space, and then appends the remainder of the name.

Fortunately, you don’t need to know how it works in order to use it. Just set yourself up with an Excel spreadsheet and type in a few names using the pattern surname, first name. Use Tools, Macro, Macros to summon the Macros dialogue box. Type Switch as the name of the macro and click Create. The Visual Basic Editor opens with Sub and End Sub lines for the Switch macro already in place. Type the code listed above between these two lines, pressing Enter after each line. Press Alt and Q to return to the worksheet, then save it as Names.xls with its macro in place.

Testing VBA macros
Before testing the macro, click Tools, Macro, Security to change the default security level for Excel macros to Low. This is perfectly safe for running your own macros, but not for those you download unless you have up-to-date anti-virus software in place.

To test the macro, select the list of names to be transposed and then summon the Macros dialogue box. Highlight the Switch macro and click Run. The transposition will be almost instantaneous unless you’ve made a mistake in your typing, in which case the results will be unpredictable and you might even be thrown into the Visual Basic Editor to correct the mistake.

More ways of running macros
When designing macros for other people to use, it’s best to provide a button or hotspot for them to click on, rather than expecting them to use the Macros menu or learn a new shortcut.

Using Word, we’ll call on FilePrintPreview (a built-in macro) to illustrate the technique. Open an existing Word document and, at the top of the first page, type ‘Double-click to see how this document will print’, then place the cursor where the clickable hotspot is required (in this case, immediately before the word ‘to’).

On the Insert menu select Field, and in the Field dialogue box scroll down to select the MacroButton entry. Type ‘HERE’ in the Display text panel, and from the list of available macros select FilePrintPreview. Click OK. Test the hotspot by double-clicking the word ‘HERE’.

Instead of a text hotspot, any form of graphic can be used: first copy an image onto the Windows clipboard and then return to Word. Press Alt and F9 to turn on the display of field codes. Highlight the hotspot word (‘HERE’ in the example) and then use Ctrl and V to replace it with the copied image. Size the image by dragging one corner, then turn off the display of field codes by pressing Alt and F9 again.

Excel is even more flexible in its approach to running macros. It’s possible to right-click any shape, AutoShape, inserted graphic or piece of Wordart and choose Assign Macro from the context menu. If a plain button is preferred, use the Forms toolbar: click on the button icon and drag one to size. A dialogue box is then displayed in which you can choose which macro should be attached to the button. If the Forms toolbar is not visible, invoke it by using the Toolbars option on the View menu.

Where to go next
There isn’t space in a Masterclass to teach Visual Basic programming, but it’s not hard to learn. If you’d like to have a go, download a free copy of Visual Basic 2008 from Microsoft’s Beginner Developer Learning Center (http://tinyurl.com/2w4ch2) and follow the free tutorials.

A web search for VBA macros will locate plenty of sites where you can download ready-to-use VBA code for use in your macros, but remember to leave macro security on the high setting before running them. Whatever you decide, you’ll save time and effort in the long run by developing macros now to automate the everyday tasks you need to perform regularly.