VBA & Macro Problems

How to Make a VBA UDF Available in All Excel Workbooks

Maira Mehtab Sep 27, 2026 872 views

Question details

The user needs to configure a VBA User-Defined Function (UDF) so that it can be executed in any Excel workbook, rather than being confined to the original file where it was created.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating custom formulas (UDFs) in Excel and needing to apply them universally across new and existing files for personal efficiency or distribution to other users.
Observed behavior: By default, a VBA UDF stored in a specific macro-enabled workbook (like Book1.xlsm) is only recognized and usable within that exact workbook.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that your Trust Center settings are configured to allow macros to run safely. Verify that your original UDF code works flawlessly in your current workbook before migrating it.

Solution 1Recommended

Store the UDF in the Personal Macro Workbook

This is the best method for personal use, as saving the function in PERSONAL.XLSB ensures the UDF loads automatically in the background whenever you open desktop Excel.

The Personal Macro Workbook is a hidden file that opens silently every time Excel launches. Storing your custom functions here makes them instantly available across any workbook you open on your computer.

Create the Personal Macro Workbook

If you haven't used it before, open Excel, go to View > Macros > Record Macro. In the 'Store macro in' dropdown, select 'Personal Macro Workbook', click OK, and then immediately click Stop Recording.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications Editor.

Insert the UDF Code

In the Project Explorer pane on the left, locate 'VBAProject (PERSONAL.XLSB)'. Right-click it, select Insert > Module, and paste your UDF code into the new blank module window.

Save and Apply

Click the Save icon in the VBA Editor toolbar. You can now use your custom function in any local workbook by typing '=PERSONAL.XLSB!YourFunctionName()' into a cell.

One-Time Setup: Once PERSONAL.XLSB is generated and saved, it will persistently load your saved macros and UDFs during every future Excel session.

Solution 2

Convert the Workbook into an Excel Add-in (.xlam)

Creating an Excel Add-in is the ideal approach if you intend to distribute your UDF to colleagues or teams without sharing a standard workbook file.

Solution 3

Create a Macro-Enabled Template (.xltm)

Best for situations where you want the UDF automatically included only when generating specific types of new, standardized workbooks.

WPS Spreadsheet Macros

Create and Run VBA Functions Across Workbooks in WPS Office

WPS Office Spreadsheet provides robust support for VBA macros and User-Defined Functions. You can easily write, save, and run your custom scripts in .xlsm, .xlam, or .xltm formats just like in Microsoft Excel.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the top ribbon, click on the 'Developer' tab, and verify your macro security settings.
2. Open Visual Basic Editor: Click on 'Visual Basic Editor' (or press Alt + F11) to access the VBA environment in WPS Office.
3. Insert UDF Code: Right-click your workbook project, select Insert > Module, and paste your custom User-Defined Function code.
4. Save as Add-in: Go to Menu > Save As, select 'WPS Spreadsheets Add-In Files (*.xlam)' to save the function globally for use across all WPS Spreadsheet files.

Native support for VBA macros and Excel-compatible UDFsFull compatibility with .xlsm, .xltm, and .xlam file formatsLightweight application with a familiar Developer tab interfaceCost-effective and highly efficient alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my VBA macro run in Excel for the web?

VBA macros and User-Defined Functions rely on the desktop architecture of Excel. Excel for the web does not support VBA execution; you must open the workbook in the desktop application to run or edit these scripts.

Can I save VBA code in a standard .xlsx file?

No. The standard .xlsx file format automatically strips out all VBA code upon saving for security reasons. You must use a macro-enabled format such as .xlsm, .xltm, or .xlam to retain your VBA functions.

How do I call a UDF stored in the Personal Macro Workbook?

To use a UDF from the Personal Macro Workbook in a standard cell formula, you typically need to prefix the custom function name with the workbook's name, writing it as `=PERSONAL.XLSB!MyFunction(A1)`.

What is the difference between an .xlsm file and an .xlam file?

An .xlsm is a standard macro-enabled workbook where users interact with visible sheets, data, and charts. An .xlam is an add-in file that operates invisibly in the background, providing global access to its custom macros and UDFs without exposing its worksheets to the user.

Excel & Data Problems