If Excel does not recognize a VBA function, first check that it is a Public Function in a standard module in a macro-enabled workbook, then confirm macros are allowed and the worksheet formula spells the function name correctly. A missing or unavailable function often appears as #NAME?, but a function that runs and receives the wrong kind of input may produce a different error.
What “function not recognized” usually means
A VBA user-defined function (UDF) is code that returns a value to a worksheet cell. For example, a function can calculate a result that you could also calculate with a built-in formula. When Excel cannot find the function referenced in a cell, it may display #NAME?. That is different from an error inside a function: if Excel finds and runs the code but the calculation fails, the result might instead be #VALUE! or another error.
Start by distinguishing the symptoms. A cell showing #NAME? points toward a missing, inaccessible, or misspelled function name. A VBA compile error points toward invalid code or a missing reference. A formula that returns an old result may be a recalculation issue. These problems have different fixes, so note exactly what Excel displays before changing settings or rewriting the function.
Put the function in a standard module
For a worksheet cell to call a normal VBA UDF, place its procedure in a standard module—not in a worksheet’s code module, the ThisWorkbook module, or a class module. In Excel for Microsoft 365 on Windows, open the VBA editor using Developer > Visual Basic. In the editor, choose Insert > Module, then add the function there. If the Developer tab is missing, enable it through File > Options > Customize Ribbon and check Developer.
The procedure should be public. In a standard module, a function without a visibility keyword is normally public by default, but writing Public makes the intent clear. For instance, with a number in cell A2, the following function returns three times that number:
Option Explicit
Public Function TripleValue(ByVal number As Double) As Double
' Return three times the input value.
TripleValue = number * 3
End Function
With the function saved in a standard module and a numeric value in A2, enter =TripleValue(A2) in another cell. If A2 contains 4, the result is 12. If the function is declared Private, it is not available as a worksheet function. The VBA statement Option Private Module has a narrower effect: it hides a module’s public members from other VBA projects, but it does not normally prevent formulas in the same workbook from calling them.
Check the formula name and arguments
The name in the worksheet formula must match the VBA function name. VBA procedure names cannot contain spaces, and names that collide with built-in Excel functions can cause confusion. Choose a distinctive name, such as TripleValue, rather than a generic or familiar Excel function name. Also check for a spelling difference, an extra character, or an accidental space in the formula.
With a numeric input in A2 and the function named TripleValue as shown above, the worksheet formula is =TripleValue(A2). A UDF call looks like a regular formula: start with an equals sign, use the function name, and supply its arguments in parentheses. For a function with multiple arguments, match the number and order of inputs declared in VBA. Excel’s formula argument separator can vary with regional settings, so a multi-argument formula may use semicolons rather than commas on some systems; follow the separator Excel accepts in that installation.
Argument types matter too. The example accepts a number as a Double. If a cell contains text that cannot be converted to a number, the UDF may return a value error rather than #NAME?. Test the function with a simple numeric cell first. If that works, inspect the actual values and types being passed by the more complicated formula.
Confirm the workbook format and macro access
VBA code needs to remain in a macro-enabled workbook. Save the file as Excel Macro-Enabled Workbook (.xlsm) or Excel Binary Workbook (.xlsb) when it contains VBA. The standard .xlsx format does not store VBA projects; saving a macro-enabled file as .xlsx prompts that the VBA project will be removed if you continue. If the code disappeared after a format change, reopen a copy saved before that change or restore the code from another source.
Excel’s macro security can also prevent code from running. On Windows, review File > Options > Trust Center > Trust Center Settings > Macro Settings, or follow the security prompt Excel displays for the file. Do not enable all macros indiscriminately just to test an unfamiliar workbook. Prefer files from a source you trust, and follow your organization’s security policy. A blocked or disabled macro can leave a worksheet unable to evaluate a VBA function.
Excel for the web does not run VBA macros. A workbook containing VBA can be stored or opened there, but its VBA UDF cannot calculate in the browser as it does in desktop Excel. Use desktop Excel to run and troubleshoot the VBA function. Excel for Mac supports VBA, although its menus and security controls differ from Windows; the Windows paths above should not be assumed to match every Mac release.
Compile the project and check for code errors
Open the VBA editor and choose Debug > Compile VBAProject. If Excel reports an error, fix that error and compile again. A compile error elsewhere in the same VBA project can interfere with running code, even if the UDF itself looks correct. Review the highlighted line and any message carefully rather than treating every failure as a missing function.
References can be another source of trouble. In the VBA editor, choose Tools > References and look for an entry marked “MISSING.” A project that relies on a missing library may fail to compile or run correctly. Do not uncheck references at random: the code might depend on them. If you control the workbook, determine which library is required and whether it is installed or can be replaced safely.
After edits, save the workbook, return to the sheet, and test the simplest call again. If the code is in a different workbook or an add-in, confirm that workbook or add-in is open and available. A UDF stored in an unloaded add-in cannot be found by the current Excel session.
Consider recalculation and worksheet-function limits
A working UDF may not update when you expect. Excel normally recalculates a worksheet function when one of its referenced arguments changes. If the VBA function reads a cell or other information that is not included among its arguments, Excel may not know that its result has become stale. A developer can make a UDF volatile with Application.Volatile, but that causes recalculation more often and may slow a workbook. A better design is usually to pass the relevant changing values as arguments.
For a manual recalculation, press F9 in Excel for Windows; on Mac, the equivalent can depend on the keyboard and system settings, and may require the Fn key. Check Formulas > Calculation Options to see whether the workbook is set to Automatic or Manual. Manual calculation can explain a stale value, but it does not fix a genuinely unrecognized function name.
Worksheet UDFs are intended to return values, not to act like macros that edit other cells, change formatting, or perform workbook actions. If the goal is to write values into other cells or change the workbook, use a regular VBA Sub procedure launched as a macro or another appropriate automation feature. Keeping calculation functions focused on returning results makes them easier to maintain and less surprising in a sheet.
A practical troubleshooting order
Work through these checks in order, testing the formula after each relevant change. This avoids making broad security or code changes when a small spelling or placement issue is responsible.
- Read the exact cell error: distinguish
#NAME?from a compile error, a value error, or a stale result. - In the VBA editor, verify that the code is a
Public Functionin a standard module. - Compare the worksheet function name and its arguments with the procedure declaration.
- Confirm the workbook is saved as
.xlsmor.xlsb, and that the code is still present. - Check whether Excel has blocked macros, and only allow code you trust.
- Compile the VBA project and resolve reported errors or missing references.
- Test a simple input, then check calculation mode and any add-in dependency.
For example, if =TripleValue(A2) returns #NAME?, first verify that the function is named exactly TripleValue and resides in a standard module. If the same formula returns 12 when A2 is 4 but does not update after a change, investigate calculation settings or how the function obtains its inputs instead.
When to use an alternative to VBA
If the calculation can be expressed with built-in worksheet functions, that is often the most portable choice. Built-in formulas work in Excel for the web and do not require a VBA project or macro permissions. For reusable custom calculations in Microsoft 365, Excel also offers the LAMBDA function, which can define a named formula without VBA; LAMBDA is available in Microsoft 365 and Excel 2024. Power Query or Office Scripts may suit some data-preparation or automation tasks, but they serve different purposes and are not drop-in replacements for every worksheet UDF.
Choose based on where the workbook must run and who will use it. A VBA UDF is reasonable when desktop Excel users need a calculation implemented in VBA. A built-in formula, named LAMBDA, or another supported tool is usually preferable when users need browser compatibility or cannot enable macros.
Frequently Asked Questions
Why does my VBA function show #NAME? in Excel?
Excel generally shows #NAME? when it cannot resolve the function name. Check spelling, code location, public visibility, macro availability, and whether the workbook or add-in containing the function is available.
Does a VBA function have to be in a standard module?
For a typical worksheet UDF, yes. Put the public function in a standard module using Insert > Module in the VBA editor. A function placed in a worksheet, workbook, or class module may not be exposed as a normal worksheet function.
Will a VBA UDF work in Excel for the web?
No. Excel for the web does not execute VBA macros, so a VBA-defined worksheet function will not calculate there as it does in desktop Excel. Use desktop Excel or consider a non-VBA alternative if browser use is required.
Why does the function work on one computer but not another?
The other computer may have macros blocked, may not have the workbook saved with its VBA project, or may lack a required reference or add-in. Check the file format, security prompt, project references, and whether the code is available in that Excel session.
Can I use a VBA function to change other cells?
A worksheet UDF should return a value to its formula cell; it is not the right tool for changing other cells or formatting. Use a Sub macro for actions that modify the workbook, and save a copy before running a macro because its changes cannot be undone with Ctrl+Z.
Related
- Excel Text to Columns and Rows: How to Split Data
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Font Color: Set, Read, and Fix ItIn Excel VBA, set a cell’s font color with the Font.Color property, usually by assigning an RGB value—for…
- Excel VBA Convert Date to Number: Serial ValuesYes. Excel dates are stored as serial numbers, so VBA can read a date cell’s underlying number with…
- Excel VBA Loops: How to Repeat Tasks EfficientlyAn Excel VBA loop repeats a block of code until it has processed a set number of items…
- VBA Array Notation: Syntax, Bounds, and ExamplesIn VBA, declare an array with parentheses after its name, such as Dim scores(1 To 5) As Long,…
- VBA Copy and Paste Values Across All Excel Tabs SafelyYes. In Excel for Microsoft 365 on Windows, a VBA macro can take the selected range on the…
- VBA Right Function: Syntax, Examples, and Common ErrorsIn VBA, use Right(string, length) to return a specified number of characters from the end of a string.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.