This is the second blog post in a series about creating Excel macros, Word macros, and other macros for applications in the Microsoft Office.
The big lie:
Many people have been told that creating a macro is easy. They are told just turn on the recorder, do your actions, and stop the recording. While those steps are part of the process, they are not the whole process. And those who only learn those steps invariably become frustrated because the macro doesn’t do exactly what they want, and they have no idea how to understand the code that the macro recorder generated. The purpose of this article is to give you a “bird’s eye view” of the big picture, or an overview of the steps a professional macro developer uses. This article won’t give you the nitty-gritty details you will need, those will come in future blog posts. But you will get an idea of how important planning is to creating macros that will save you time and money and will make you a lot more efficient.
The 5 steps for creating macros:
Professional macro developers employ five steps when they create a macro. The five steps are planning, preparing, creating, fine-tuning, and beta-testing. Each of these steps is described below.
Step 1 – Planning: Know what you want
Before you can create a macro you have to define what you want the macro to do. That may seem obvious, but you should dig deeper and figure out in great detail exactly what the macro is supposed to do. For example, suppose your boss says, “Create a macro that prints out the current spreadsheet.” What does that really mean? Does the boss want the print out in landscape or portrait mode? Should the print out include page numbers? If so, do the page numbers go in the top left header, or the bottom right footer? Should the print out be shrunk to fit on one page? There are so many choices, so many options. There is simply no way you can create a macro that does exactly what you want if you don’t explicitly define exactly what is needed.
Here’s another example. Suppose you need to sort data in your spreadsheet. Before you create a macro to sort the data you need to define where the data begins and ends. You need to know if the number of rows is dynamic or static. Today its rows 2 through 30, but tomorrow it may be rows 2 through 50. If the number of rows does change then your macro will have to be written in such a way that it will sort all the rows that you currently have. You should also define which columns will be sorted, and in what order. Is it sorted by Last Name and then First Name or will the list be sorted by Country, State, City, and then Zip Code? You also need to define if the data will be sorted in Ascending, Descending, or some custom sort order. All these factors, and more, have to be defined if you want the macro to do exactly what you want.
Step 2 – Preparing: Know how to do it without a macro
If you plan to create the macro with the macro recorder, then you need to know how to manually do the task you want. For example, if you want a Microsoft Word macro to create a Table of Contents, then you need to know how to create a Table of Contents. If you want an Excel macro to create a PivotTable, then you will need to know how to create a PivotTable.
Step 3 – Creating: Record the macro
This is the step most books tell you about. In Word and Excel choose View (tab) > Macros (group) > Macros (drop down arrow) > Record Macro… (icon). You can also, if you are displaying the developer tab, can choose Developer (tab) > Code (group) > Record Macro… (icon).
The record macro dialog box will be displayed. There are quite a few details here which we will discuss in a future blog post. For now know that you need to enter in the name of the macro, without any spaces, decide where the macro should be stored, and optionally fill in a description.
Figure 1: Word 2010’s Record Macro dialog box
Figure 2: Excel 2010’s Record Macro dialog box
After you click the OK button you need to do the actions you want the macro to perform. Contrary to popular belief the macro record does NOT record everything you do. Instead it only records the results of what you do. Therefore it won’t matter if you perform the action with the mouse, the ribbon, or from a keyboard shortcut. What matters is that you perform the right steps in the right order, and that you not perform any additional steps.
Once you have completed those steps you need to turn off the recorder by choosing, from the ribbon, View (tab) > Macros (group) > Macros (drop down arrow) > Stop Recording (icon). If the developer tab is available choose Developer (tab) > Code (group) > Stop Recording (icon).
Step 4 – Fine-tuning: Test the macro and clean up the code as needed
After you record the macro you should test it to make sure it works. As shown in our previous blog post, How to run a macro, choose from the ribbon View (tab) > Macros (group) > Macros (icon), then double click the name of the macro you just created.
Often the macro recorder doesn’t quite do exactly what you wanted or expected. You will therefore need to edit the code. We will cover editing macros in a future blog post.
Step 5 – Beta-testing: Share it with someone else
After you have tested the macro and cleaned up the code you should share the macro with others. Why? Because often others will find bugs that you missed. Another reason is others often have suggestions for improving the macro that you can incorporate into version 2.
In this blog post we have presented an overview of the five steps used by professionals to develop macros in Microsoft Word and Excel. These five steps, planning, preparing, coding, fine-tuning and beta-testing apply to macros for other applications like PowerPoint and Access. We hope you can see that creating a macro can be a big deal. But we also assure you that the reward is an even bigger deal. Instead of taking hours or days to do tedious work you can just run a macro and have it all done in minutes if not seconds.
If you would like some help creating a macro for your business please contact us. As Snippet Mentors, we’d love to help you get more out of technology.