Excel VBA Function Tutorial: Write and Use Functions

An Excel VBA function is a procedure that calculates a value and returns it to the code that called it; you can also use a public function from a standard module in a worksheet formula. To make one, open the VBA editor, insert a standard module, write a Function procedure, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

What an Excel VBA function does

Excel already includes hundreds of worksheet functions, such as SUM and IF. A VBA function, also called a user-defined function or UDF, is useful when you need a reusable calculation or transformation that is difficult to express with existing formulas. You write its logic once, then call it from VBA code or, with some limits, from a worksheet cell.

A function differs from a Sub procedure. A Sub performs actions, such as formatting cells or copying data, but does not return a value to its caller. A Function calculates and returns a value by assigning that value to the function’s own name. For example, assigning 68 to CelsiusToFahrenheit makes 68 the function’s result.

VBA is not the same thing as an Excel formula. A formula is entered in a worksheet cell and recalculates as its inputs change. VBA is code stored in the workbook or another VBA project. A worksheet UDF connects the two: the cell formula calls the function, and Excel displays the returned value.

Open the editor and insert a module

These steps apply to Excel for Microsoft 365 on Windows. On Windows, press Alt+F11 to open the Visual Basic Editor. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, select Developer, and choose OK. You can also open the editor from Developer > Visual Basic.

In the editor, choose Insert > Module. A standard module is the usual location for a worksheet function because it makes the procedure available throughout the VBA project. A function placed inside a worksheet code module or ThisWorkbook may have different visibility and is generally not the best starting point for a UDF.

Type Option Explicit at the top of the module. It makes VBA require variables to be declared, helping catch spelling mistakes.
Put the Function procedure below that line.
Return to Excel with Alt+F11, or use the editor’s close or switch-window controls.
Save the file as an .xlsm workbook so it can retain VBA code.

On Mac, Excel also includes a VBA editor; use Developer > Visual Basic if the Developer tab is available. Option+F11 is a common editor shortcut, although the Fn key or keyboard settings can affect function-key behavior. Excel for the web does not provide the VBA editor or run VBA macros. A workbook containing VBA can be stored or opened there, but use desktop Excel to create or execute its VBA code.

Understand the structure of a Function procedure

A procedure begins with Function and ends with End Function. Its declaration gives the name, any inputs, and the type of value it returns. In this example, ByVal means the function receives each argument as a value rather than being allowed to change the caller’s variable. Declaring types makes the intended inputs and output clearer.

Option Explicit

Public Function CelsiusToFahrenheit(ByVal Celsius As Double) As Double
    ' Convert a Celsius temperature to Fahrenheit.
    CelsiusToFahrenheit = Celsius * 9 / 5 + 32
End Function

Here, Celsius is an argument, Double is its data type, and the final As Double declares the return type. The line beginning CelsiusToFahrenheit = assigns the result. Leaving out that assignment does not make VBA infer the intended answer; the function may return the default value for its type instead.

Public makes the function available outside the module, including to worksheet formulas when the function is in a standard module. Private limits its availability to the module. If a function is meant for use in cells, give it a distinctive name that does not conflict with a built-in Excel function or another procedure in the project.

Call the function from a worksheet

After entering the code and returning to the worksheet, call the function as you would a worksheet function. With the Celsius temperature 20 in cell A2, enter this formula in another cell:

=CelsiusToFahrenheit(A2)

The result is 68, because 20 × 9 ÷ 5 + 32 equals 68. The formula passes the value in A2 to the Celsius argument, and Excel displays the value returned by the VBA function. You can also enter a direct value, but referring to a cell is more useful when the input may change.

Excel’s formula separator is normally a comma in Microsoft 365 installations configured for English-style formula syntax, though regional settings can affect separators. This example has one argument, so no separator is needed. If Excel displays a function name as unrecognized, first confirm that the code is in a standard module, is Public, and the workbook’s macros are enabled. Also check that the formula uses the exact procedure name.

Choose arguments, data types, and return values

The argument list defines what a caller must supply. A function can take no arguments, one argument, or several. Use a type that matches the data: Long for whole-number counts, Double for values that may include decimals, String for text, and Boolean for True or False. Excel worksheet values can also be blank, errors, dates, or mixed types, so think through what your function should do when it receives unexpected input.

VBA permits Variant, a flexible type that can hold different kinds of values. That flexibility can help when a worksheet function must accept varied cell contents, but it also means the code should check what it receives before performing an operation. A strongly typed argument such as Double is simpler for a narrowly defined calculation, but a text value or worksheet error passed to it may cause a type mismatch.

Functions can have optional arguments, but beginners should start with required inputs and a clear return type. If a calculation needs multiple results, consider returning a clearly structured value or using a Sub to write results into cells instead. A worksheet UDF is designed to return a value, not to serve as a general-purpose macro that edits the workbook.

Know what worksheet functions can and cannot do

A UDF called from a worksheet cell should calculate and return a result. It is not a replacement for a Sub macro: worksheet functions are not intended to edit other cells, change formatting, or perform workbook actions as a side effect of recalculation. Use a Sub for tasks such as writing a report, applying formats, or moving data.

Excel may recalculate a UDF more than once, for example when a dependent cell changes or when calculation is set to automatic. This is another reason to keep worksheet functions predictable: the same inputs should produce the same output, and the function should not depend on an action happening exactly once. Functions that rely on changing external conditions may not refresh when expected unless Excel has a reason to recalculate them.

For ordinary calculations, prefer a built-in worksheet function when it already does the job. Built-ins are easier for other people to recognize and do not require macro permissions. VBA is especially useful when the logic is specific to a workbook, repeated in many places, or awkward to maintain as a long nested formula. A UDF does not automatically make a calculation faster; complex code, repeated calls, or inefficient loops can make a workbook slower.

Test and troubleshoot a new function

Test with a small set of known inputs before using a new UDF throughout a workbook. For the temperature example, 0 Celsius should return 32, and 100 should return 212. These reference points help reveal a wrong operator, an incorrect return assignment, or a formula that is calling a different procedure than intended.

If Excel shows #NAME?, check the spelling of the function name, its Public declaration, and whether the code is in a standard module.
If Excel shows #VALUE! or another error, inspect the input cells and the argument types. A cell containing text or an error may not be valid input for a numeric argument.
If the function appears not to update, check the referenced cells and Excel’s calculation mode. Press F9 on Windows to recalculate; on Mac, use Formulas > Calculate Now or the available recalculation shortcut for that Excel configuration.
If the code does not compile, use Debug > Compile VBAProject in the editor and read the highlighted line. Option Explicit also helps reveal undeclared or misspelled variable names.

For line-by-line inspection, click inside the procedure and use F8 in the VBA editor to step through code on Windows. You can hover over variables while paused to inspect their values. On Mac, the editor provides debugging tools too, though keyboard shortcuts can differ by keyboard and Excel version.

Save safely and handle macros with care

Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) when you need to preserve VBA in a workbook. Saving as .xlsx removes the VBA project. The workbook may also need to be reopened or recalculated after code changes before worksheet results visibly refresh.

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a macro that overwrites cells or deletes data may be difficult to reverse. This caution applies even when a function itself only returns a value; a workbook may contain other macros that do change data.

Only enable macros in files from sources you trust. Macros can run code with access to workbook data and, depending on the code and environment, interact with other resources. Follow your organization’s security policy rather than lowering Excel’s macro security settings to make an unfamiliar file work. If you distribute a workbook with a UDF, tell recipients that desktop Excel and macro permission may be required for the function to calculate.

VBA functions compared with alternatives

A normal worksheet formula is the simplest choice when the logic is short and built-in functions can express it. It updates naturally when referenced cells change, and recipients do not need to enable VBA. Excel’s newer functions, including LET, FILTER, and XLOOKUP, can replace some long formulas, but availability depends on the Excel version; these functions are available in Microsoft 365 or Excel 2021 and later.

Power Query is often a better fit for repeatable importing, cleaning, and reshaping of data than a worksheet UDF. A Sub macro is the better VBA choice when the goal is to perform an action rather than return a value. Office Scripts may suit some automation tasks in supported Microsoft 365 environments, but they are not VBA and do not turn into worksheet functions. Choose the tool based on whether the task is a cell calculation, a data-preparation process, or an action in the workbook.

Frequently Asked Questions

Can I use a VBA function in any Excel cell?

You can call a suitable Public function from a worksheet cell when it is in a standard module in a workbook or add-in and macros are available. It will not run as a VBA function in Excel for the web, and an external workbook or add-in may need to be open for its function to be available.

Why does my VBA function return #NAME?

Excel usually cannot resolve the function name. Check spelling, make sure the procedure is Public and stored in a standard module, confirm the workbook containing it is open, and verify that macros have not been disabled by security settings.

Can a VBA function change another cell?

A worksheet UDF should return a value rather than edit other cells or change workbook state. Use a Sub macro for those actions. Keeping the two roles separate also prevents confusing results when Excel recalculates a formula.

Do VBA functions work in Excel for Mac?

Excel for Mac supports VBA and the Visual Basic Editor, but some Windows-specific features and interfaces are not available on Mac. Test code that relies on platform-specific components, and use the editor’s Developer menu if Windows keyboard shortcuts do not apply.

Can Excel for the web run a VBA function?

No. Excel for the web cannot create, edit, or run VBA code. Use Excel desktop for Windows or Mac to calculate a VBA-based worksheet function; the web version may show values saved in the workbook without executing the macro code.

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.