If Excel VBA reports Sub or Function not defined, it cannot find the procedure named in the highlighted line. Check the spelling first, then confirm the function exists in the project, is visible from the calling code, and does not depend on a missing reference or unavailable add-in.
This is usually a compile-time error, not a problem with the calculation inside the function. The fix is to make the procedure name and its location accessible to the code that calls it, or to restore the library that supplies it.
What the VBA error means
VBA resolves procedure names while compiling a project. When it encounters a call such as CalculateTotal, it searches the current project and available libraries for a matching Sub or Function. If it cannot find a callable procedure, compilation stops and the editor highlights the name it could not resolve.
The wording can be confusing: a Sub can trigger the same message as a Function. Both are procedures. A Function returns a value; a Sub performs an action and does not return a value. The message does not necessarily mean that Excel lacks a built-in worksheet function, and it does not tell you that the calculation itself is wrong.
For example, if a line calls CalculateTotal but the procedure is actually named CalculateTotals, VBA treats those as different names. Likewise, a valid procedure can be present in the workbook but inaccessible because it is Private, placed in the wrong kind of module, or located in another project that is not available.
Start with the highlighted name
When the error appears, choose OK and note the highlighted word or phrase. Avoid editing unrelated lines until you know what VBA is trying to resolve. Then use this short diagnostic sequence:
- Compare the highlighted name with the procedure declaration, including spelling and any plural ending or suffix.
- Search the current VBA project for the declaration. In the Visual Basic Editor, use Edit > Find and search for the name.
- Check whether the procedure is declared Public or Private and which module contains it.
- If the name belongs to an external library or add-in, inspect the project references and confirm that the dependency is installed.
- Run the compile command again after making one targeted correction.
On Windows, open the Visual Basic Editor with Alt+F11, then use Debug > Compile VBAProject. On a Mac, open the editor through Tools > Macro > Visual Basic Editor; the editor’s Debug menu includes the compile command. Menu presentation and keyboard shortcuts can differ by Mac keyboard, so use the menu if the shortcut is inconvenient.
Common causes and practical fixes
- Misspelling or renamed procedure: Correct either the call or the procedure declaration so both names match. Use Edit > Find to check whether other call sites still use an old name after a rename.
- Procedure is Private: A procedure declared Private can only be called from within its containing module. If another module needs it, make it Public where appropriate, or move the call into the same module.
- Code is in a different project: A procedure in another workbook’s VBA project is not automatically available as if it were in the current project. The other workbook may need to be open, its project referenced, or its code exposed through an intentional interface.
- Wrong module type: A general-purpose helper is easiest to call when stored in a standard module. A procedure in a worksheet or ThisWorkbook module belongs to that object and may require an object-qualified call; it is not always visible as an ordinary global procedure.
- Missing add-in or library: A call may rely on code supplied by an add-in, another workbook, or a referenced type library. Restore the correct dependency or remove or replace the call if the dependency is no longer needed.
- Name collision: A module, variable, property, or procedure with a confusingly similar name can make code hard to resolve and maintain. Rename ambiguous items and use a distinctive procedure name.
VBA does not require parentheses in every kind of procedure call, so punctuation is not usually the cause of this specific message. However, a malformed call can produce a different syntax or argument error. Fix the unresolved name first, then address any separate messages that appear during compilation.
Make a function callable from another module
For a small reusable function, put it in a standard module. In the Visual Basic Editor, choose Insert > Module, then define a Public Function. Here is a complete example with a small test macro:
Option Explicit
' Returns the sum of two values.
Public Function AddNumbers(ByVal firstValue As Double, ByVal secondValue As Double) As Double
AddNumbers = firstValue + secondValue
End Function
' Demonstrates a call from the same VBA project.
Public Sub TestAddNumbers()
Dim total As Double
total = AddNumbers(2, 3)
Debug.Print total
End Sub
The function and test macro are both in a standard module, and the names match exactly. The declared variables and parameters make the expected data types clear. With the function in a standard module, a worksheet can also call it as a user-defined function when the workbook is macro-enabled and macros are permitted. Assume the function is in the workbook and cell A1 contains 4; enter =AddNumbers(A1,3) in a worksheet cell and the result is 7.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even when a sample macro seems harmless. Save code that must persist in a macro-enabled workbook such as an .xlsm file; a regular .xlsx workbook cannot store VBA project code.
Distinguish VBA functions from worksheet functions
Excel has worksheet functions such as SUM, but not every worksheet function is a VBA language function that can be called by writing its name alone in a macro. From VBA, Excel worksheet functions are generally accessed through the WorksheetFunction object, for example Application.WorksheetFunction.Sum. This distinction matters when the highlighted word is a familiar function name: the name may be valid in a cell formula but not in that particular VBA context.
Conversely, a custom VBA Function does not automatically become a worksheet formula. For a worksheet user-defined function, place a Public Function in a standard module and give it arguments and a return value that Excel can use. Avoid making the function Private or embedding it only in a worksheet object module if you expect to call it by its simple name from a cell.
Worksheet formulas are recalculated by Excel’s calculation engine, while VBA procedures run as code. A macro that updates cells is not interchangeable with a worksheet function, and a worksheet function should not be used for actions such as changing formatting or deleting sheets. Choosing the right kind of procedure prevents confusing follow-on errors.
Check references and add-ins
A missing reference can break code that uses an external library, even when the procedure name is spelled correctly. In the Visual Basic Editor, open Tools > References and look for an entry marked MISSING. This indicates that the project expects a library that Excel cannot currently locate, often because a component was removed, moved, or is a different version on this computer.
Do not uncheck a missing reference blindly. First determine what code depends on it. If the project uses a class, constant, or method from that library, removing the reference may simply cause additional compile errors. Restore the required library or update the code to use an available replacement. For a workbook shared between Windows and Mac, check whether the dependency exists on both platforms; some external components are platform-specific.
An add-in may also provide a procedure that a workbook calls. Confirm that the add-in is installed and loaded, and that the procedure is exposed in a way the workbook can call. If the workbook was obtained from someone else, ask which add-in or companion file it expects rather than downloading an unknown replacement.
Compile the whole project and interpret the next error
Use Debug > Compile VBAProject after the change. Compilation can reveal another unresolved name elsewhere in the project; fixing one error does not guarantee there are no others. If Compile is unavailable or appears disabled, the project may already be compiled or there may be no pending changes for VBA to check. Make a code edit and try again if appropriate.
With Option Explicit at the top of a module, VBA requires variables to be declared. That setting is good practice because a typo in a variable name can otherwise create an unintended variable. But an undeclared variable normally produces a different compile message, such as Variable not defined, rather than Sub or Function not defined. Read the exact error text instead of treating every compile failure as the same problem.
After compilation succeeds, run the code again. A later error may be a runtime problem, such as invalid data, a missing worksheet, or an unsupported operation. Runtime errors occur while code is executing; the unresolved-procedure message occurs because VBA cannot compile a call. These require different investigations.
Excel for the web, Mac, and macro security
Excel for the web can open and edit many workbooks containing VBA, but it cannot create, run, or edit VBA macros. Open the workbook in the desktop Excel application to use the Visual Basic Editor. A browser-based workbook cannot fix a missing procedure by running the macro there.
Excel for Windows and Excel for Mac both support VBA, but available libraries, add-ins, and controls can differ. A project that compiles on one platform may refer to a Windows-only component and fail on a Mac. Check the highlighted call and References list on the computer where the failure occurs, and avoid assuming every external dependency travels with the workbook.
Macro security is separate from the compile error. If Excel blocks macros, the code may not run, but that does not itself mean a procedure is undefined. Only enable macros for files and sources you trust; do not weaken security settings just to bypass an error. If a trusted workbook is blocked, follow your organization’s approved process for opening it.
When to rewrite or remove the call
If the original procedure or add-in is gone and cannot be restored, identify what the call was supposed to do before replacing it. Search for its declaration, comments, documentation, or other call sites. A function that returns a numeric result is not a safe substitute for a Sub that performs a multi-step operation, even if both names seem related.
For a simple calculation, a normal worksheet formula may be easier to maintain than custom VBA. For repetitive actions, a macro may still be suitable, but document its inputs and effects. When code belongs to a third-party add-in, replacing it with an improvised procedure can change results, so verify expected behavior with the add-in’s documentation or provider.
Frequently Asked Questions
Why does VBA say Sub or Function not defined when the function is there?
It may be declared Private, stored in a worksheet or workbook object module, or located in a different project that is not accessible. Check the declaration and module location, and confirm that the calling code refers to the right project.
Is Function not defined the same as Variable not defined?
No. Sub or Function not defined means VBA cannot resolve a procedure call. Variable not defined usually means a variable name is undeclared, especially when Option Explicit is in use. The exact highlighted name and message help distinguish them.
How do I call a VBA function from an Excel cell?
Put a Public Function in a standard module in a macro-enabled workbook, then call it in a cell with its name and arguments. For example, with the AddNumbers function above, cell A1 holding 4, and the function in the same workbook, =AddNumbers(A1,3) returns 7. The workbook must be opened in desktop Excel with macros allowed.
Can Excel for the web run VBA macros?
No. Excel for the web does not run VBA. Use desktop Excel for Windows or Mac to edit and run VBA code; a browser session may still display workbook data produced by an earlier macro run.
Should I remove a MISSING reference?
Only after checking what depends on it. If the project uses that library, restore it or update the dependent code. Removing it without investigation can replace one compile error with several others.
Related
- Excel Pasting: Stop Tab-Delimited Text Splitting on Paste
- Excel Unable to Merge Cells? Causes, Fixes, and Alternatives
- Excel Text to Columns Wizard: Split Data Easily
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Loop Collection: For Each and ExamplesTo loop through a VBA Collection in Excel, use For Each to visit every item, or use a…
- Macro Recorder With Mouse Tracking: How It WorksYes, macro recorders with mouse tracking can capture clicks and, in some tools, pointer movement, scrolling, and keystrokes…
- VBA Sort Range by Column: Practical Examples and TipsTo sort a range by one column in Excel VBA, call the range’s Sort method and specify the…
- VBA Delete Blank Rows in Excel: Safe MethodsYes. In Excel for Microsoft 365 on Windows, a VBA macro can scan a chosen range from bottom…
- How to Find VBA Code in Excel: Open and Search MacrosTo find VBA code in Excel for Microsoft 365 on Windows, open the workbook in the desktop app,…
- Excel VBA AutoFilter Tables: Setup, Code, and Common FixesTo filter an Excel table with VBA, use the table’s Range.AutoFilter method and specify the column’s position within…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.