Microsoft Office Tutorials and References
In Depth Information
Using the Macro Recorder
This is normally a six-step process, which is quite boring, but it’s part of your job responsibilities.
To complete the task you might:
Insert a new column at column A.
Select the Region column, cut it, and paste it to empty column A, to the left of the Item
Delete the now-empty column from where the Region column was cut.
Select range A1:C13 and sort in ascending order by Region, Item, and Count.
Select range C2:C13 and format the numbers with the thousands comma separator.
Select range A1:C1 and format those cells as Bold.
Not only are these steps monotonous, but also an invitation to honest mistakes due to
eventual human error. The good news is, if you perform the necessary steps perfectly for the Macro
Recorder, the task can be reduced to a simple mouse click or keyboard shortcut, with VBA doing
the grunt work for you.
Any time you create a macro, it’s wise to plan ahead about why you are
creating the macro, and what you want the macro to do. This is especially important
with complex macros, because you will want your macros to operate efficiently
and accurately, with just the code that’s necessary to get the job done properly.
By avoiding excessive code, your macros will run faster and be easier to edit
or troubleshoot. For example, get your workbook ready beforehand to avoid
unnecessary coded actions. Have the worksheet that you’ll be working on active,
with the range of interest already visible. Mistakes are recorded too! Practice the
steps first, so your macro is not longer than it needs to be.
Because you know what manual steps are required for this daily task, you are ready to create your
macro. The first thing to do is turn on the Macro Recorder. In Excel versions 2003 or before, click
the Record Macro button on
the Visual Basic toolbar as
shown in Figure 2-10. For
later Excel versions, click the
Record Macro button in the
Code section of the Developer
tab on the Ribbon, as shown
in Figure 2-11.