Microsoft Office Tutorials and References
In Depth Information
The Macro Recorder
In fact, this macro can be made even more efficient by using the With-End With construct:
.Offset(0, 0) = “Jan”
.Offset(0, 1) = “Feb”
.Offset(0, 2) = “Mar”
.Offset(0, 3) = “Apr”
.Offset(0, 4) = “May”
.Offset(0, 5) = “Jun”
Or, if you’re a VBA guru, you can impress your colleagues by using a single statement:
When you record your actions to create VBA code, you have several options in the Record Macro
dialog box. The following list describes your options.
h Macro name: You can enter a name for the procedure that you’re recording. By default,
Excel uses the names Macro1 , Macro2 , and so on for each macro that you record. I
usually just accept the default name and change the name of the procedure later. You,
however, might prefer to name the macro before you record it. The choice is yours.
h Shortcut key: The Shortcut key option lets you execute the macro by pressing a shortcut
key combination. For example, if you enter w (lowercase), you can execute the macro by
pressing Ctrl+W. If you enter W (uppercase), the macro comes alive when you press
Ctrl+Shift+W. Keep in mind that a shortcut key assigned to a macro overrides a built-in
shortcut key (if one exists). For example, if you assign Ctrl+B to a macro, you won’t be
able to use the key combination to toggle the bold attribute in cells.
You can always add or change a shortcut key at any time, so you don’t need to set this
option while recording a macro.
h Store Macro In: The Store Macro In option tells Excel where to store the macro that it
records. By default, Excel puts the recorded macro in a module in the active workbook. If
you prefer, you can record it in a new workbook (Excel opens a blank workbook) or in
your Personal Macro Workbook. (Read more about this in the sidebar, “The Personal