Fix Excel VBA Function Works in One Workbook but Not Another
Question details
An Excel VBA user-defined function (UDF) operates perfectly in the original workbook but fails to calculate or return values when the exact same code is copied to a different workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using a custom VBA user-defined function in a new or different Excel workbook.
- Observed behavior
- The function fails to be recognized in worksheet formulas, often returning a #NAME? error, unless the original workbook is explicitly referenced in the formula.
Ensure that macros are enabled in the new workbook's Trust Center settings and that the file is saved as a Macro-Enabled Workbook (.xlsm or .xlsb).
Move the VBA Function to a Standard Module
User-defined functions (UDFs) must be placed in a standard module to be recognized globally by worksheet formulas. Storing them in Worksheet or ThisWorkbook modules prevents proper execution.
When you copy code from one workbook to another, it is easy to accidentally paste it into a Worksheet module (e.g., Sheet1) or the ThisWorkbook object. Code stored in these specific class modules cannot be called directly from standard cell formulas without explicit referencing.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left, right-click your current workbook project, hover over 'Insert', and select 'Module'.
Cut your user-defined function code from the incorrect Worksheet or ThisWorkbook module, and paste it into the newly created standard Module (usually named Module1).
Return to your Excel worksheet, select the cell containing your formula, click into the formula bar, and press Enter to recalculate.
Remove External Workbook References in Formulas
If your function works but relies on a reference to the old workbook, you need to break that link to make the new workbook independent.
Create and Manage VBA Functions Seamlessly in WPS Spreadsheet
WPS Office provides robust support for VBA and macros, allowing you to write, edit, and seamlessly execute user-defined functions just like in Microsoft Excel.
- 1. Access the Developer Tools: Open WPS Spreadsheet, navigate to the 'Tools' tab, and click on 'Developer'.
- 2. Open the VBA Editor: Click on 'Visual Basic Editor' or press Alt + F11 to launch the coding environment.
- 3. Insert a Module: Right-click your project, select 'Insert', and choose 'Module' to create a standard global module.
- 4. Use the Function: Paste your custom VBA code into the module, save the file, and immediately use the function in your spreadsheet formulas.
Frequently Asked Questions
Why does my custom VBA function return a #NAME? error?
This error typically occurs if macros are disabled in the Trust Center, the function name is misspelled in the formula, or the function code is incorrectly placed in a Worksheet or ThisWorkbook module instead of a standard module.
Can I use a VBA function from another closed workbook?
No. To use a user-defined function from another workbook, that external workbook must remain open. Alternatively, you can save the workbook containing the function as an Excel Add-in (.xlam) and install it so the function is always available.
What is the difference between a Standard module and a Worksheet module in VBA?
A Standard module makes its functions and macros globally accessible to all sheets within the workbook. A Worksheet module is a class module meant strictly for event handlers specific to that single sheet, such as clicking a button or changing a specific cell value.
