How to Create a Personal Macro Workbook
by Francis Hayes (The Excel Addict)
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
The 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;
' Macro1 Macro
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 that you got online or elsewhere.
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 window. However, when you attempt to close Excel, you will be prompted to save changes. Personally, I prefer to save changes to my Personal Macro Workbook before closing the VBE window (by clicking the Save button on the VBE toolbar) because, on too many occasions, I have accidentally clicked No to save changes when shutting down Excel at the end of the day.
|Copyright © 2016
Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.