The Personal Macro Workbook is a good place to store your common macros.
This will always be accessible whenever Excel is open.
This workbook will not exist until you have recorded your first macro.
You can easily check if this file is available by selecting (View > Unhide).
If the Unhide command is unavailable or if it does not display a Personal.xls file then it means that this file has not yet been created.
After this workbook has been created it will load every time as a hidden workbook whenever Excel is open.
This workbook can be made visible using (Window > UnHide).
This file is hidden and is saved in your Excel startup folder when you close Excel.
If Personal.xls does not exist, the macro recorder will create one for you.
An excpetion where you might want to make the Personal.xls file visible is if you need to store data on its worksheets.
You can hide the workbook easily afterwards.
If you are creating a general purpose utility macro which you want to be able to use with any workbook you can store this in your Personal.xls workbook.
If the macro relates to just the application in the current workbook you can store the macro in that specific workbook.
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.
Office 365 - 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\
Excel 2013 - C:\Users\"user name"\AppData\Roaming\Microsoft\Excel\XLStart\
Does One Exist ?
The Personal.xlsb file will not exist until you record your first macro.
Record a macro into the Personal.xls. Goto (Tools > Macros > Record) - and select "Personal Macro Workbook" before recording a very basic macro.
Advantages of the Personal.xls
Excel opens the Personal Macro Workbook as a hidden file every time that you open Excel.
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.
Any macros that are stored there are available to run with any workbook.
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.
Storing your Macros
You can store your macros in a couple of places. In individual workbooks, in your Personal.xls or in an add-in.
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).
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.
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.
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext