Microsoft Office Tutorials and References
In Depth Information
The Personal Macro Workbook
If you create some VBA macros that you find particularly useful, you may want to store
these routines in your Personal Macro Workbook. This workbook, named Personal.xls, is
stored in your Xlstart directory. Whenever you start Excel, this workbook is loaded. It’s
hidden, so it’s out of your way. When you record a macro, one of your options is to
record it to your Personal Macro Workbook. The Personal.xls file doesn’t exist until you
record a macro to it.
When you record your actions to create VBA code, you have several options. Recall
that the Tools
Record New Macro command displays the Record Macro
dialog box before recording begins. This dialog box gives you quite a bit of control
over your macro. The following paragraphs describe your options.
You can enter a name for the procedure that you are recording. By default, Excel
uses the names Macro1, Macro2, and so on for each macro you record. I usually
just accept the default name and change the name of the procedure later. You,
however, may prefer to name the macro before you record it. The choice is yours.
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. You can add or change a shortcut key at any time, so you don’t need
to set this option while recording a macro.
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.
By default, Excel inserts five lines of comments (three of them blank) that list the
macro name, the user’s name, and the date. You can put anything you like here, or
nothing at all. As far as I’m concerned, typing anything in is a waste of time,
because I always delete these comments and add my own.