If you use a lot of VBA codes like me then you need to have a place where you can store all of them and use them whenever you need.
But the question is, whatās the best place for this? PERSONAL MACRO WORKBOOK. Yes, that PERSONAL.XLBS workbook. Hereās the crazy thing:
Creating a personal macro workbook and storing all the important macros in it can save you a ton of time and help you use them in all Excel files.
Isnāt that AWESOME?? And today in this post, Iāll be sharing with you everything you need to know about this file. So stay with me for the next 3 to 4 minutes.
What is PERSONAL MACRO WORKBOOK
Personal Macro Workbook is a file that can be the common storage place to store your all macros and use them. Although itās a hidden file that opens when you start Excel in your system. And you donāt need to open it separately when you want to access any of the codes from it. All the codes from it can be seen in your macro list.
Whatās the LOCATION of PERSONAL.XLSB Workbook
Hereās the bad news: Even though itās such a useful thing to have, itās not there in Excel by default, so you need to create it (Iāve shared the steps ahead in this post). But, you can check if it is already created or not.
The first method is to open your visual basic editor and if your project window you have listed a āPERSONAL.XLBSā named file.
That means itās already there and there is no need to create it. The second method is to have a look at your XLSTART folder.
Note: If you put anything thing in the XLSTART folder that will open automatically when you launch Excel. If you are using Windows 10:
C:Users\Puneet Gogia\AppData\Roaming\MicrosoftExcel\XLSTART
And if you are using MAC: Libraryā© āø āØGroup Containersā© āø āØUBF8T346G9.Officeā© āø āØUser Contentā© āø āØStartupā© āø āØExcel
If you arenāt able to find your Personal Macro Workbook using these methods then you need to follow the steps Iāve mentioned ahead to create a new one.
How to Create a Personal Macro Workbook in Excel
Creating a new personal macro workbook is simple. All you need to do is to record a macro, thatās it yes, thatās it.
Windows Version
Here are the simple steps you need to follow:
- First of all, go to the developer tab and click on the āRecord Macrosā button.
- After that, in the āRecord Macroā dialog box:
- Enter a name for the Macro
- In the āStore macro inā, drop down select āPersonal Dialog Boxā.
- In the end, click OK.
Now you can record something or you can simply click on āStop Recordingā.
Your new āPersonal Macro Workbookā is created now. You can find the new PERSONAL.XLSB file in the XLSTART folder.
(MAC Version)
Below are the steps to create your Personal Macro Workbook in the MAC version of Excel.
- First of all, go to the developer tab and click on the āRecord Macro Buttonā.
- After that, enter the name of the macro and select the āPersonal Macro Workbookā from the drop-down.
- In the end, click OK.
Now you can simply click on āStop Recordingā as your new personal macro workbook is created.
Why Should I have a Personal Macro Workbook?
This is the real questionā¦
Well, if you love to use macros and have some useful codes which help you do your stuff with a click you must have a Personal Macro Workbook. You can store all those codes in your personal workbook and can use them from any workbook on your system.
As I said when you open your Excel the āPERSONAL.XLSBā open with it so your codes will be accessible all the time when you are using Excel. So having it is a big WIN-WIN.
It happens sometimes when you need to share your macros from Personal Macro Workbook with others or you just need to transfer it to a different system. Now in this situation, you can use do the following things:
(A) Export and Import the Module
The best way to share or transfer your Personal Macro Workbook is to export the VB module from one system and import it into the other system. Below are the steps you can use to do this:
- First, open the VB editor and expand the tree of āPersonal.xlsbā and right-click on the module which you want to export.
- Now, select the āExport Fileā option and save it on the desktop, and after that transfer it to the new system.
- Next, you need to import this module not the VB editor at the new system.
- For this, open VB editor and right click on the āPersonal.xlsbā and select the āImport Fileā.
- After that, browse the module file and click OK to import.
Now, you have the new module in your Personal Macro Workbook where you have all the VBA codes and you can use them from the macros list.
(B) Copy-Paste Personal.xlsb File to the Startup Folder
If the new system where you want to transfer all the codes doesnāt have a Personal Macro Workbook then you can simply copy-paste personal.xlsb from the old system to the new systemās Excel startup folder.
Yes, thatās it.
Delete Personal Macro Workbook
If you donāt want to have a Personal Macro Workbook you can simply delete it from your system. All you need to do is, open the Excel STARTUP folder and delete the file from there.
And if the PERSONAL.XLSB file hidden then you simply unhide it and then delete it. The recommendation is to take a back of the Personal Macro Workbook so that you can restore it in the future.
What if Iām Unable to Record a Macro in Personal Macro Workbook
There can be a situation where you arenāt able to record a macro in Personal Macro Workbook. Or, it doesnāt open when you launch Excel.
In this situation, you need to check if your Personal Macro Workbook isnāt under āDisabled Itemsā. If it is, then you need to enable it. To open āDisabled Itemsā, go to File Tab ā Options ā Add-Ins ā Manage ā Disabled Items.
From this dialog box, you can simply enable it.
Hide it if Excel Keep Opening it Every time
As you know, every time you open Excel, the Personal.xlsb file opens with it automatically but sometimes instead of opening as a hidden file it will open in the front.
Well, you can make it hide back. All you need to do is when you open your Excel app go to the View Tab and click on āHideā and select āPersonalā.
In the end, click OK. Now, close the Excel and restart it. Thatās it.
Transfer Other VBA Codes in Personal Macro Workbook
Itās always better to record and write your macro code in Personal.xlsb, but for all the code which you have before creating the PERSONAL MACRO WORKBOOK.
This thing is simple. All you need to do is create a new module and then paste all the code there.
Lock Personal.xlsb for Editing
If you want to make your Personal Macro Workbook locked so that it canāt be edited then you can simply follow below steps:
- First of all, go to the place where you have Personal.xlsb and right-click and open its properties.
- After that, from the āSecurityā tab tick mark āRead Onlyā.
- In the end, click OK.
Once you do this, you can be able to read the macro from it but canāt able to save a new macro in it.
In the end,
A Personal Macro Workbook can be helpful for you to manage all the important macros in one place. The best way to create a personal.xlsb is to simply record a macro in it. I hope you found this VBA tip will help you to
But now, tell me one thing.
How do you Manage all the VBA codes you use?
Make sure to share your views with me in the comment section, Iād love to hear from you and please, donāt forget to share this post with your friends, I am sure they will appreciate it.
More on VBA
- User Defined Function | Activate a Sheet using VBA| VBA Code to Search on Google| VBA IF | Pivot Table using VBA | VBA MsgBox Function | VBA New Sheet | Record a Macro | VBA Option Explicit
VBA is one of the Advanced Excel Skills.
I have not learned how to manage my codes as yet as I am fairly new at VBA. Can record and ssave simple macros using the record function, but still learning.
Thank you very much, really helpful
you’re welcome, Sakil.
Thank you very much
Thanks for a valuable introduction to the “PERSONAL.xlsb”
I do not use it, instead keep all of my macros in a specific workbook.
Can you please inform us of the best way for a User to backup this “All Important” “PERSONAL.xlsb” workbook ?
I too donāt use a PERSONAL.XLBS file. Keep all the codes in one module, Export it as BAS file and when required, import the same into the New works.
This is where the “workbook” works in any machine with out any sharing
Thanks for the Mail and new tricks which you always share.
I use my macro by making them as an ADD-In.
I don’t use a PERSONAL.XLBS file, though I must admit I don’t use too much VBA.
For me, putting code (SUBs or FUNCTIONs) into a personal workbook means I may forget to transfer the required VBA code from it to the workbook I’m creating for others to use. Then things would fail when they tried to use it, a situation I try to avoid as much as possible.
I can always go to one of the workbooks I’ve created and copy the code as & when I need it. Maybe in the future I’ll end up creating one, but haven’t had a need to .. yet.
Great presentation, Very useful for the up and coming Excel Gurus that are learning.
Iām so glad you liked it.
Very useful.
I’m so glad you liked it.
As usual, very good.
Thanks for your words š
This is a good idea!
Yup š
Cool!!
Thanks š