The Excel Personal Macro Workbook is the workbook that contains all your created macros in one place.
This workbook usually opens whenever you start Excel, but since it is hidden, you cannot see it.
The exact location of the Personal Macro Workbook, though, can be a little hard to find, since you will not find it in your Documents folder, along with your other Excel documents.
In this tutorial, we will show you how to find the location of your Excel Personal Macro Workbook.
We will also show you how you can view the personal workbook, in case you want to make edits to any of your macros.
Why is there a Personal Macro Workbook in Excel?
When you write or record a macro in Excel, you can only access it from the particular workbook where it was created.
But recreating macros every time you open a new workbook can just be futile.
So Excel lets you store your frequently required macros in one place (the Personal Macro Workbook), which is always open in the background whenever you start the Excel application.
In this way, your macros are accessible all the time and in any workbook!
How to Create an Excel Personal Macro Workbook?
Every time you write or record a macro, Excel gives you the option to save it in the Excel personal macro workbook.
If you don’t already have an Excel Personal Macro Workbook, you can create one very easily as follows:
- From the Developer menu, click on ‘Record Macro’ under the ‘Code’ group.
- This will open the Record Macro window.
- Type in a name for the Macro (if you want) and from the dropdown menu under ‘Store macro in:” select ‘Personal Macro Workbook’.
- Click OK.
- This will now start recording your macro. If you have a macro that you want to record then go ahead and record it, otherwise, simply move to the next step.
- Click ‘Stop recording’ from the Developer tab.
- You are now done recording your macro.
- Close the Excel window, and you will be asked if you want to save the macro you just recorded to the Personal Macro Workbook.
- Click Save.
Your Personal Macro Workbook should now be created.
Location of the Excel Personal Workbook
The Personal Macro Workbook is always stored in a special file named ‘personal.xlsb’ in newer Excel versions (2007 onwards). In earlier versions, it is saved as ‘personal.xls’.
Personal Macro workbook is always stored in the XLSTART folder.
Excel always looks in this folder whenever it launches. So any file stored here gets loaded as soon as Excel starts.
The problem is, the location of this folder may be a little tricky for us to find.
So here’s a simple hack that you can use to quickly get the location of the XLStart folder.
- From the Developer tab, select Visual Basic.
- This will open the Visual basic Developer window.
- You will see the Project window on the left, containing a list of all your VBA projects and modules under them.
- Select ‘VBAProject (PERSONAL.XLSB)’.
- Under the Immediate window (at the bottom of your Developer window), type “?thisworkbook.Path”.
- Press the Return key
- You should now see the path of the folder that contains the ‘Personal.xlsb’ file in your computer.
- Copy this path and paste it into the File location bar of your File Explorer and press the return key.
You should now be able to see your personal macro workbook as ‘personal.xlsb’ in the XLSTART folder.
How to Unhide a Personal Macro Workbook
The Personal macro workbook is always available in any open Excel workbook. The reason we don’t see it though is that it remains hidden in the background.
You might want to see this workbook, maybe to make edits to macros stored in it, or for any other purpose.
To view the personal macro workbook, you need to simply unhide it.
Here’s how you can do this:
- From the View menu, select the ‘Unhide’ button (under the ‘Window’ group).
- This will open the Unhide dialog box, from where you can select the hidden workbook that you want to unhide.
- Select ‘Personal’ from the list of workbooks displayed in the box and click OK.
- This will unhide your personal macro workbook and bring it to the foreground.
This workbook is basically blank, consisting only of macros that you had saved. However, if there’s certain information or data that you want to make available in all workbooks then you can go ahead and add it to the personal macro workbook.
To hide the workbook once again, simply click on View->Hide.
In this tutorial, we explained what the Excel Personal Macro workbook is, how it works as well as how to locate it in your computer’s files and folders.
We also showed you how to view the workbook, make changes to it, and then how to hide it again, so that it keeps running in the background without interfering.
We hope this was helpful.
Other Excel tutorials you may also like: