Personal.xlsb

This workbook will not exist until you have recorded your first macro AND closed and reopened Excel.
Any macros that are stored here will be available to run from any workbook.

Where is the File Saved ?

This file is saved in your Excel startup folder when you close Excel.
If you have a Personal.xlsb file this will be stored in your Personal xlStart folder:
For more details about the exact location of this folder, please refer to the Workbooks > Startup Folders page.

Excel 365 - C:\Users\"user name"\AppData\Roaming\Microsoft\Excel\XLStart\ 
Excel 2021 - C:\Users\"user name"\AppData\Roaming\Microsoft\Excel\XLStart\ 
Excel 2019 - C:\Users\"user name"\AppData\Roaming\Microsoft\Excel\XLStart\ 
Excel 2016 - C:\Users\"user name"\AppData\Roaming\Microsoft\Excel\XLStart\ 

Your Personal Macro Workbook

This file will be created for you automatically when you choose "Personal Macro Workbook" from the Record Macro dialog box.
This is a hidden workbook that you can use to store any general, utility macros, that you want to use (and apply) to any open workbook.
This can be thought of as the place to store your so called "global" macros, i.e. macros that you want available all the time.
Once created, this hidden file will always be accessible when Excel is open.

Does the File Exist Already ?

To check if the file has been created, open a blank workbook and select the View tab.
If the Unhide command is disabled, then your Personal.xlsb file has not been created.

If the Unhide command is enabled, press it and see if the Personal.xlsb file appears in the list.
If this filename appears in the list then the file exists.

Storing your Macros

When you record your first macro it will default to being saved in "This Workbook".

Storing your macros in "This Workbook" makes sense when you are manipulating data for a specific workbook.
Choosing "New Workbook" makes sense when you are experimenting with the macro recorder and do not want the recorded macros to be added to the active workbook.
Using the "Personal Macro Workbook" makes sense when you are recording a utility macro that could be used on a number of different workbooks.
The Personal Macro Workbook provides you with a way of saving general utility macros that only you can use.
If you want to distribute your macros to other people then you need to Create an Excel Add-in.

Remember

Another way to check if the Personal.xlsb file exists is to select the Developer tab, press Macros and expand the "Macros in" drop-down list. Does Personal.xlsb exist in this list.
Your Personal.xlsb workbook is a common and easy target for macro viruses so if you have made a lot of customisations to the Excel environment it is worth saving a copy of this file.

Example

In this example we are going to create a simple macro that we can run from any workbook.
This macro will select cells "A1:B10" on the active worksheet and shade the cells Blue.
There is a button on the status bar that you can use to quickly record a macro.

Before we actually start recording the macro it is definitely worth displaying the Developer Tab.
The button on the status bar is just a shortcut to the "Record Macro" button on the Developer tab.

Record the Macro

Close and ReOpen Excel and create a new blank workbook.
Display the "Record Macro" dialog box.
Select the "Personal Macro Workbook" from the store macro in drop-down box.

Press OK to start recording.
Select the cell range "A1:B10".
Right mouse click and choose Format Cells from the shortcut menu.
Select the Fill tab. Choose a Blue colour and press OK.

Stop Recording

When you have finished recording your actions press the "Stop Recording" button the Developer tab.
Alternatively you can press the button on the status bar.

Close Excel and Save The Changes

Once you have finished recording your macro, close Excel.
Before Excel closes you will see the following popup message.
Every time changes are made to your Personal Macro Workbook you will prompted to save the changes before Excel closes.

Press Save. Once Excel has closed the file will appear in your XLStart folder.

Using the Macro

ReOpen Excel and create a new blank workbook.
Display the Developer Tab and press the Macros button.

This displays the "Macro" dialog box that displays all the macros that are currently available.
The macros in drop-down box at the bottom will default to "All Open Workbooks".
All open workbooks will include your Personal Macro Workbook.

This new macro can be used on any active workbook.
To run the macro press the "Run" button at the top.

Inserting Macros

Open the Visual Basic Editor
Find the Personal.xlsb project
Expand the Modules folder

The Visual Basic for Applications window will be opened automatically.
You will see that a code module has been added to the active workbook and that the following code has been added:
You are now ready to insert or paste in you own VBA code.
Once you have entered your VBA code select (Debug > Compile) to make sure your code does not contain any syntax errors.
After you have finished you can return to Excel by selecting (File > Close and Return to Microsoft Excel).

You can quickly display the Visual Basic Editor by clicking Edit in the Macros dialog box

The project explorer lists any workbooks you have currently got open and also includes any workbooks in the xlStart folder or alternate startup folder. Even workbooks that are hidden (ie the Personal.xls).

Saving Data

It is possible to store data in the Personal Macro Workbook.
You might want to make the Personal.xlsb file visible if you need to store data on its worksheets.
You can hide the workbook easily afterwards.

Adding worksheets to your Personal.xlsb

This workbook is normally hidden however you can unhide it by pressing (Window> UnHide) and selecting "Personal.xls" from the dialog box ??
If you have a Personal.xls created it is open every time Excel is open meaning your macros are always available. You can only see the workbook from the VB Editor as it is always hidden.
If you want to add a custom button to you standard toolbar then you should put the code to be executed when the button is pressed in a module in your Personal.xls.