Personal Macro Workbook (PERSONAL.XLSB) in Excel

I started my way in programming with simple macros recorded with Excel’s built-in macro recorder. It was convenient: press a button, and Excel records your actions. Over time, I learned about the Personal Macro Workbook, a way to store your procedures so that they’re always at hand, in any file. That was a big step forward.

Personal macros

In Excel, you often need to automate everyday actions: find, delete or change certain characters, words, cells or whole ranges. Macros are handy for this: they run in one click and perform a recorded sequence of commands.

I use macros every day. They speed up the work a lot and let me focus on the task itself rather than on routine. Each such macro is made for specific needs: for the format of my tables and the logic I work with. That’s what personal macros are: the ones a particular user uses in their own environment.

However, when you record a macro, Excel by default saves it in the workbook that was open at that moment. As a result, the macro works only in one file and isn’t visible in other documents. That’s inconvenient: you have to copy macros manually, move modules and keep the copies in different files identical.

So where can you store personal macros so that they’re always available in any workbook, load automatically and don’t get in the way? The answer is the Personal Macro Workbook. It’s a special file that:

• is created once;
• opens automatically with Excel and stays open while at least one workbook is open;
• isn’t shown among the open workbooks (it stays hidden in the background);
• stores your macros, functions and other data.

Create the Personal Macro Workbook

To have your functions (UDFs) and procedures always at hand, you need to create the Personal Macro Workbook once. It starts with a perfectly ordinary action: recording a macro.

• Go to View → Macros → Record Macro (or Developer → Record Macro).
• In the Store macro in list, choose Personal Macro Workbook and click OK.
• Do anything, for example change the fill color of a cell, or nothing at all, and click Stop Recording.

The action itself doesn’t matter: what matters is starting the mechanism. Excel creates the PERSONAL.XLSB file, adds a module in the VBA editor and offers to save the changes when you close Excel. Say Save.

Creating the workbook is the basic step. Do it once, and you never have to think again about where to store your macros. Below: where the file is, how to open it for editing, how to copy, delete or move macros.

Save a macro to the Personal Macro Workbook

Once the workbook exists, you can save any procedures in it, recorded or written in the VBA editor. A macro saved once stays available in any Excel file.

The first way is to record new macros with Store macro in: Personal Macro Workbook. The second is to paste code directly: open the editor (Alt+F11), find VBAProject (PERSONAL.XLSB) in the project list, add a module if needed (Insert → Module) and paste the procedure. It’s a good place for both simple and multi-purpose macros grouped by task, like the ones that delete empty columns or merge CSV files.

Every new macro changes PERSONAL.XLSB. The file is saved when you exit Excel, so always agree to save it when you close the program. Otherwise the new macros won’t be written to the file.

Using your own functions from PERSONAL.XLSB

One thing that surprises many people: a user-defined function stored in the Personal Macro Workbook must be called with the file name in front of it. If you have a function WORDCOUNT there, on a sheet you write:

=PERSONAL.XLSB!WORDCOUNT(A1)

Without the prefix, Excel returns #NAME?. If that’s inconvenient, save your functions as an add-in (.xlam) instead and connect it via File → Options → Add-ins: functions from add-ins are called without any prefix. That’s exactly how the !SEMTools functions work.

Where is PERSONAL.XLSB located

After the first save, the file is stored in Excel’s startup folder, XLSTART. Excel opens every file in this folder at startup, and PERSONAL.XLSB loads from there in the background, hidden. It isn’t shown among regular documents, although technically it’s open: in the VBA editor (Alt+F11) you can see it’s loaded.

In Windows, the path usually looks like this:

C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART

The universal path with the %appdata% environment variable, to paste into the File Explorer address bar:

%appdata%\Microsoft\Excel\XLSTART

If you haven’t created the workbook yet, the folder may not exist, and Explorer will show an error. You can also get the exact path with VBA: run ?Application.StartupPath in the Immediate window (Ctrl+G in the editor).

Important: PERSONAL.XLSB must always stay in this startup folder. If it’s moved, renamed or deleted, your personal macros stop loading when Excel starts.

How to open and unhide the Personal Macro Workbook

The Personal Macro Workbook is an ordinary Excel file with the .xlsb extension. It has sheets, you can enter data and formulas, and save tables in it. The only difference is that Excel hides it at startup.

To show it, go to View → Unhide. PERSONAL.XLSB is in the list of windows. Select it, and the workbook becomes visible like any other.

When it’s visible, you can work with it like with a regular workbook: enter data, paste reference tables, keep notes. All of it is saved together with the macros. Usually nobody stores data there, but it’s not forbidden, just not common.

When you’re done, hide it again with View → Hide. Excel remembers this state and opens the file hidden next time. Edit the macros inside PERSONAL.XLSB in the VBA editor, like in any other workbook: add modules, delete procedures and change the code.

Copy code from the Personal Macro Workbook

Macros from the Personal Macro Workbook are easy to copy to other files: to pass a ready solution to a colleague, build a macro into a working file or collect your own library of functions and procedures.

• open the VBA editor (Alt+F11) and find PERSONAL.XLSB and its modules in the project list;
• select the code you need, copy it with Ctrl+C and paste it with Ctrl+V into a module of another workbook. If the target workbook has no module yet, add one via Insert → Module.

The reverse also works: paste a macro from another workbook into the Personal Macro Workbook so it’s always available.

Instead of copying single procedures, you can move a whole module: drag it in the project tree of the VBA editor, for example from Book1.xlsm to PERSONAL.XLSB. This keeps the whole code structure, including helper procedures and comments. The source stays in place, and an identical module is created in the target workbook.

To move your macros to another computer, I copy PERSONAL.XLSB from the XLSTART folder to the same folder on the new device. Just make sure macros aren’t blocked by the security settings there.

Delete a macro from the Personal Macro Workbook

If you no longer need a macro, delete it in the VBA editor. I open the editor (Alt+F11), find the module in PERSONAL.XLSB, select the procedure and simply delete its code. If the module contains only one macro, delete the whole module: right-click it → Remove Module. Excel offers to export a copy in case you need the code later.

You don’t need to save the file manually: Excel asks whether to save the changes to PERSONAL.XLSB when you exit. Agree, and the changes take effect.

Delete the Personal Macro Workbook

To delete the whole workbook rather than single macros, close Excel, go to the XLSTART folder and delete PERSONAL.XLSB like a regular file. After that, Excel stops opening it at startup, and all the macros stored in it are gone.

Common mistakes

• Macros from PERSONAL.XLSB don’t run, or the workbook doesn’t open at all. Check File → Options → Trust Center → Trust Center Settings → Macro Settings, and whether the workbook is listed in Add-ins → Manage: Disabled Items.
• The function returns #NAME?. Functions from PERSONAL.XLSB need the prefix: =PERSONAL.XLSB!FunctionName(A1).
• New macros disappeared after restarting Excel. You didn’t agree to save PERSONAL.XLSB when closing Excel.
• The file opened in another workbook shows #NAME? on another computer. Your colleague doesn’t have your PERSONAL.XLSB: for shared files, put the code into the workbook itself (.xlsm) or into an add-in.

Summary

The Personal Macro Workbook is a handy solution if you want your macros to be available in any Excel workbook. It lets you record and store personal procedures and speed up everyday work. When there are only a few functions and procedures, it’s ideal.

But one day the built-in macro recorder and the Personal Macro Workbook weren’t enough for me anymore. There were too many procedures and functions, and calling them from a flat list became hard. I wanted something more user-friendly: a clear hierarchy of procedures, menus, buttons, checkboxes and so on :) That’s how the work on !SEMTools began, a tool that grew out of the need to automate everyday tasks and make Excel more convenient for me and others.