Define a Function in Excel VBA: Syntax and Examples

VBA & macros · Updated November 18, 2026

To define a function in Excel VBA, write a Function procedure in a standard VBA module, give it a name and any required inputs, assign a result to that function name, and finish with End Function. A public function in a standard module can then be called from an Excel worksheet formula, much like a built-in function.

What a VBA function is

A VBA function, also called a user-defined function (UDF), is a procedure that calculates and returns a value. You can write one when a calculation or transformation is not conveniently expressed with Excel’s built-in functions, or when you want to reuse the same calculation in multiple places. For example, a function might convert a measurement, apply a business rule, or combine several steps into one named operation.

In VBA, a function has a declared name and can accept arguments, which are the values or cell references supplied when it is used. Its return value is assigned to the function’s own name. This is different from a Sub procedure: a Sub performs actions but does not return a value to a worksheet cell. Functions are therefore a natural fit for calculations, while Subs are commonly used for tasks such as formatting cells or creating reports.

Function syntax and the return value

The general structure is Public Function FunctionName(argument As DataType) As ReturnType, followed by the calculation and an assignment to FunctionName, then End Function. The Public keyword makes the procedure available outside its module; a function declared Private is restricted to its module. The argument and return types tell VBA what kind of values the function expects and returns.

Here is a small example that converts kilometres to miles. Before using the worksheet formula below, put the number of kilometres in cell A2; with 10 in A2, the formula returns 6.21371 miles.

Public Function KmToMiles(ByVal Kilometres As Double) As Double
    ' Convert kilometres to miles.
    KmToMiles = Kilometres * 0.621371
End Function

The function name on the left side of the assignment is the return value. If that assignment is missing, VBA does not automatically return the last calculated expression: the result will be the default value for the declared return type. In this example, Double is used for both the input and output because converted measurements can contain decimal places. ByVal means VBA receives the argument’s value rather than a reference that lets the function change the caller’s variable.

With kilometres in A2, the worksheet formula is =KmToMiles(A2). The function can also be called from another VBA procedure, passing a numeric value as its argument.

Create a function in the Visual Basic Editor

  1. Open the workbook in Excel for Microsoft 365 on Windows. Press Alt+F11 to open the Visual Basic Editor. You can also use Developer > Visual Basic if the Developer tab is shown.
  2. In the editor, select the workbook project in Project Explorer. Choose Insert > Module. A standard module is the usual location for a function intended for worksheet use.
  3. Paste or type the function into the module’s code window. Use a unique function name and declare the input and return types.
  4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains VBA code, then return to the worksheet.
  5. Enter the function name in a cell formula, supplying the required arguments. If Excel does not recognize it immediately, check the troubleshooting section below.

On Excel for Mac, open the Visual Basic Editor from Developer > Visual Basic; depending on the keyboard and function-key settings, the Windows shortcut may not apply. The VBA editor is not available in Excel for the web. A workbook can be stored and opened there, but VBA code cannot be created or run in the browser; use desktop Excel to work with VBA functions.

Use a user-defined function in a worksheet

Once the workbook containing a public function is open in desktop Excel, a worksheet formula can call it by name. The function’s arguments correspond to its declared inputs, and Excel recalculates the formula when relevant inputs change. You can type the formula directly into a cell or use the function name in a larger formula, provided the types and number of arguments are appropriate.

For the example function, with kilometres in A2, enter =KmToMiles(A2). If A2 contains 10, the calculation is 10 × 0.621371, so the returned value is 6.21371. The cell may display fewer decimal places depending on its number format, even though the underlying result retains precision. Formatting a result cell changes how the value appears, not the value returned by the function.

Worksheet UDFs are best treated as calculations: give them inputs and have them return results. They generally should not be used to change other cells, alter formatting, or perform unrelated workbook actions. Excel controls when a formula is recalculated, and a worksheet function that tries to make changes elsewhere can behave unpredictably or produce errors. Use a Sub or another appropriate automation approach for actions rather than a cell formula.

Choosing arguments, data types, and visibility

Declare types deliberately. Common choices include Double for decimal numbers, Long for whole-number counters and indexes, String for text, and Boolean for True/False values. A Variant can hold different kinds of values, but using it everywhere can make a function less clear and may require extra checks. Choose a return type that matches the intended result and consider what should happen when a user supplies blank, text, or out-of-range input.

Arguments can be required or optional. A required argument must be provided in the worksheet call. VBA also supports optional arguments, but a function should define a sensible default and handle omitted values explicitly. If a function accepts a range, its parameter is often declared as a Range; the code must then decide whether to process one cell or multiple cells. A simple scalar function such as the conversion example is easier to understand because it accepts one number and returns one number.

For worksheet access, put the function in a standard module and declare it Public (or omit the visibility keyword, since Public is the usual default in a standard module). A function placed in a worksheet’s code module or ThisWorkbook may be callable from VBA in some contexts, but it is not the recommended location for a regular worksheet UDF. Avoid names that conflict with built-in Excel functions or defined names, since those can make formulas ambiguous.

Save format, macros, and workbook safety

A workbook with VBA code should be saved in a macro-enabled format such as .xlsm. Saving as a standard .xlsx file removes the VBA project, so Excel may warn that the code will be lost. If the function must be available in multiple workbooks, developers sometimes package reusable VBA code in an Excel add-in (.xlam), but a normal workbook is simpler for a function used only in that file.

Excel’s macro security controls whether VBA code is allowed to run. Only enable macros in files from sources you trust, and follow your organization’s security policies. A worksheet formula using a VBA UDF may show an error or fail to calculate if the workbook’s macros are disabled. Sharing the file with someone who cannot or will not enable macros also affects whether the function will work for them.

Before running VBA code, save a separate copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a procedure that changes workbook data may not be recoverable through Excel’s undo history. The conversion UDF above only calculates a value, but the same caution is important when using other code that edits cells, sheets, or files.

Common problems and how to diagnose them

  • #NAME? in the cell: Check the function spelling, confirm the workbook containing the function is open, and make sure the code is in a standard module. Also check whether macros are disabled or the file was saved as .xlsx and lost its VBA project.
  • Compile error in the editor: Check that the procedure begins with Function, ends with End Function, and has balanced parentheses and valid type declarations. A misspelled variable or incompatible assignment can also cause a compile error.
  • Unexpected result or an error value: Verify the inputs and data types. A function expecting a number may not handle text or an error value in a referenced cell as intended. Add validation or explicit error handling if those inputs are possible.
  • Function missing from the Insert Function list: A function can still be callable by typing its name even if it is not presented as expected in the dialog. Check its scope, module location, and whether the workbook is macro-enabled.
  • Result does not update: Confirm that the formula references the cells that actually changed and check Excel’s calculation mode. A UDF that reads data indirectly, such as from another range without declaring it as an argument, may not recalculate when that data changes. Prefer passing dependencies as arguments.

Clear function names and explicit inputs make UDFs easier to troubleshoot. If a function depends on a value, pass that value into the function rather than having the code search for it elsewhere. That makes the relationship visible in the worksheet formula and helps Excel determine when the result needs recalculation.

When a VBA function is the right choice

Before writing VBA, check whether a built-in Excel function or a formula using existing functions can solve the problem. Built-in formulas are usually easier to share, do not require enabling macros, and work in Excel for the web more readily than VBA. A VBA UDF can be useful when a calculation is too cumbersome to maintain as a formula, needs to be reused consistently, or relies on logic that is more readable in code.

Power Query is often a better fit for repeatable data import and transformation, while a Sub macro is more appropriate for automating actions such as preparing a report or updating a range. These tools solve different problems: a UDF returns a value to a formula, Power Query transforms data during a refresh, and a Sub performs a sequence of actions. Choosing the simplest tool that meets the need makes a workbook easier to maintain and share.

Frequently Asked Questions

How do I define a function in Excel VBA?

Open the Visual Basic Editor, insert a standard module, write a Function procedure with any arguments and a return type, assign the result to the function name, and close it with End Function.

Where should I put a VBA function for a worksheet formula?

Put a public function in a standard module in the workbook’s VBA project. Save the workbook in a macro-enabled format such as .xlsm so the code is retained.

Can I use a VBA function in Excel for the web?

No. Excel for the web does not create or run VBA code. Open the workbook in desktop Excel to use its VBA function; a formula that depends on that function will not be a browser-compatible replacement.

What is the difference between a VBA Function and a Sub?

A Function returns a value and can be used in a worksheet formula when set up as a UDF. A Sub does not return a worksheet value; it is used to carry out actions, such as editing cells or running a process.

Why does my VBA function return #NAME?

Check the name and spelling, module placement, workbook file format, and macro settings. The function must be available in the open workbook, and its VBA project must not have been removed by saving as an .xlsx file.

Related

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.