Use Excel User-Defined Functions in Worksheet Formulas
Question details
Determine whether a VBA user-defined function (UDF) can be entered and calculated in an Excel worksheet formula.
- Product
- Microsoft Excel desktop for Windows and Mac
- Device & OS
- Windows and macOS
- Scenario
- The user wants to use a custom VBA function as part of a worksheet calculation.
- Observed behavior
- A UDF can return a calculated value to the cell containing the formula, but it cannot directly change values in other worksheet cells.
Use a desktop version of Excel, because VBA UDFs do not run in Excel for the web. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and enable macros when you open it.
Call a public Excel VBA user-defined function from a worksheet formula
Use this method when you need a custom calculation that returns a result in the formula cell.
Excel desktop for Windows and Mac can use a VBA UDF in a worksheet formula. The function should be declared Public and placed in a standard VBA module so Excel can find it.
In Excel, press Alt+F11 on Windows, or open the Visual Basic Editor from the Developer tab on Mac.
In the Visual Basic Editor, select Insert > Module, then add a public function such as: Public Function AddFee(amount As Double) As Double: AddFee = amount * 1.05: End Function.
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) so the VBA code is retained.
Return to the worksheet, select a cell, and enter a formula such as =AddFee(A1). Press Enter to display the value returned by the function.
Keep worksheet changes outside the Excel user-defined function
Use this approach when your VBA code needs to update cells, format a range, create sheets, or perform other actions beyond returning a value.
Use WPS Spreadsheet for compatible everyday Excel workbook editing
For spreadsheet work that does not depend on Excel-specific VBA behavior, WPS Office provides a free, lightweight alternative with a familiar spreadsheet interface and seamless migration for common Office files. You can open, edit, and share compatible Excel workbooks while using built-in formulas for routine calculations.
Frequently Asked Questions
Can I use a VBA UDF in Excel for the web?
No. Excel for the web does not run VBA macros or worksheet UDFs. Open the .xlsm workbook in the desktop Excel application on Windows or Mac.
Why does my custom Excel function show #NAME? in a worksheet?
Make sure the function is declared Public, is stored in a standard module created with Insert > Module, and that macros are enabled for the macro-enabled workbook.
Does an Excel UDF have to be in an .xlsm file?
Yes, if the workbook must retain VBA code. Use File > Save As > Excel Macro-Enabled Workbook (*.xlsm); saving as .xlsx removes VBA macros.
Can a user-defined function write a result into another cell?
No. A UDF called by a worksheet formula should only return its calculation to the formula cell. Use a separate Sub macro, run from Developer > Macros or a button, to change other cells.
