VBA & Macro Problems

Fix Excel VBA Function Returning #NAME? Error in Older Workbooks

Muhammad Talha Oct 20, 2026 868 views

Question details

The user's custom Excel VBA function works perfectly in new files but displays a #NAME? error when used in older, saved workbooks.

How to Fix Excel VBA Function Returning #NAME? Error in Existing Workbooks

Product: Microsoft Excel 2019
Device & OS: not provided
Scenario: Attempting to use a custom VBA function (such as SPELLCURR) in previously saved Excel files.
Observed behavior: The custom function returns a #NAME? error instead of executing properly, indicating Excel might not be loading the VBA project or recognizing the function.

Before you start

Ensure that your Excel file is saved in a macro-enabled format (.xlsm) and that you have verified the exact spelling of your custom function before modifying Trust Center settings.

Solution 1Recommended

Verify Macro and Trust Center Settings

The most common reason for the #NAME? error in existing workbooks is that macros are disabled upon opening or the file location is not fully trusted by Excel.

By default, Excel blocks macros originating from untrusted locations or older files to protect your system. Adding the workbook's folder to Trusted Locations ensures the VBA project loads completely.

Access the Trust Center

In Excel, navigate to File > Options > Trust Center, and click the 'Trust Center Settings' button.

Enable Macros

Select 'Macro Settings' from the left sidebar and ensure that 'Disable all macros with notification' or 'Enable all macros' is selected so they are not silently blocked.

Add a Trusted Location

Navigate to 'Trusted Locations' in the left menu, click 'Add new location', and browse to the folder where your older workbook is saved.

Recalculate Formulas

Reopen the affected workbook, click 'Enable Content' if prompted at the top of the screen, and press F9 or go to Formulas > Calculate Now to refresh the #NAME? errors.

Trust Center Updated: Adding the file's folder to Trusted Locations prevents Excel from repeatedly blocking your custom macros.

Solution 2

Check VBA Module Declaration and Code Placement

Excel will return a #NAME? error if the custom function is placed in the wrong type of module or is not declared as Public.

Solution 3

Rebuild Workbook to Fix VBA Corruption

If settings and code are perfectly correct but the older workbook still fails, its internal VBA project may be corrupted.

Free Microsoft Office alternative

Use WPS Office for Seamless Macro Compatibility

If Microsoft Excel continues to struggle with corrupted VBA projects or strict Trust Center restrictions, WPS Office provides a lightweight, highly compatible alternative. Its spreadsheet tool fully supports .xlsm formats and standard VBA macros, making it easy to transition and run your custom functions without constant security blockages.

1. Install WPS Office: Download and install WPS Office Free from the official website.
2. Open Your Workbook: Launch WPS Spreadsheet and go to Menu > Open to select your macro-enabled (.xlsm) file.
3. Enable Macros: Click 'Enable Macros' in the security warning prompt to safely run your custom VBA functions and eliminate the #NAME? errors.

Fully compatible with Microsoft Excel file formats (.xlsx, .xlsm, .csv).Robust built-in support for VBA and macros to run your custom functions seamlessly.Lightweight installation with a familiar, easy-to-use tabbed interface.Free to use with comprehensive spreadsheet, document, and presentation tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA function work in a new workbook but not an old one?

Older workbooks may have stricter Trust Center settings applied automatically by Excel, could be saved in a standard format that does not support macros (.xlsx instead of .xlsm), or might have developed a corrupted internal VBA project over time.

What does the #NAME? error mean when using a custom VBA function?

The #NAME? error indicates that Excel cannot recognize the function name you typed in the formula bar. This happens if macros are disabled, the function name is misspelled, or the VBA code is hidden inside a private or incorrect module type.

How do I ensure macros are enabled for a specific folder in Excel?

Go to File > Options > Trust Center > Trust Center Settings. Select 'Trusted Locations', click 'Add new location', and browse to the specific folder. Any Excel files opened from this folder will bypass strict security checks and have macros automatically enabled.

Can I recover my custom functions if my Excel workbook is heavily corrupted?

Yes, you can usually salvage your custom code by opening the VBA Editor (Alt + F11), manually copying the text of your macros, and pasting them into a standard module of a brand-new .xlsm workbook.

Excel & Data Problems