Click here to get more time-saving Excel tips.

The Excel Addict - Help with Microsoft Excel

How to Create a Personal Macro Workbook

A Personal Macro Workbook is used to store macros that can be run in any open workbook.

Do you have a Personal Macro Workbook?

To check if you already have a Personal Macro Workbook, from the View menu select Unhide. If PERSONAL.XLSB is listed, you have one. If not, you can easily create one. Click Cancel on the Unhide dialog.

Create a Personal Macro Workbook

Click the Record Macro button to begin recordingThe simplest way to create a Personal Macro Workbook is by recording a 'dummy' macro.

1) Click the Record Macro button on the bottom left side of the Status Bar (next to the Ready message);

2) In the Record Macro dialog select Personal Macro Workbook from the 'Store macro in' dropdown and click OK;

3) The record macro button on the StatusBar has changed to a Stop button. Click it to stop recording the macro;

4) Next hold down ALT key and press F8 key to open the Macro dialog;

5) Select the macro
(probably Macro1) from the list and click the Step Into button. The Visual Basic Editor (VBE) window opens;

6) In the VBE window, from the Run menu, select Reset;

7) You will see the Macro code that was recoded in the Code window;

Sub Macro1()
' Macro1 Macro

End Sub

8) Select this code and press the Delete key;

9) Finally, save your Personal Macro Workbook by clicking the Save button on the VBE toolbar.

You can now record new macros in your Personal Macro Workbook or paste in existing VBA code.

Note that the Personal Macro Workbook is a hidden workbook, so when you close the VBE window, the workbook remains open but is hidden. Since the workbook is still open, you are not prompted to save changes to your Personal Macro Workbook when you close the VBE. However, when you attempt to close Excel, you will be prompted to save any changes. I personally prefer to save changes to my Personal Macro Workbook before closing the VBE window because, on many occasions, I have accidentally clicked No to save changes when shutting down Excel long afterwards.

Francis Hayes, The Excel Addict, Microsoft Excel time-saving tips, tutorials and training resources

Click here to get more time-saving Excel tips.

Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630