VBA Functions: Basics, Examples, and How to Write Them

A VBA function is a named block of code that performs a task and returns a value. In Excel for Microsoft 365 on Windows, you can write one in the Visual Basic Editor, call it from another macro, or—if it is a suitable user-defined function—use it in a worksheet formula.

What a VBA Function Does

VBA, or Visual Basic for Applications, is the programming language built into desktop Microsoft Office apps, including Excel. A function packages instructions under a name so the same calculation or operation can be used in more than one place. For example, a function could calculate a discount, convert a measurement, or combine a set of rules into one result.

The key feature is its return value. A function sends a result back to the code or worksheet that called it. That result can be a number, text, a date, a Boolean value such as True or False, or another supported data type. Functions are useful when you need an answer from a calculation. If you instead want code to take an action—such as formatting a range or creating a new sheet—a Sub procedure is usually the better choice.

VBA functions are different from Excel’s built-in functions such as SUM and IF, although a user-defined function can be called in a cell in a similar way. A function written in VBA is part of the workbook’s VBA project, not a new built-in Excel function available automatically in every workbook.

Function and Sub: The Difference

Both Function and Sub procedures contain reusable VBA instructions, but they are designed for different jobs. A Function returns a value; a Sub performs actions and does not return a value to its caller. For instance, a function might return the total of two numbers, while a Sub might write that total into a selected cell.

Use a Function when the caller needs a result it can use in a further calculation. Use a Sub when the task is an operation, such as sending a report to a new worksheet or applying formatting. A Sub can call a Function, and a Function can call other functions. A worksheet formula can call an eligible public function, but it cannot call a Sub as though the Sub were a formula.

This distinction also helps prevent a common design mistake: trying to use a worksheet function to make changes elsewhere in the workbook. Worksheet functions are meant to calculate and return results. For actions that change workbook state, create a Sub and run it as a macro instead.

Basic Function Syntax

A typical VBA function has a name, optional arguments, a declared return type, instructions, and an assignment to the function’s own name. That last assignment supplies the returned value. This small example calculates a tax amount from a value and a rate:

Option Explicit

Public Function TaxAmount(ByVal amount As Double, ByVal rate As Double) As Double
    ' Return the calculated tax amount.
    TaxAmount = amount * rate
End Function

Public makes the procedure available outside its module; a public function in a standard module can generally be called from a worksheet. ByVal passes each argument’s value to the function so that assigning a new value to the parameter does not change the caller’s variable. Double is a numeric data type suitable for decimal values. The final As Double declares the type of the result.

With a value in A2 and a rate in B2, the worksheet formula =TaxAmount(A2,B2) calls the function and returns their product. If A2 contains 100 and B2 contains 0.08, the result is 8. The same function can be called from another VBA procedure by passing two values or variables. Argument order matters: the first supplied argument is assigned to amount, and the second to rate.

Arguments, Data Types, and Return Values

Arguments let a function work with different inputs rather than being tied to fixed values. Declare each argument with a useful name and an appropriate type. Common VBA types include Long for whole numbers, Double for decimal numbers, String for text, Boolean for True or False, and Date for dates and times. Use Variant when a value may have different types or when you need to return a special Excel error value, but do not use it automatically: more specific types make code easier to understand and can catch mistakes sooner.

A function’s declared return type should match what it actually returns. To return text, for example, declare the function As String and assign text to the function name. To return a true/false test, use As Boolean. If the function has no explicit return type, VBA treats it as a Variant, which may hide type problems.

Optional arguments are useful when most callers need the same default setting. For example, a rounding function could accept a number and an optional number of decimal places. In VBA, an optional argument must appear after required arguments. Give it a default value when its type permits one, and explain the default in a comment or procedure description so callers know what happens when they leave it out.

Choose names that describe a function’s purpose, such as TaxAmount or IsWorkday. Names cannot contain spaces, and a name that conflicts with an existing Excel or VBA name can create confusing calls. Option Explicit at the top of a module requires variables to be declared before use, helping catch misspellings that might otherwise be treated as new variables.

Writing and Calling a Function in Excel

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. You can also use Developer > Visual Basic on the ribbon. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. In the editor, choose Insert > Module, then place the function in that standard module. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep its VBA code.

On a Mac, open the Visual Basic Editor from Developer > Visual Basic. The keyboard shortcut can depend on the keyboard and function-key settings; Option+F11 or Fn+Option+F11 may be needed. The editor’s menus and the VBA language are similar, but ribbon layout and some keyboard commands differ from Windows. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use the desktop app for this work.

To use a public function in a worksheet, enter its name and arguments in a cell just as you would with a built-in function. If Excel does not recognize the name, check that the function is in a standard module, is public, and has no compile errors. Also check that macros are available in the desktop application and that the workbook was saved in a macro-enabled format. A custom function is normally available only while the workbook containing its code is open, unless you store it in a suitable add-in.

Using a Function as a Worksheet Formula

A function intended for a cell is often called a user-defined function, or UDF. A useful UDF calculates a result from its arguments without changing the workbook. Here is a function that returns an Excel division-by-zero error when the denominator is zero, rather than stopping with a VBA runtime error:

Public Function SafeRatio(ByVal numerator As Double, _
                          ByVal denominator As Double) As Variant
    ' Return a worksheet error when division is undefined.
    If denominator = 0 Then
        SafeRatio = CVErr(xlErrDiv0)
    Else
        SafeRatio = numerator / denominator
    End If
End Function

Because the function can return either a number or an Excel error value, its result type is Variant. With the numerator in A2 and denominator in B2, use =SafeRatio(A2,B2). If A2 is 12 and B2 is 3, the result is 4; if B2 is 0, the cell displays Excel’s division-by-zero error. Returning an error is often more useful than returning a misleading number such as zero.

Keep worksheet functions focused on calculations. A UDF called from a cell should not be used to edit other cells, change formatting, or perform workbook actions. Use a Sub for those tasks. Also, Excel tracks the arguments passed to a UDF for recalculation; if the function reads additional cells internally that are not among its arguments, Excel may not know that a change to those cells should trigger a recalculation. Pass required inputs explicitly where practical.

Testing, Debugging, and Common Errors

Test a new function with ordinary values, boundary cases, and unexpected inputs. A percentage function, for example, should be checked with zero, a typical value, and a value outside the expected range. Decide whether unusual inputs should produce an error, a special result, or a documented default. Clear expectations make the function safer to reuse.

In the Visual Basic Editor, use Debug > Compile VBAProject to identify syntax or compile-time problems. You can set a breakpoint by clicking beside a line or pressing F9, then run the calling procedure and step through it with F8. The Immediate window is useful for inspecting values or trying short VBA expressions. On Mac, editor shortcuts may differ, so use the menu commands if a Windows shortcut does not work.

#NAME? in a worksheet often means Excel cannot resolve the function name. Confirm the code is in the correct workbook and a standard module, and check spelling and visibility.
#VALUE! can indicate that an argument has a type or value the function does not handle. Check the cell inputs and whether the declared data types match the intended use.
A compile error usually points to malformed syntax, a missing declaration, or an invalid name. Read the highlighted line and also inspect the line immediately before it.
A runtime error occurs while the code runs, often because an assumption about a value or object was wrong. Validate inputs and use targeted error handling where recovery is appropriate.

Do not add On Error Resume Next simply to make an error disappear. It can conceal the cause and allow later code to run with incomplete data. Prefer checking likely problem conditions directly or using a specific error handler that reports or safely handles the problem.

Saving, Security, and Safe Use

VBA code is a macro, so Excel’s macro security settings apply. Files from the internet or other untrusted sources may have macros blocked or disabled. Only enable macros in workbooks you trust and understand; do not lower security settings globally just to run one file. In an organization, follow its policy for trusted locations, signed macros, and approved files.

Before running a macro that changes workbook contents, save a separate copy of the workbook. VBA macro actions generally cannot be reversed with Ctrl+Z, and running a procedure can overwrite or delete data. A calculation-only UDF normally returns a cell result, but it is still sensible to keep a backup when editing code or working with important files. Use a descriptive name, add brief comments for non-obvious logic, and test on sample data before applying a macro to a full workbook.

Frequently Asked Questions

Can I use a VBA function in an Excel cell?
Yes. A public function in a standard module can usually be entered as a worksheet formula while the workbook containing it is open in desktop Excel. The workbook must retain its VBA code, typically by being saved as an .xlsm file. Excel for the web does not run VBA.

Why does my custom function show #NAME?
Check the function name, confirm the code is in a standard module rather than only a worksheet or workbook module, and make sure it is public and has no compile errors. Also confirm that you are using desktop Excel and that the workbook containing the function is open.

Should I use a Function or a Sub?
Use a Function when you need a returned value, such as a calculated amount or a text result. Use a Sub when you want to perform an action, such as changing formatting or copying data. A Sub can call a Function when it needs that function’s result.

Can a VBA function change other cells?
A worksheet UDF should return a value rather than change other cells or workbook settings. For a task that writes to cells or alters the workbook, write a Sub and run it as a macro. Passing inputs directly to a UDF also helps Excel recalculate it when those inputs change.

Can I undo a macro with Ctrl+Z?
Usually not. Running VBA code clears Excel’s normal undo history, so save a backup copy before running a macro that changes data. If something goes wrong, close without saving only if that is safe for the rest of your work, or restore the backup.

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.