Fix Excel VBA Custom Function #NAME? Errors in a Workbook
Question details
The user needs to make an Excel VBA custom worksheet function, such as BARCODE128_ENCODED, work in a workbook where it returns #NAME? or produces no result.
- Product
- Microsoft Excel VBA custom functions
- Device & OS
- not provided
- Scenario
- A custom VBA function works in one workbook but fails after being used, copied, or recreated in another workbook.
- Observed behavior
- Excel displays #NAME? for the custom function or the formula does not return the expected result.
Open the affected workbook in the Excel desktop app, because Excel for the web cannot run VBA macros. Confirm that you have permission to enable the workbook's macros when it opens.
Save the workbook as .xlsm and enable macros
Use this first when a custom function works in one workbook but is not recognized in another.
A VBA user-defined function is stored in the workbook's VBA project. If the file was saved as .xlsx, the VBA project is removed, and worksheet formulas that call the function can return #NAME?.
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Save the workbook, close it, and reopen the new .xlsm file.
When the yellow Security Warning appears below the ribbon, select Enable Content. If no prompt appears, go to File > Options > Trust Center > Trust Center Settings > Macro Settings and review your organization’s macro policy.
Click a cell containing the formula, such as =BARCODE128_ENCODED(A1), press F2, and then press Enter to recalculate it after macros are enabled.
Place the public function and helper functions in a standard module
Use this when macros are enabled but Excel still cannot find the function in worksheet formulas.
Repair missing references and compile the VBA project
Use this when the code is in a standard module but the workbook still returns #NAME? or fails after being moved to another computer.
Open and edit macro-enabled Excel workbooks with WPS Office
If you need a free, lightweight office suite for everyday spreadsheet work, WPS Office offers a familiar interface and seamless migration for Microsoft Office files. You can open and edit compatible Excel workbook formats while keeping your spreadsheet content accessible; VBA behavior can vary, so validate custom macro functions in Microsoft Excel when full VBA compatibility is required.
Frequently Asked Questions
Why does my Excel custom function show #NAME? after I save the workbook?
The workbook may have been saved as .xlsx, which does not retain VBA code. Use File > Save As > Excel Macro-Enabled Workbook (*.xlsm), reopen the file, and select Enable Content if Excel shows a security warning.
Where should I place a VBA worksheet function in Excel?
Press Alt+F11 and place the function in a standard module created through Insert > Module. Functions stored only in a worksheet module or ThisWorkbook may not be available as worksheet user-defined functions.
How do I find a missing VBA reference that prevents my function from working?
Open the Visual Basic Editor with Alt+F11, then choose Tools > References. Look for any item marked MISSING:, correct or clear it, and select Debug > Compile VBAProject.
Does a helper function affect an Excel VBA custom function returning #NAME??
Yes. If BARCODE128_ENCODED calls a helper such as ONLY_DIGITS, that helper must exist, have the correct name, and be accessible from the same VBA project. Compile the project through Debug > Compile VBAProject to locate missing code or errors.
Can Excel for the web run VBA custom functions?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the Excel desktop application, enable content, and then test the custom function.
