Introduction to Macros: Automating Repetitive Tasks

An obscure and often overlooked button on your Microsoft Office toolbar—a thing called “macros”—can save you time, tedium, and trouble when you need to do a repetitive change across a lot of text or data. The macro I will describe below cut my work time by 97%!

I discovered the power of macros more than 25 years ago, when my employer—an international computer corporation—needed me to work a miracle. Our company would be the first in our field to launch a Windows version of our software. Only one glitch: tens of thousands of software documentation files that had to be reworked to match the new “GUI” screens. Managers estimated it would take three people three years to do the work. But there was just one of me, a new hire. And they needed the work done before the rest of the industry caught up with us.

Thanks to this marvelous thing called macros, they got their miracle. In less than three months, I had it all done. I got to be my company’s Employee of the Year, 1994, with an all-expenses-paid trip to California and a nice raise.

But the best perk of all was learning macros on company time.

I have used them ever since for more purposes than I can recall. Macros save time and tedium and error, making large projects manageable. For genealogists—with our masses of documents, variations of software, and the trading of materials with so many people—macros should be considered a standard tool of our work.

What is a macro?

A macro is a script or program that automates a set of tasks that need to be repeated. While there is a programming language behind any macro, the macro language in Microsoft Office allows you to do many things without knowing or seeing the programming code at all. You can simply record the steps you take in doing something the first time and then repeat the process as many times as needed, by pressing a keystroke combination.

A desired change needing a macro

While a simple change to a Microsoft Word or Excel document can usually be handled with the search-and-replace tool, we often want to make more complex changes to a series of documents or sections within documents.

For today’s project let’s look at data my research software dumped out for me. It includes hundreds of tables, looking like this:

Let’s say I want to make the tables more readable for a booklet I am creating. I want them to look like this:

Multiple tasks required

To get the table the way I want it, I need to do each of these eight tasks:

• Change the font and font size.
• Remove the spacing between cells to eliminate the double lines.
• Increase the cell padding, so the words are not so close to the borders.
• Alter the border style to a solid blue line.
• Turn off the feature that alters column size based on content, so that all of my tables will have columns of exactly the same width.
• Change the width of the first column to 1.2 inches.
• Change the text in the first column to blue.
• Right-justify the first column.

All that takes about a minute and a half per table, once you have it down to a routine. Making the changes once would only be mildly tedious. Making the changes 10 times, 30 times, or 100 times, might make you rethink whether you care enough to fix the hard-to-read tables.

This is what macros were made for.

Recording a macro

Once I create a macro to do the eight tasks, I can reformat a table simply by clicking in it and pressing a keystroke combination to run the macro. No programming required!

For reference, I am working on a PC with Windows 10, using Office 365. I assume things work similarly in other versions.

Here’s how I do it:

Step 1: Make a copy of the document I am revising. I will always work on a copy, just in case the macro does something unexpected. Standard Operating Procedure.

Step 2: I click in the first table I want to revise.

Step 3: I click Word’s View menu, then click the lower half of a button labeled Macros, selecting Record Macro.

Step 4: In the “Record Macro” dialog box, I choose a Macro name. I’ll call it “ReformatTables.” Then I click the Keyboard button to assign the keystroke combination I will use to repeat this macro as often as needed.

Step 5: In the “Customize Keyboard” dialog, I put my cursor in the field labeled “Press new shortcut key” and click on the keys I want to use to trigger the macro in future. Whatever I choose, Word will show me if the combination is already in use for another purpose. If it is, I can choose to override it by continuing, or I can choose another combination. I choose to use a combination that is not in use (and not likely to be). I use Alt+Ctrl+Shift+Z. The dialog tells me it is “Unassigned,” and I click the Assign button, then Close.

Please note that you can choose to create a macro without assigning keys. It is just more awkward to run macros through menu selections.

Step 6: The system will now record everything I do, until I tell it to stop recording. I will now go through all the tasks above, keeping these things in mind:

• You cannot use your right-click menus or drag-and-drop features, while recording a macro. Use menus and toolbars.
• I have found it best to open a dialog and do one thing, close the dialog, then do the next—even if it occasionally means I am opening the same box more than once.
• There are a few glitches in the macro-recording tool that you work around as you go. In the task list above, the macro does not pick it up when you click on the text color button on your toolbar and pick one of the displayed colors. It does pick up the color change command, if you click on the drop-down arrow on the color button and choose “More Colors.” Pick your color that way. But most of the commands work as expected.

Step 7: I can stop the macro by going back to the “View” menu, clicking on the arrow beneath Macros, and choosing Stop Recording. But there is also now an icon at the bottom of my document window with a black square on it. I can click on that to stop the recording—and that’s my preference.

Using the macro

Now I can click into any of the other tables in my document, hold down the four keys together (Alt+Ctrl+Shift+Z) and watch Word do the work for me.

It takes less than three seconds to convert a table this way—reducing the time required by 97%.

Behind the scenes with the macro

You saw me do no programming in the exercise above. But my actions were turning into a program in the Visual Basic for Applications (VBA) programming language. When you do your macros, you will be programming, too, without knowing it.

A little knowledge of VBA lets you go much farther than what we did above. You could program VBA to search for each table and do them all—one keystroke combination to complete a hundred tables. You can use VBA to open a series of documents, doing things in all of them. Or you can extract information from documents and compile the information in other documents in a new form. With VBA you can alter what the program will do, based on what it finds or on how you answer a question it asks you.

Macros are the secret to converting old documents to a new form—more usable, more beautiful, more accurate—whatever the change is that you desire.

And once you know how to do macros in Word, you can use them in and between Microsoft Office applications. I’ve used them in Word, Excel, Access, and Outlook.

Learning to use macros was one of the smartest things I ever did. Twenty-five years later, I still use them all the time. Let me know how you’re using them or how you want to use them. I suspect there is the fodder for many more blogs between our experiences.

Share...