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 macros. 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!
Pages to are hidden for
"Adding Macro to Personal Macro Workbook.xlsx"Please download to view full document