Chapter 42: Introducing Visual Basic for Applications
42
More about recording VBA macros
If you followed along with the preceding examples, you should have a better feel for how
to record macros—and a good feel for problems that might occur with even simple macros.
If you find the VBA code confusing, don’t worry. You don’t really have to be concerned with
it as long as the macro that you record works correctly. If the macro doesn’t work properly,
re-recording the macro rather than editing the code is often easier.A good way to learn about what is recorded is to set up your screen so that you can see the
code that is being generated in the VBE window. To do so, make sure that the Excel win-
dow isn’t maximized; then arrange the Excel window and the VBE window so that both are
visible. While you’re recording your actions, make sure that the VBE window is displaying
the module in which the code is being recorded. (You may have to double-click the module
name in the Project Explorer window.)If you do a lot of work with VBA, consider adding a second monitor to your system. Then you can display Excel on one
monitor and the VBE on the other.
Storing macros in your Personal Macro Workbook
Most user-created macros are designed for use in a specific workbook, but you may want
to use some macros in all of your work. You can store these general-purpose macros in
the Personal Macro Workbook so that they’re always available to you. The Personal Macro
Workbook is loaded whenever you start Excel. This file, named personal.xlsb, doesn’t
exist until you record a macro, using Personal Macro Workbook as the destination.The Personal Macro Workbook is normally located in a hidden window to keep it out of the way.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook
option in the Record Macro dialog box before you start recording. This option is in the Store
Macro In drop-down box.If you store macros in the Personal Macro Workbook, you don’t have to remember to open
the Personal Macro Workbook when you load a workbook that uses macros. When you want
to exit, Excel asks whether you want to save changes to the Personal Macro Workbook.Assigning a macro to a shortcut key
When you begin recording a macro, the Record Macro dialog box gives you an opportunity
to provide a shortcut key for the macro. Here’s what to do if you’d like to change the short-
cut key or provide a shortcut key for a macro that doesn’t have one: