This article describes how to create a Personal Macro Workbook for writing and editing Microsoft Visual Basic for Applications (VBA) macros that you cannot record.
How to Create a Personal Macro Workbook
The easiest way to create a Personal Macro Workbook is to record a "dummy" macro. To do this, follow these steps:
- Start Excel so that only a new workbook is open. On the Tools menu, point to Macro, and then click Record New Macro.
- In the Record Macro dialog box, click Personal Macro Workbook in the Store macro in list.
- Click OK. Because you are creating a "dummy" macro, do not click or type anything.
- In the toolbar that appears, click the Stop Recording button.
- On the Excel menu, click Quit. Or, for versions of Excel earlier than Excel X, click Quit on the File menu.
- Click Save when you are prompted to save your changes.
When you start Excel, this Personal Macro Workbook opens as a hidden workbook. By default, it is stored in the following path, depending on your version of Excel. In Excel 98 and in Excel 2001
Microsoft Office folder:Office:Startup:Excel:Personal Macro Workbook
In Excel X or in later versions
Hard disk drive:Applications:Microsoft Office version:Office:Startup:Excel:Personal Macro Workbook
How to Use a Personal Macro Workbook
To write or edit VBA code in a Personal Macro Workbook, follow these steps:
- On the Tools menu, point to Macro, and then click Visual Basic Editor.
- In the "Project - VBA Project" window, double-click VBAProject (Personal Macro Workbook) to display the Modules folder within.
- Double-click the Modules folder to display any modules in this project, and double-click a module to display its code or blank code sheet.
You can use this code sheet to write and edit VBA code.