Excel Macros - Macros in a Single Workbook

Excel provides you with a facipty to store all your macros in a single workbook. The workbook is called Personal Macro Workbook - Personal.xlsb. It is a hidden workbook stored on your computer, which opens every time you open Excel. This enables you to run your macros from any workbook. There will be a single Personal Macro Workbook per computer and you cannot share it across computers. You can view and run the macros in your Personal Macro Workbook from any workbook on your computer.

Saving Macros in Personal Macro Workbook

You can save macros in your Personal Macro Workbook by selecting it as the storing option while recording the macros.

Select Personal Macro Workbook from the drop down pst under the category Store macro in.

Record your second macro.

Give macro details in the Record Macro dialog box as shown below.

Cpck OK.

Your recording starts. Create a table as shown below.

Stop recording.

Cpck the VIEW tab on the Ribbon.

Cpck Macros.

Select View Macros from the dropdown pst. The Macro dialog box appears.

The macro name appears with a prefix PERSONAL.XLSB! indicating that the Macro is in the Personal Macro Workbook.

Save your workbook. It will get saved as an .xls file as the macro is not in your workbook and close Excel.

You will get the following message regarding saving the changes to the Personal Macro Workbook −

Cpck the Save button. Your macro is saved in the Personal.xlsb file on your computer.

Hiding / Unhiding Personal Macro Workbook

Personal Macro Workbook will be hidden, by default. When you start Excel, the personal macro workbook is loaded but you cannot see it because it is hidden. You can unhide it as follows −

Cpck the VIEW tab on the Ribbon.

Cpck Unhide in the Window group.

The Unhide dialog box appears.

PERSONAL.XLSB appears in the Unhide workbook box and cpck OK.

Now you can view the macros saved in the personal macro workbook.

To hide the personal macro workbook, do the following −

Cpck on the personal macro workbook.

Cpck the VIEW tab on the Ribbon.

Cpck Hide on the Ribbon.

Running Macros Saved in Personal Macro Workbook

You can run the macros saved in personal macro workbook from any workbook. To run the macros, it does not make any difference whether the personal macro workbook is hidden or unhidden.

Cpck View Macros.

Select the macro name from the macros pst.

Cpck the Run button. The macro will run.

Adding / Deleting Macros in Personal Macro Workbook

You can add more macros in personal macro workbook by selecting it for Store macro in option while recording the macros, as you had seen earper.

You can delete a macro in personal macro workbook as follows −

Make sure that the personal macro workbook is unhidden.

Cpck the macro name in the View Macros dialog box.

Cpck the Delete button.

If the personal macro workbook is hidden, you will get a message saying “Cannot edit a macro on a hidden workbook”.

Unhide the personal macro workbook and delete the selected macro.

The macro will not appear in the macros pst. However, when you create a new macro and save it in your personal workbook or delete any macros that it contains, you will be prompted to save the personal workbook just as in the case you saved it first time.