Personal Macro Workbook (personal.xlsb)

Home ➜ VBA ➜ Personal Macro Workbook (personal.xlsb)

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 which can be the common storage place to store your all macros and use them. Although it’s a hidden file which 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:
  1. Enter a name for the Macro
  2. 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” 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 the 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 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.

Can I Share My Personal Macro Workbook with Others?

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 in 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 at 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 don’t have a Personal Macro Workbook then you can simply copy-paste personal.xlsb from the old system to 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 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 open 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 the “Hide” and select the “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 the “Read Only”.

• In the end, click OK.

Once you do this, you can be able to read 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 is to create a personal.xlsb is to simply record a macro in it. I hope you found this VBA tip will help you to Learn VBA, Faster.

But now, tell me one thing.

How 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.