Fix Excel VBA User-Defined Function Returning #NAME? in Microsoft 365
Question details
The user is encountering a #NAME? error when trying to calculate a VBA user-defined function in web-based Microsoft 365 environments, even though the same function works correctly in desktop Excel 2016.
- Product
- Microsoft Excel for the web
- Device & OS
- not provided
- Scenario
- Opening a macro-enabled workbook containing VBA user-defined functions in Excel for the web, SharePoint Online, or Microsoft Teams.
- Observed behavior
- The VBA user-defined function fails to execute and returns a #NAME? error in the cells instead of the expected calculated result.
Ensure you have access to a desktop version of Microsoft Excel or WPS Office, as web-based Excel environments strictly do not support the execution of VBA macros or user-defined functions.
Open the Workbook in the Desktop Version of Excel
The most straightforward solution is to switch from the web environment to the desktop application, which fully supports VBA macro execution.
Excel for the web lacks the backend compilation engine required to run Visual Basic for Applications (VBA). By opening the file in your local desktop application, the #NAME? errors will automatically resolve as the VBA engine calculates the functions.
While viewing your workbook in Excel for the web, SharePoint, or Teams, look at the top ribbon menu where the primary file options are located.
Click the 'Viewing' or 'Editing' dropdown menu on the ribbon and select 'Open in Desktop App'. Alternatively, click the dedicated 'Open in Desktop App' button if visible.
Once Excel launches on your desktop, click 'Enable Content' in the yellow security warning bar to allow the VBA user-defined functions to run and calculate properly.
Replace VBA with Native Worksheet Formulas
If you must use Excel for the web, replace the custom VBA user-defined function with a combination of standard built-in Excel formulas.
Recreate the Function Using Office Scripts
For complex automation that requires coding in Excel for the web, use Office Scripts instead of VBA.
Use WPS Office to Run Excel VBA Macros Seamlessly
Since Excel for the web cannot run VBA macros, you can switch to WPS Office. It provides an excellent, lightweight desktop alternative that is highly compatible with Microsoft Excel formats (.xlsm) and supports VBA execution in its advanced editions.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office installer.
- 2. Install the software: Run the downloaded installer and follow the on-screen prompts to set up the lightweight application on your computer.
- 3. Open your macro-enabled workbook: Launch WPS Spreadsheets and open your .xlsm file to seamlessly execute your VBA user-defined functions.
Frequently Asked Questions
Why do my custom Excel functions say #NAME? when uploaded to SharePoint?
SharePoint Online uses Excel for the web to display workbooks. Because the web version does not support VBA, it doesn't recognize the custom function's name, resulting in a #NAME? error. You must open the file in a desktop app to evaluate the functions.
Can I force VBA macros to run in Microsoft Teams?
No, the built-in file viewer in Microsoft Teams relies entirely on Excel for the web architecture. To run VBA macros from Teams, you must click the menu icon next to the file and choose the 'Open in Desktop App' option.
Is there any setting in Excel for the web to enable VBA?
There are no settings, flags, or workarounds to enable VBA in Excel for the web. The platform natively lacks the backend compilation engine required to execute Visual Basic code.
Are there web-compatible alternatives to VBA in Microsoft 365?
Yes, Microsoft recommends using Office Scripts (based on TypeScript) for automation on the web, or replacing custom VBA functions with modern built-in Excel formulas like LAMBDA.
