Microsoft Office Tutorials and References
In Depth Information
The Macro Recorder
Excel remembers your choice, so the next time you record a macro, it defaults to the
same location you used previously.
h Description: If you like, you can enter a description for you macro in the Description box.
Text you enter here appears at the beginning of your macro as a comment.
Cleaning up recorded macros
Earlier in this chapter, you see how recording your actions while you issue a single command (the
Page Layout ➜ Page Setup ➜ Orientation command) produces an enormous amount of VBA code.
This example shows how, in many cases, the recorded code includes extraneous commands that
you can delete.
The macro recorder doesn’t always generate the most efficient code. If you examine the
generated code, you see that Excel generally records what is selected (that is, an object) and then uses
the Selection object in subsequent statements. For example, here’s what is recorded if you
select a range of cells and then use some buttons on the Home tab to change the numeric
formatting and apply bold and italic:
Selection.Style = “Comma”
Selection.Font.Bold = True
Selection.Font.Italic = True
The recorded VBA code works, but it’s just one way to perform these actions. You can also use
the more efficient With-End With construct, as follows:
.Style = “Comma”
.Font.Bold = True
.Font.Italic = True
The Personal Macro Workbook
When you record a macro, one of your options is to record it to your Personal Macro Workbook.
If you create some VBA macros that you find particularly useful, you may want to store these
routines on your Personal Macro Workbook. This workbook is named Personal.xlsb and is
stored in your XLStart directory. Whenever you start Excel, this workbook is loaded, and you
have access to the macros stored in the workbook. Personal.xlsb is a hidden workbook, so
it’s out of your way when you’re working in Excel.
The Personal.xlsb file doesn’t exist until you record a macro to it.