Workbook Personal Macro

The Record Macro dialog box allows you to choose where the recorded macro will be stored. There are three possibilities in the "Store Macro In" list box This Workbook, New Workbook and Personal Macro Workbook. The Personal Macro Workbook (PERSONAL.XLS in Excel for Windows, or Personal Macro Workbook in Excel for the Macintosh) is a workbook that is automatically opened when you start Excel. Since only macros in open workbooks are available for use, the Personal Macro Workbook is the ideal location for macros that you want to have available all the time. 

Normally the Personal Macro Workbook is hidden (choose Unhide... from the Window menu to view it). If you don t yet have a Personal Macro Workbook, you can create one by recording a macro as described earlier, choosing Personal Macro Workbook from the "Store Macro In" list box. 

Excel displays the Assign Macro dialog box. Choose the name of the macro to be assigned to the custom menu item (best located in the Personal Macro Workbook). Click the OK button. 

To install a new menu command whenever Excel is started, create an Auto Open procedure in the Personal Macro Workbook. To install a new menu command when a particular workbook is opened, create an Auto Open procedure in that workbook. Use the MenuBars and Menus VBA keywords to specify where the new Menultem is to be added, with the Add keyword. The syntax of the Add method is ... 

Some of your macros will be written for a very specific purpose, such as to prepare a specialized report. The workbook containing the macro will be opened only when you want to assemble the report. Other macros automate tasks that you perform often, and you ll want to have them available whenever you re using Excel. These macros should be saved in the Personal Macro Workbook. To make a command macro even easier to use, you can add a custom toolbutton to a toolbar and assign the macro to it. The three macros described in this section — the NumberFormatConvert macro, the FullPage macro and the ChemicalFormat macro — are particularly convenient to use when they are assigned to a button. 

Custom toolbuttons were created for the Full Page Portrait, Full Page Landscape Chemical Format H2O and Toggle Between Floating Point and Scientific macros. The Chemical Format macro is described in Chapter 16. Module sheets containing these macros should be saved in the Personal Macro Workbook. 

It is usually most convenient to store macros with the particular workbook where they will be used. If you later switch to another workbook, just copy those macros to the new workbook if you need them there as well. However, if you anticipate using a macro often, and you use your own personal computer, you may want to store it in the Personal Macro Workbook, which will keep it as a PersonaLxls file in the XLStart folder. All files stored in that folder are opened automatically when Excel is started, and will then be available to any spreadsheet. To keep them out of view, hide them with Windows => Hide. To modify these files, first use Windows O Unhide, which will show them in the Unhide dialog box. Select the particular hidden file you want to edit, in this case Personal, and click on the OKbutton. 

Unfortunately, if you re entering the custom function in a different workbook than the one that contains the custom function, the function name must be entered as an external reference, e.g. PERSONAL.XLS FtoC. This can make typing the function rather cumbersome, and means that you ll probably enter the function by using Excel s Paste Fimction. But, see "Creating Add-In Function Macros" in Chapter 17 for a solution to the problem. 

---