VBA & Macro Problems

Make a Personal.xlsb VBA Function Available in Excel

Jason MillerJason Miller Nov 14, 2026 868 views

Question details

Make a user-defined VBA function stored in Personal.xlsb available in Excel worksheet formulas.

Make a Personal.xlsb VBA Function Available in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to call a function from Personal.xlsb in other workbooks and find it in Excel's function list.
Observed behavior
The function may return #NAME? or fail to appear in the function list when it is stored in the wrong module or workbook location.
Before you start

Open Excel desktop and confirm that the function code is available in the Visual Basic Editor. The XLSTART path below applies to Windows; enable macros only for files you trust.

Solution 1Recommended

Create an Excel add-in for the VBA function

Use an add-in to make the function available across workbooks without keeping its code in Personal.xlsb.

1
Create a standard module

Create a new workbook, press Alt+F11 to open the Visual Basic Editor, and select Insert > Module. Copy the function into this standard module, not a worksheet or ThisWorkbook module.

2
Check the function declaration

In the module, ensure the function is declared Public, such as Public Function MyFunction(...), and that the module does not contain Option Private Module.

3
Save as an add-in

Return to Excel and choose File > Save As. Select Excel Add-In (*.xlam) as the file type and save the add-in.

4
Load the add-in

On Windows, place the .xlam file in C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART and restart Excel. If it does not load, go to File > Options > Add-ins, select Excel Add-ins under Manage, click Go, and use Browse to select the file.

5
Test the function

In a worksheet, enter a formula such as =MyFunction(A1), using your function's actual name and arguments. Check Formulas > Insert Function and the User Defined category if you also want to find it in the function list.

Function list visibility: An add-in function can work in a formula without appearing as expected in Insert Function. Availability in the list may depend on how the function is registered.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not fix Excel's Personal.xlsb or add-in configuration, and VBA compatibility may vary.

Open and edit common Microsoft Excel formats, including XLSX and XLSMUse a familiar spreadsheet interface for everyday formulasMove spreadsheet work from Microsoft Office with minimal adjustmentWork in a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Personal.xlsb function return #NAME?

Excel may not be able to find the function if Personal.xlsb is not open, macros are disabled, or the code is not in a standard module. Check the module in the Visual Basic Editor and try a workbook-qualified formula such as =PERSONAL.XLSB!MyFunction(A1).

Where should I put a worksheet VBA function?

In the Visual Basic Editor, select the workbook and choose Insert > Module. Put the Public Function in that standard module rather than in a worksheet or ThisWorkbook module.

How do I enable an Excel add-in?

In Excel desktop, go to File > Options > Add-ins. Under Manage, select Excel Add-ins and click Go. Check the add-in or click Browse to locate its .xlam file.

Will an add-in function always appear in Insert Function?

Not necessarily. Check Formulas > Insert Function and the User Defined category, but a function may still work in a worksheet formula even if it is not listed as expected.