Personal Macro Workbook
As you become more of a power user , you will probably want to use macros (written
either by yourself or by someone else) that automate certain routine tasks. The best way
to do this is to put such macros in your Personal macro workbook. This is a hidden file
that opens automatically when you open Excel. Therefore, any macros it contains are
always available, regardless of which files you have open. You may or may not have a
Personal macro workbook on your machine, but it is easy to create one. Follow these
steps. (These instructions were written for Excel 2010, but they should work with minor
changes in Excel 2007.)
1. You should have the Developer ribbon visible. If it isn't, click on the File tab, then
Options, and then Customize Ribbon. Choose Main Tabs from the top left dropdown list,
and move the Developer tab to the right pane. The Developer ribbon is for working with
2. Get into the VBA editor. (VBA stands for Visual Basic for Applications. It is Excel's
macro language.) You can get there by pressing Alt-F11 or clicking on Visual Basic from
the Developer ribbon.
3. In the VBA window, look at the Project Explorer pane on the left. (See screenshot to
the right.) Is there a Personal item in the list? If not, you need to create one. To create a
Personal macro workbook, which you have to do only once per machine, you can record
any macro and store it in the Personal file. To do so, click on Record Macro from the
Developer ribbon and fill out the dialog box as shown to the right, choosing Personal
Macro Workbook as the destination. Click on OK and then do anything, like boldfacing a
cell. Finally, click on Stop Recording on the Developer ribbon. Now you should have a
Personal item in the VBA Project Explorer list, and it should have one module. (A module
is where you store VBA code. As you can see, mine has a number of modules I have
created for automating various tasks. I like to prefix all of my module names with mdl.)
Double-click on the module to see the rather trivial code you just recorded. It starts with
the line Sub PracticeMacro() and ends with the line End Sub. Unless it does something
useful, you can delete this sub (short for subroutine).
4. OK, you now have a Personal macro workbook. From now on, if you record any useful
macros that you always want to have available, you can store them in a module (any
module) in your Personal macro workbook. Alternatively, if I give you any macro code,
you can copy it there as well. Just remember to save changes to your Personal macro
workbook. To do so, highlight the Personal item in the VBA Project Explorer list and click
on the Save button in the VBA toolbar.
5. You have simple two options on how to run this macro. You can create a button for it
in your QAT, or you can modify an existing ribbon by inserting a button for this macro.
E.g., I did the latter, creating a custom group in my Data ribbon and adding a new macro
Insert Pivot Table to Right to it. (See to the right. Note that you are allowed to add
buttons only to custom groups in ribbons, not original groups.) Now this pivot table
functionality is only a click away!