If an Excel VBA function is not showing up, first check that it is a public function in a standard module in a macro-enabled workbook, then confirm that macros are enabled. Also check where you are looking: the Developer tab’s Macros list shows runnable Sub procedures, not worksheet functions.
Where a VBA function should appear
Excel has several places where people expect to find VBA code, and they do not all list the same things. A worksheet function, also called a user-defined function (UDF), is entered in a cell like a built-in formula. You can type its name directly, or look for it in the Insert Function window, opened with the Formulas tab’s Insert Function button or the fx button beside the formula bar. Depending on the Excel build and function setup, it may be listed under the User Defined category.
By contrast, Developer > Macros lists runnable procedures declared with Sub, such as a procedure that formats a report. It does not list a normal worksheet function declared with Function. So a function missing from that dialog is usually behaving as expected. If the goal is to calculate a value in a cell, enter the function as a formula instead.
Typing the name directly is a useful check. For example, after creating a function named AddTax, type its name into a worksheet formula. If Excel accepts the formula and calculates a result, the function works even if it is not easy to find in a menu.
Put the function in a standard module
For a typical worksheet UDF, place the code in a standard module in the workbook that will use it. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, select the workbook in Project Explorer, and choose Insert > Module. If Project Explorer is hidden, press Ctrl+R in the editor. Paste the function into the new module, not into a worksheet’s code window or the ThisWorkbook object.
Here is a small example. It returns a total after applying a rate supplied as a second argument:
Public Function AddTax(ByVal amount As Double, ByVal rate As Double) As Double
' Return the amount including the supplied rate.
AddTax = amount * (1 + rate)
End Function
With the code in a standard module in the open workbook, enter =AddTax(100,8%) in a worksheet cell. The result is 108. Save a backup copy of the workbook before running macros or changing VBA; macro actions cannot be undone with Ctrl+Z. This example is a worksheet function, so Excel recalculates it as a formula rather than running it from the Macros dialog.
Use a clear name that starts with a letter and contains no spaces. Make sure the procedure is not declared Private, and check that the module does not have a setting that hides its procedures from other VBA projects if you intend to call it from elsewhere. A function in a worksheet or class module can have different visibility and call requirements; a standard module avoids many of those complications.
Check the declaration and return value
A worksheet UDF should be declared with Function, not Sub. It needs to be available publicly, accept arguments that match what the formula supplies, and assign a value to its own function name. In the example, AddTax = amount * (1 + rate) is the return assignment. If that line is missing or never reached, Excel may return an empty or incorrect result even though the function name is recognized.
Check that the name in the cell formula matches the declaration exactly. VBA is generally not case-sensitive, but spelling still matters. Also check the number and order of arguments: the example requires both an amount and a rate. Calling it with only one value will not match the function signature. If you change the code, return to Excel and recalculate; pressing F9 recalculates open workbooks. On a Mac, use Command+Option+F9 for a full calculation, subject to the keyboard’s function-key settings.
Compile the VBA project to catch syntax and reference errors. In the Visual Basic Editor, choose Debug > Compile VBAProject. If the command is unavailable, the project may already compile without an error, or the editor may be focused on another project. Fix any reported compile error, including errors in other modules: a compile problem elsewhere can interfere with the project’s VBA code.
Confirm the workbook format and macro permissions
A workbook saved as .xlsx cannot store VBA code. If you save a workbook containing macros in that format, Excel warns that the VBA project will be removed if you continue. Save the file as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As, then close and reopen that saved copy if needed. An Excel add-in containing VBA typically uses the .xlam format.
Excel may also block code because of its security settings. On Windows, inspect File > Options > Trust Center > Trust Center Settings > Macro Settings. Choose a setting appropriate to your organization and only enable macros in files you trust; enabling all macros globally is not a safe fix. Files downloaded from the internet may receive additional blocking, and workplace policy can override user settings. If the workbook is from a trusted source but remains blocked, ask your administrator about the approved process rather than bypassing the policy.
Excel for the web does not run VBA macros. It can open and preserve an Excel file that contains VBA, but a VBA UDF will not calculate there as it does in the desktop app. Open the workbook in desktop Excel to use the VBA function. Excel for Mac supports VBA, but security controls and some VBA features differ from Windows, especially code that depends on Windows-only components.
Find the function in Excel for Windows or Mac
On Windows, use the following sequence to check the common discovery paths:
- Open the workbook containing the code in desktop Excel and confirm that it is saved as
.xlsmor is loaded as an add-in. - Press Alt+F11, locate the workbook in Project Explorer, and confirm the function is in a standard module and declared Public.
- Return to a worksheet and type
=AddTax(100,8%)to test the function directly. - Use Formulas > Insert Function and look under User Defined if you want to browse for it.
- If Excel shows a security warning or an error, resolve that issue before treating the missing menu entry as a code problem.
On Mac, Option+F11 opens the Visual Basic Editor on many keyboards; if the function keys control hardware settings, you may need Fn as well. You can also open the editor from Excel’s Tools > Macro menu. The Insert Function button is available in desktop Excel for Mac, though exact ribbon layout and security preferences can vary by release. The same basic requirement applies: use desktop Excel, keep the code in a suitable module, and enable only trusted macros.
Common errors and what they mean
If a cell displays #NAME?, Excel may not recognize the function name. Check for a typo, confirm that the workbook containing the code is open or that its add-in is loaded, and verify that macros are available in the desktop app. If Excel recognizes the function but displays another error, inspect the arguments and the code’s return logic. A function that returns a value of the wrong type can also produce unexpected results.
Functions stored in another workbook usually depend on that workbook being open, unless the VBA code is packaged and loaded as an add-in. If you use the function in many workbooks, an .xlam add-in can make it available across Excel sessions. To install one in Windows Excel, use File > Options > Add-ins, select Excel Add-ins in the Manage box, choose Go, and browse to the add-in file. If the add-in is not checked or its location is unavailable, its function may disappear from formulas after Excel restarts.
Another source of confusion is Excel’s formula autocomplete. A custom function may not appear in suggestions in the same way as a built-in function, and suggestions can depend on the version and context. Autocomplete is not a definitive test. Try the full formula in a cell and use Insert Function or the VBA editor to verify the code and arguments.
When a VBA function is the wrong tool
VBA UDFs are useful for custom calculations, but they have trade-offs. They require desktop Excel with macros permitted, and a recipient may not be able to calculate the function in Excel for the web or in another spreadsheet application. A VBA worksheet function should calculate and return a value; it is not a suitable way to change other cells, format a range, or perform a sequence of actions. Those jobs belong in a macro procedure, generally a Sub.
If a built-in Excel function can do the job, it is usually easier to share a workbook without VBA. For automation in Microsoft 365, Office Scripts may be an option where the account and platform support them, but Office Scripts do not serve as drop-in VBA worksheet functions. Choose based on where the workbook will run and whether users can trust and enable its code. For a calculation that must work in a cell across desktop and web, prefer a normal Excel formula when practical.
Frequently Asked Questions
Why is my VBA function not in the Developer Macros list?
The Macros list is for runnable Sub procedures, not worksheet Function procedures. Enter the function name in a worksheet formula, or find it through Formulas > Insert Function.
Why does my custom function show #NAME? in Excel?
Check the spelling and arguments, make sure the workbook or add-in containing the function is open and enabled, and confirm you are using desktop Excel with macros permitted. Excel for the web cannot run VBA UDFs.
Does a VBA function have to be in a standard module?
For the usual worksheet-function setup, yes: put a public function in a standard module. Code in worksheet or class modules can have different scope and may not be discoverable like a standard-module UDF.
Can I use a VBA function in an .xlsx file?
No. The .xlsx format does not retain VBA code. Save the workbook as .xlsm, or distribute the code as a suitable add-in, such as an .xlam file.
Can I use my VBA function in Excel for the web?
No. Excel for the web does not execute VBA. Open the workbook in desktop Excel to calculate the UDF, or replace it with a formula or another supported approach if web use is required.
Related
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
- Excel Text to Columns: Can It Split Capital Letters?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Assign a Macro to a Toggle Button in Excel 365In Excel for Microsoft 365 on Windows, an ActiveX Toggle Button runs VBA from its Click event: insert…
- Excel VBA Function Arguments: Syntax and ExamplesIn Excel VBA, function arguments are the values or variables you pass to a function so it can…
- Enum in Excel: Create Fixed Lists and VBA EnumsExcel has no native worksheet data type called an enum. To create enum-like choices in a cell, use…
- VBA Pivot Table Ranges: How to Set and Resize ThemIn VBA, a PivotTable’s displayed range is read with PivotTable.TableRange2, while its source data range is set through…
- VBA Create New Workbook: Code, Options, and Tips in ExcelTo create a new Excel workbook with VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates a…
- Excel VBA Delete Row: Methods, Examples, and Best PracticesTo delete a worksheet row with Excel VBA, reference the worksheet and call its Rows collection’s Delete method—for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.