VBA & Macro Problems

Make Excel VBA Macros Available in Every Workbook

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 869 views

Question details

The user wants to store Excel VBA macros in one place so they can run them in any workbook without creating an add-in.

Make Excel VBA Macros Available in Every Workbook
Product
Microsoft Excel desktop
Device & OS
not provided
Scenario
Reuse recorded or existing VBA macros across workbooks on the same computer.
Observed behavior
The desired result is a hidden Personal Macro Workbook that loads with Excel and makes its macros available to other open workbooks.
Before you start

Use desktop Excel, since Excel for the web cannot create or run VBA macros. The instructions below use Windows shortcuts; before moving existing code, check for workbook-specific references such as ThisWorkbook or named worksheets.

Solution 1Recommended

Create PERSONAL.XLSB with the Excel macro recorder

Use the Personal Macro Workbook to store reusable macros that load automatically when Excel starts.

1
Start recording a macro

Open Excel and select View > Macros > Record Macro. Enter a unique macro name without spaces, such as PersonalTest.

2
Choose the personal storage location

In the Record Macro dialog, set Store macro in to Personal Macro Workbook, then click OK. Excel creates PERSONAL.XLSB if it does not already exist.

3
Stop recording

Select View > Macros > Stop Recording. You can stop immediately if you only need to create the Personal Macro Workbook before adding existing VBA code.

4
Save the Personal Macro Workbook

Close Excel and choose Save when prompted to save changes to the Personal Macro Workbook. Reopen Excel so PERSONAL.XLSB loads in the background.

5
Run a personal macro

Open the workbook you want to work on, press Alt+F8, and set Macros in to All Open Workbooks if needed. Select the macro stored in PERSONAL.XLSB and click Run.

Create PERSONAL.XLSB with the Excel macro recorder
Available on this computer: PERSONAL.XLSB stays hidden and loads with Excel on this computer. It is not embedded in the workbooks you edit, so sending those workbooks to someone else does not include your personal macros.
Free Microsoft Office alternative

Use WPS Spreadsheet for everyday workbook editing

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for easy migration of everyday workbook tasks. It does not configure Excel's PERSONAL.XLSB; VBA support depends on the WPS edition and environment, so verify compatibility before moving macro-dependent workflows.

Open and edit Microsoft Excel .xlsx and .xls files.Use a familiar spreadsheet interface for formulas, tables, and routine workbook editing.Keep macro-dependent tasks in Excel while using WPS for everyday spreadsheet work.Move everyday Office tasks to a lightweight suite with free core editing tools.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my personal macro missing from the Alt+F8 list?

In Excel, press Alt+F8 and set Macros in to All Open Workbooks. Press Alt+F11 to confirm PERSONAL.XLSB is loaded. Check that the macro is a public Sub procedure with no required arguments in a standard module, rather than a Private procedure or a procedure in a module marked Option Private Module.

Do I need to unhide PERSONAL.XLSB to edit its macros?

No. Press Alt+F11 and expand VBAProject (PERSONAL.XLSB) in Project Explorer to edit its code while the workbook remains hidden. If you need to view its worksheets, use View > Unhide in Excel, then hide it again with View > Hide.

Why does a moved macro act on PERSONAL.XLSB instead of my open workbook?

ThisWorkbook always refers to the workbook containing the VBA code. After moving the code, that workbook is PERSONAL.XLSB. Press Alt+F11 and revise the code to use an explicit target workbook reference or ActiveWorkbook where appropriate.

Will my personal macros follow a workbook when I share it?

No. PERSONAL.XLSB is stored separately on your computer. To share a macro with a workbook, place the required code in that workbook through Alt+F11 and save it as an Excel Macro-Enabled Workbook (.xlsm).

Can I use PERSONAL.XLSB in Excel for the web?

No. Excel for the web cannot run VBA macros or load the desktop Personal Macro Workbook. Open the file in desktop Excel, then use View > Macros > View Macros to run an available personal macro.