Excel VBA Function Name Error: Common Causes and Fixes

An Excel VBA function-name error usually means VBA cannot parse or find the procedure name, or Excel cannot recognize a custom function used in a worksheet formula. Check the exact error first: “Expected: identifier,” “Sub or Function not defined,” “Ambiguous name detected,” and the worksheet error #NAME? point to different problems.

What a function-name error means

VBA functions are procedures with names that identify them in code. A name error can occur while you are editing or compiling VBA, or later when you enter the function in a worksheet cell. Those are separate stages. For example, VBA may reject a malformed function declaration before Excel can use it; alternatively, the code may compile but a worksheet formula may not find the function.

Start by noting where the message appears and its exact wording. A dialog in the Visual Basic Editor is generally a compile or code-resolution issue. A cell showing #NAME? means Excel did not recognize part of the formula, which may include a misspelled UDF name. A different worksheet error can point to invalid arguments or a problem inside the function rather than its name.

VBA function naming rules

A VBA procedure name must begin with a letter. It can contain letters, numbers, and underscores, but not spaces or punctuation such as hyphens. It must not be a VBA reserved word, and it should be distinct from other procedure and project names that could make a reference ambiguous. Names are not case-sensitive, so CalculateTotal and calculatetotal are treated as the same identifier.

Choose a concise name that describes the result, such as CalculateDiscount, rather than a generic name such as Value or Test. Avoid names that match built-in Excel functions, VBA keywords, workbook names, or other project members. A distinctive prefix can help when a workbook contains many custom functions. If you change a function name in the VBA editor, update every formula or macro that calls the old name.

Identify the message before changing code

“Compile error: Expected: identifier” commonly indicates that VBA cannot read the name where it expects a procedure or variable identifier. Check for spaces, punctuation, a missing name, or a reserved word in the declaration.
“Compile error: Sub or Function not defined” means VBA cannot resolve a called procedure. Check spelling, whether the procedure exists, and whether its visibility and module location allow that code to call it.
“Ambiguous name detected” points to a name that VBA cannot uniquely resolve, often because the project contains duplicate procedures or conflicting identifiers. Search the project for the name and rename one of the conflicts.
#NAME? in a cell means Excel does not recognize a name in the formula. Check the formula spelling, whether the function is in a suitable module, whether macros are available, and whether the workbook containing the function is open.

The exact wording and the line highlighted by the editor matter. Do not rename a function just because a formula returns an error: if the name is correct, the cause could instead be a missing argument, an unsupported operation, or an error value returned by the code.

Make a simple worksheet function

A worksheet UDF (user-defined function) is a VBA function that returns a value to a cell. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, then place a public function in that standard module. A function stored in a worksheet code module or in ThisWorkbook is not the usual location for a worksheet UDF.

Public Function AddTwoValues(ByVal firstValue As Double, ByVal secondValue As Double) As Double
    ' Return the sum of the two supplied values.
    AddTwoValues = firstValue + secondValue
End Function

In this example, the function name is AddTwoValues. It starts with a letter, has no spaces, and is used consistently in the declaration and return assignment. The Public keyword makes the procedure available beyond its module in the project; for an ordinary worksheet UDF, keep it in a standard module and do not declare it Private.

With numbers 12 and 5 in cells A1 and B1, enter =AddTwoValues(A1,B1) in another cell. It returns 17. The example assumes the function code is in the workbook where the formula is entered. If Excel displays #NAME?, confirm the spelling and that the code is saved in that workbook.

Check the module, scope, and workbook

Where the function lives affects whether Excel can use it. For a normal cell formula, put the UDF in a standard module created with Insert > Module. Confirm the declaration begins with Public Function, and ensure there is no compile error elsewhere in the VBA project that prevents the code from compiling. A typo in a function call inside another procedure can also produce “Sub or Function not defined,” even if a similarly named function exists.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) or as an Excel Macro-Enabled Add-In (.xlam) if appropriate. A standard .xlsx workbook cannot store VBA code; saving a macro-enabled file as .xlsx removes the VBA project. If the code is in a separate workbook, keep that workbook open for ordinary use or set up the function as an add-in. Excel for the web can open a macro-enabled workbook, but it does not run VBA macros; use desktop Excel to execute or edit VBA.

On Excel for Mac, VBA is also available in desktop Excel, though keyboard shortcuts can depend on the keyboard and macOS settings. Option+F11 opens the Visual Basic Editor on many Mac keyboards; the Fn key may also be required. Excel for the web does not provide the VBA editor.

Resolve duplicate and confusing names

If the editor reports an ambiguous name, search the entire VBA project rather than only the current module. In the Visual Basic Editor, use Edit > Find to look for every occurrence of the identifier. Two procedures with the same name in a scope can cause a conflict, as can a procedure name that clashes with another project identifier. Rename one item to a unique name, then update code and cell formulas that refer to it.

Also distinguish a procedure name from its module name. A standard module may be named in the Properties window, and that module name is not the same thing as the function declared inside it. Giving modules descriptive names such as modCalculations and functions names such as CalculateDiscount makes references easier to inspect. If a formula name could also be interpreted as a defined name, review Formulas > Name Manager and choose a function name that is not easily confused with a workbook name.

Use a careful troubleshooting sequence

Copy the complete error message and identify whether it came from the VBA editor or a worksheet cell.
Check the function declaration and every call for matching spelling. Look closely for spaces, underscores, or an accidental extra character.
In the Visual Basic Editor, select Debug > Compile VBAProject. Fix the first compile error reported; later messages may be consequences of the first one.
Confirm the UDF is public and in a standard module, and check that its workbook is saved in a macro-capable format.
Search the project for duplicate procedure names and review defined names if Excel returns #NAME?.
Test the function in a new cell with simple inputs before using it in a larger formula. If that works, check the original formula’s arguments and references.

Compilation is useful because it checks VBA code for syntax and certain name-resolution problems; it does not guarantee that every input will produce the intended result. A function can compile successfully and still return an error for particular data. Test representative inputs, including blanks or unexpected values if the function is meant to handle them.

Security and practical limitations

Only enable macros in files from sources you trust. Macro security is a protection against code that can perform actions beyond calculating a cell result. Do not lower Excel’s security settings globally just to make one function work; instead, verify the file’s source and follow your organization’s approved process for trusted documents.

A worksheet UDF is generally intended to calculate and return a value, not to change other cells or alter Excel’s environment. If a task needs to write to cells, format a sheet, or respond to a button, a macro or event procedure may be a better fit than a function called from a cell. Before running any macro that changes workbook data, save a separate copy: VBA actions cannot be reversed with Ctrl+Z.

Frequently Asked Questions

Why does my VBA function return #NAME? in Excel?
Excel may not recognize the function name in the formula. Check spelling, place the public UDF in a standard module, confirm the code is in a macro-enabled workbook, and use desktop Excel with macros available.

Can a VBA function have spaces in its name?
No. A VBA procedure name cannot contain spaces. Use a single identifier such as GetCustomerTotal or Get_Customer_Total, and call it using exactly that spelling.

Why does VBA say Sub or Function not defined?
The called name may be misspelled, missing, private to another module, or unavailable in the current project. Search for the declaration and check the location and visibility of the procedure.

Can I use a VBA function in Excel for the web?
Excel for the web does not run VBA. A workbook can be opened there, but use desktop Excel to run or edit its VBA; do not rely on a VBA UDF to calculate in the browser.

Will renaming the VBA function fix existing formulas?
Only if the formula uses the corrected name. Renaming the procedure does not necessarily update every cell formula or other code reference, so search for the old name and replace those calls as needed.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.