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

- 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.
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.
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.
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.
In the module, ensure the function is declared Public, such as Public Function MyFunction(...), and that the module does not contain Option Private Module.
Return to Excel and choose File > Save As. Select Excel Add-In (*.xlam) as the file type and save 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.
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.
Call the function directly from Personal.xlsb
Use this approach if you want to keep the code in Personal.xlsb and call it from another workbook.
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.

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.




