Finding the Personal Macro Workbook in ExcelIf Your Personal Macro Workbook Disappeared, this Tutorial will Help you Get it Back Easily!
The Personal Macro Workbook in Excel is a special workbook that stores all of the macros that you have created.
This workbook automatically opens when you start Excel, but it is hidden so it may not be easily visible.
The location of this workbook may not be straightforward, as it is not typically found in the same folder as your other Excel documents.
This tutorial will guide you through the process of locating your Excel Personal Macro Workbook and also provide instructions on how to access and edit the macros within the workbook.
What is the purpose of the Personal Macro Workbook in Excel?
When you create a macro in Excel, it is only available for use within the workbook where it was made.
Recreating macros every time you open a new workbook can be a tedious task.
To alleviate this, Excel allows you to store frequently used macros in a single, central location, called the Personal Macro Workbook.
This workbook is always open in the background when Excel is running, making your macros always accessible in any workbook, at any time.
Instructions for Creating a Personal Macro Workbook in Excel
When you create or record a macro in Excel, you have the option to save it in the Personal Macro Workbook.
If you do not yet have a Personal Macro Workbook, it can be created using the following process:
- Go to the Developer menu and select “Record Macro” from the “Code” group.
- This will open the Record Macro window.
- Name the Macro and choose “Personal Macro Workbook” from the “Store macro in” dropdown menu.
- Click OK to begin recording your macro.
- Record your desired macro and click “Stop recording” when finished.
- Close the Excel window. You will be prompted to save the recorded macro to the Personal Macro Workbook.
- Select “Save” to confirm.
Now your Personal Macro Workbook is now created.
Where to Find the Personal Macro Workbook in Excel
The Personal Macro Workbook is typically stored in a file named “personal.xlsb” in newer versions of Excel (2007 and later), and as “personal.xls” in older versions.
It is always located in the XLSTART folder, which Excel automatically searches upon launch. This means that any file stored in this folder will be loaded when Excel starts.
The location of the XLSTART folder can be tricky to find, but there is an easy method for quickly identifying its location.
- Select Visual Basic (from the Developer tab).
- The Visual Basic Developer window will now be opened.
- You will find the Project window on the left of the screen, which lists all the VBA projects and the modules within them.
- Click ‘VBAProject (PERSONAL.XLSB)’.
- Type “?thisworkbook.Path” (under the Immediate window).
- Press the Enter key.
- You will now see the location of the folder that holds the ‘Personal.xlsb’ file on your computer.
- Copy the path, open File Explorer, paste the path into the File location bar, and press enter.
You should now be able to locate your Personal Macro Workbook, named “personal.xlsb”, within the XLSTART folder.
Instructions to Unhide a Personal Macro Workbook
The Personal Macro Workbook is always present when you are working with Excel, however, it may not be visible as it is hidden in the background.
You may wish to access the workbook to make changes to stored macros or for other reasons.
To reveal the Personal Macro Workbook, you can unhide it.
Here’s the step-by-step process:
- Go to the “View” menu, select “Unhide” from the “Window” group.
- This will open the Unhide dialog box, allowing you to choose the hidden workbook you want to reveal.
- Select “Personal” from the list of options, then click OK.
- This will make the Personal Macro Workbook visible, and it will appear in the foreground.
The Personal Macro Workbook only contains the macros that you have saved. Otherwise, it is generally empty.
However, if you have information or data that you wish to make accessible in all workbooks, you can include it in the Personal Macro Workbook.
Simply click on View>Hide to hide the workbook again.
In this tutorial, we explained the concept of the Excel Personal Macro Workbook, how it functions, and how to find its location in your computer’s files and folders.
We also demonstrated the process of revealing the workbook, making changes to it, and then hiding it again to keep it running in the background without disruption.
We hope you found this information useful.