When we create the macros for automation, they can be used for that particular workbook only. We cannot access them outside that workbook in others.
Excel Personal Macro Workbook is a hidden workbook in your local system that pops up whenever you open the Excel application. It is a place where all the macros from any workbook can be stored and one can use the macros from here which are often required.
How to create a personal macro workbook?
Creating a personal macro workbook is similar to creating a macro in Excel.
Let’s discuss it in some simple steps :
- Click on the developer tab on the ribbon and then click on the Record Macro tool in the Code group.
- A new dialog box would open.
- In the option, “Store Macro In”, choose Personal Macro Workbook. In this way, our macro would be stored in the personal macro workbook.
- After filling the dialog box, click OK.
- In the last, click on stop recording in the developer tab.
This is how a personal macro workbook can be created.
How to view, create, copy and edit macros in Personal Macro Workbook?
All the options like view, create, copy and edit macros can be done from VBA(Visual Basic for Applications) Editor.
Let’s discuss it in some simple steps :
- Go to the Developer tab on the ribbon, click on the Visual Basic tool in Code Group.
- As the Visual Basic tool is clicked, a new window would open.
- On the left-hand side, some headings can be found. Out of which, double-click on “VBAProject ( PERSONAL.XLSB)”.
- Then among the options, double-click on “Modules”.
- On the right-hand side, the code of each macro can be found, and we can view and edit it from that window.
How to use macros stored in Personal Workbook?
We can view all the macros stored in the personal workbook in one place and run them accordingly.
Let’s discuss it in some simple steps :
- Go to the Developer tab on the ribbon, click on the Macros tool in Code Group.
- A dialog box would open.
- In the option “Macro in”, choose “PERSONAL.XLSB”. and all the macros stored in Personal Workbook will show up in the dialog box.
- Select the macro that is required and click on Run.
How to export Personal Macro Workbook?
Personal Macro Workbook has many features to use, and one of them is, It can be exported to the local system. And then can be shared.
Let’s discuss it in some simple steps :
- Go to the Developer tab on the ribbon, click on the Visual Basic tool in Code Group.
- As the Visual Basic tool is clicked, a new window would open.
- On the left-hand side, some headings can be found. Out of which, double click on “VBAProject ( PERSONAL.XLSB)”.
- Then among the options, double click on “Modules”.
- Right-click on the module, you want to export. and then click on Export File.
- A dialog box would open, fill the options and click Save.
How to share Personal Macro Workbook in Excel?
Personal Macro Workbook can be shared with other people for the efficient use of the macros and to avoid recording the macros again.
Some ways in the workbook can be shared:
- Using the Personal.xlsb file: It is one of the easiest ways to share a personal macro workbook. Just export the Personal.xlsb file to your local system and share it.
- Copying macros to another workbook: If only some macros from the Personal Workbook need to be shared, copy those macros to another workbook in Excel, and email that workbook to other users.
These two methods are mostly used in sharing the Personal Macro Workbook.
How to enable Personal Macro Workbook in Excel?
Sometimes, the personal workbooks get disabled by excel after the crash, update, etc. But we can enable them.
Let’s discuss it in some simple steps :
- From the top right corner, click the File tab and then Options.
- A dialog box would open. From the left-hand side of the box, select Add-ins.
- From the Manage drop-down list, select Disabled Items and click Go.
- In the Disabled Items dialog box, select Personal Macro Workbook and then click Enable.
As of now, my personal workbook is not disabled, hence it is not showing here. However, all the disabled items including the personal workbook show up here.
- At last, Restart your Excel.
This will enable the Personal Workbook.