Your mother is probably the first person to tell of the importance of planning. Mom was right. So was the Boy Scout Troop leader who told you to “be prepared.” Similarly, you were taught about the need for planning and preparation from many historical figures and fictional characters. Do these sound familiar?

“It does not do to leave a live dragon out of your calculations, if you live near him.“
– J.R.R. Tolkein

“Give me six hours to chop down a tree and I will spend the first four sharpening the axe.”
– Abraham Lincoln

“By failing to prepare, you are preparing to fail.”
– Benjamin Franklin

“A man who does not plan long ahead will find trouble at his door.”
– Confucius

So, with such sage advice I am a bit bothered when I hear or read of software trainers who tell students that the way to create a macro is to turn on the recorder, do your actions, and stop the recording. I am bothered because they started with step 3. What about steps 1 and 2, the planning steps? Recording a macro without planning is recording a macro that will fail. Soon you “will find trouble at [your] door”, so do not be surprised when that trouble proves to be a macro dragon that can damage your document and spoil your spreadsheet calculations.

The 5 steps to properly create a macro are:

1. Know what you want.

2. Know how to do it without a macro.

3. Record a macro.

4. Test the recorded macro and edit as needed.

5. Share the macro with others.

As mentioned earlier, the first two step are the planning steps. The rest of this blog post will discuss them in more detail.

Step 1 – Know what you want

First you need to know what you want. For example, if you want a macro that will create an Excel chart, then you need to know what that chart will look like. Will it be a bar chart, or a pie chart? What colors will it use? Will it have a legend? Will it have labels? These and every other aspect of the chart need to be planned before you can create a macro that will build the chart.

Another example: In Microsoft Word, if you want a macro to insert a footer, then you need to know what the footer will contain, and what it will look like. Will it contain the document title? Will it contain page numbers? If so, will it also contain the total number of pages in the document? Where will each of these pieces go – the left side, right side, or in the middle? Which font and what font size will you use? Will the font be in black, red, green, purple or some other color? Again, before you create a macro that will create your footer, you need to know exactly what you want that footer to look like.

Step 2 – Know how to do it without a macro

The second step in planning and developing your macro is knowing how to do the command that will achieve your goal. Why? Because when you get to step 3, record the macro, you will need to execute those commands. And if you don’t know what those commands are, and the proper order in which to execute them, then you will not be able to record a macro that executes those commands. In other words, if you don’t know how to create an Excel PivotTable, then there is no way you can record an Excel macro to create a PivotTable. Similarly, if you don’t know how to perform a Mail Merge in Microsoft Word, then there is no way you can record a macro that will generate those lovely “personalized” letters for each person in your mailing list.

Complex but not necessarily complicated

Does this sound complicated? It can be. Often, it is not complicated, even though it is usually complex. What the difference? Complicated means hard. Complex means there are multiple steps. Remember when you first taught your child how to tie their shoes? Tying shoes is not hard, it’s not complicated, for an experienced adult. But as you taught your child, you probably realized that there were quite a few steps involved, steps you have memorized and don’t even think about. But then, when you taught your little one, you realized there are quite a few steps. In other words, tying shoes is complex but it’s not complicated, at least not for you.

The same is often true with macro creation. There are a lot of repetitive steps that you perform in Word and Excel that could be turned into a macro. But in order to make the macro, you have to go back and remember each little thing that you do, so when you get to step 3, you can record the macro to do exactly what it should.

Next time we will discuss step 3, recording the macro. Until then, happy planning.

If you would like to learn how to create macros in a group training, consider signing up for the TechMentors Excel VBA Macro class. In it we will review best practices for planning, recording, testing and editing macros. You will learn the VBA programming language, and will be on your way to automating your work.