VBA – Personal Macro Workbook

This article will demonstrate how to create a Personal Macro Workbook in VBA.

Normally, when we write a macro in the VBE Editor, it is stored within a module that is part of the workbook file and can only be used by that specific workbook. The Personal Macro workbook enables us to store a GLOBAL macro, a macro that is available for us to use across any open workbook in Excel.  This workbook is opened and automatically hidden each time you open Excel.

Creating a Personal Macro Workbook

We can create a personal macro workbook in Excel by recording a macro, and storing it within the Personal Macro workbook – Excel will then create the Personal Macro workbook automatically.

To record a macro, in the Ribbon, select View > Macros > Record Macro.

OR

In the Ribbon, select Developer > Code > Record Macro.

NOTE: to use the Developer tab in the Ribbon, it has to be made visible as it is by default hidden. To learn how to enable the Developer tab in the ribbon, click here.

You can change the name of the macro if you wish – remember that space are not allowed, you need to start with an alphabetical character and only alphanumerical characters are allowed (eg A-z, 0-1).  By default the Macro name will be Macro1.

In the Store macro in: drop down box, select Personal Macro workbook.

 

You can record anything you like – for the purposes of this example we are going to click on Bold and Italic in the Home tab in the Ribbon to make the active cell in Excel Bold and Italic.

To stop recording, click on the stop recording button in the bottom right hand corner of the screen.

Saving the Personal Macro Workbook

When you have recorded or written macros in the Personal Macro workbook, and you exit Excel, Excel will ask you if you wish to save the Personal Macro workbook.

If you do not save, any macros that you have written or recorded will be lost.  Click Save to save all the macros stored in the Personal Macro workbook.

Running a macro from the Personal Macro Workbook

Once you have stored a macro in the Personal Macro workbook, you can run the macro from within any Excel file.

In the Ribbon, select View > Macros > View Macros.

OR

In the Ribbon, select Developer > Code > Macros.

Select the macro you wish to run, and then click Run.

NOTE: There is also an Edit option available in the Macro dialog box above.  However, you cannot Edit a macro in the Personal Macro workbook from this screen unless you unhide the Personal Macro Workbook first.

Viewing the Personal Macro Workbook in the VBE Editor

Now that you have recorded a macro that is stored within the Personal Macro workbook, this workbook will have been created for you and is hidden.  To view the macro and the Personal Macro workbook, we need to switch to the Visual Basic Editor (VBE).

In the Ribbon, select Developer > Code > Visual Basic.

OR

Press Alt+F11 on the keyboard

This will switch you into the Visual Basic Editor.

In the Project Explorer on the left hand side, you will see that a Project has been created called PERSONAL.XLSB, and within that project, a module (Module 1) has automatically been created for you. Within that module, a sub-procedure called Macro1 has been created by the macro recorder.

Unhiding the Personal Macro Workbook

If you wanted to edit the macro directly from the View or Developer tabs in Excel, you would first need to unhide the Personal Macro workbook.

In the Ribbon, select View > Windows > Unhide.

Select the Personal Macro workbook and then select OK.

You will notice in the Excel menu bar, that you are now in the file – PERSONAL.XLSB.   This is a single blank worksheet that contains any macros that you have stored in it.

In the Ribbon, select View > Macros > View Macros.

OR

In the Ribbon, select Developer > Code > Macros.

Select the macro you wish to run, and then click Edit.

This will switch you to the VBE Editor.

NOTE: it is not recommended to unhide the Personal Macro workbook – it is rather recommended to use the VBE Editor directly from the Developer tab, or by pressing Alt+F11 to Edit your macro.

If you unhide your Personal Macro workbook, remember to hide it again before you exit Excel otherwise it will be visible next time you open Excel.

Advantages of the Personal Macro Workbook.

• Any recorded macros are available to be used in all Excel files.
• Custom VBA code and functions can be written and shared between Excel files.

Deleting the Personal Macro workbook

If for some reason you wish to remove the Personal Macro workbook from Excel, you would need to close Excel and then physically delete the PERSONAL.XLSB file from the XLSTART folder.

1. Close Excel.
2. Open the Windows Explorer and browse to C:\Users\User Name\AppData\Roaming\Microsoft\Excel\XLSTART\
3. Click on the view tab, and make sure that the hidden items check box is selected.
4. Right-click on PERSONAL.XLSB and then click Delete.