How to Make an Excel Macro Available in All Workbooks

How to make an Excel macro available in all workbooks

To make a macro available in all workbooks, store it in your Personal Macro Workbook. Microsoft describes Personal.xlsb as "a hidden workbook stored on your computer, which opens in the background every time you open Excel," so any macro inside it is always loaded.

One thing to settle before you start, because it causes more confusion than the setup does: this makes the macro available in all of your workbooks, on this computer. It does not share it with anyone else.

TL;DR: Key takeaways

• Store the macro in Personal.xlsb and it runs in every workbook you open.
• Excel creates the file for you the first time you record a macro into it.
• It lives in a folder called XLSTART and opens hidden at every startup.
• It is per computer, not per person. Colleagues do not get your macros.
• To give a macro to someone else, send them the workbook, not your Personal.xlsb.

Create your Personal Macro Workbook

You do not create the file directly. Excel makes it the first time you record a macro into it, so record a throwaway one.

1. On the Developer tab, click Record Macro.
2. Leave the default name. This macro is disposable.
3. In Store macro in, choose Personal Macro Workbook, then click OK.
4. Click Stop Recording without doing anything.
5. Close Excel. When prompted, save the Personal Macro Workbook.

Microsoft calls step 3 the important one, because that is what triggers Excel to create the file if it does not exist yet. If the Developer tab is not showing, enabling it takes a few seconds, though you can also reach the recorder without it.

Move an existing macro into it

Once the file exists, it appears in the Visual Basic Editor like any other project.

1. Press Alt + F11 to open the VBA editor.
2. Find VBA Project (PERSONAL.XLSB) in the Project Explorer on the left.
3. Expand Modules and open a module, or right-click and choose Import File to bring in a .bas.
4. Paste or import your code, then save.

That import route is the tidy way to move a macro you downloaded. Adding a macro from a .bas file covers the details, including the one line you have to delete if you paste instead of importing.

From then on, Alt + F8 lists that macro no matter which workbook you are in.

"All workbooks" means yours, not your team's

This is the part that catches people, and it is worth being blunt about.

Personal.xlsb is a file on one computer. It makes your macros follow you between workbooks on that machine. It does not travel to a colleague, it does not live in the cloud, and a workbook you email does not carry it. Someone who opens your spreadsheet and tries to run the macro gets an error, because the code was never in the file you sent.

Microsoft's own guidance treats sharing as a separate job with a separate answer: to give your macros to someone else, "you can copy it to the XLSTART folder on other computers." That is a manual file copy onto each machine, not a setting.

There is also a practical catch with copying it wholesale. If the other person already has a Personal.xlsb, overwriting it destroys their macros. Copy individual modules across instead of the whole file.

The better way to share a macro

If the macro needs to work for other people, put it in the workbook rather than in your personal file.

• Send the workbook itself. Save it as .xlsm so the code survives, and the macro travels with the file. Anyone who opens it can run it, once they allow macros.
• Send the .bas file. Plain text, readable before running, and the recipient imports it wherever they want it. This is how every macro in our free tools library is published.
• Package it as an add-in. An .xlam installs once and loads in every workbook, like a Personal Macro Workbook that you can hand to someone. Turning a macro into an add-in covers it.

The rule of thumb: Personal.xlsb is for your own convenience. A workbook, a .bas or an add-in is for anyone else.

Frequently asked questions

Where is Personal.xlsb stored?

In a folder called XLSTART. On Windows you can search for XLSTART in File Explorer to find it. Excel opens everything in that folder automatically at startup, which is what makes the macros inside always available.

Why can't I see my Personal Macro Workbook?

Because it opens hidden by design. It will not appear as a window, but it shows in the Visual Basic Editor under VBA Project (PERSONAL.XLSB). To see it as a normal workbook, use View > Unhide and pick it from the list.

Will my colleagues get my Personal.xlsb macros?

No. It is stored on your computer and applies only to your copy of Excel. Sending them a workbook does not include it. To share a macro, send the .xlsm that contains it, or send the .bas file for them to import.

Can I use the Personal Macro Workbook on a Mac?

Yes. Microsoft's guidance covers newer Mac versions of Excel as well as Windows, and the recording route to create it is the same. The main difference is where the file sits on disk.

Should every macro go in Personal.xlsb?

No. Put a macro there when it is a general tool you use across many files, such as a cleanup routine. Keep it in the workbook when it is specific to that file's layout, or when other people need to run it too.

What to do next

Create the file once with a throwaway recording, then move your most-used macro into it. Pair it with a keyboard shortcut or toolbar button, which is where the Personal Macro Workbook really pays off, since shortcuts tied to an ordinary workbook stop working the moment that file is closed. To clear out anything you no longer want, see deleting or editing a macro.