How to Create Excel PERSONAL.XLSB MACRO Workbook for VBA Codes

…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:UsersPuneet GogiaAppDataRoamingMicrosoftExcelXLSTART

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

Some of the IMPORTANT things you need to Know

Once you added a PERSONAL.XLBS file in the Excel’s startup folder…

…there are a few things which you need to know so that you can manage it in the right way.

Share it

Delete it

Unable to Use it

Hide it

Transfer Codes

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

But now, tell me one thing.

How you Manage all the VBA codes you use?

You Must Read these Tips Next

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

13 thoughts

Leave a Comment

Your email address will not be published.

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

  2. Thanks for the Mail and new tricks which you always share.
    I use my macro by making them as an ADD-In.

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