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.

microsoft excel docs

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.

microsoft excel docs

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.

microsoft excel docs

Storing your Macros

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

microsoft excel docs

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.


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.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext