VBA Functions: What They Are and How to Use Them

A function in VBA is a named procedure that performs a task and returns a value. You can call one from another VBA procedure or, when it is written as a suitable public function in a standard module, use it in an Excel 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 you can reuse them without copying the same logic into multiple places. It can accept information called arguments, work with that information, and provide one result to its caller.

For example, a function could calculate a discount-adjusted price, convert text to a standard format, or return the number of days between two dates. The value it returns might be a number, text, a date, a Boolean value such as True or False, or an object. Functions are useful when a task has a clear result that another part of a program or a worksheet can use.

In Excel, the word “function” can also mean a built-in worksheet function such as SUM. A VBA function is a procedure you write in the Visual Basic Editor. It may be called from a cell as a custom function, but it is not automatically the same thing as a built-in Excel function.

How a function is structured

A VBA function begins with Function and ends with End Function. Its declaration gives the procedure a name, lists any arguments, and can specify the type of value it returns. Inside the procedure, VBA runs the statements in order.

Public Function NetAfterDiscount(ByVal price As Currency, ByVal discountRate As Double) As Currency
    ' Return the price after applying a decimal discount rate.
    NetAfterDiscount = price * (1 - discountRate)
End Function

In this example, NetAfterDiscount is the function name. The function accepts price and discountRate as inputs. Currency and Double are data types: they tell VBA what kind of values to expect and return. The expression on the right calculates the discounted price.

VBA returns a value when you assign it to the function’s own name. Here, NetAfterDiscount = sets the result. That may look unusual if you are used to languages with a return statement, but it is standard VBA syntax. A function can assign a result at different points in its code; the value held by the function name when it finishes is what the caller receives.

Arguments, data types, and return values

Arguments let a function work with different inputs each time it is called. The function above requires two arguments, in this order: a price and a discount rate. A rate of 0.15 represents 15 percent, so a price of 100 produces 85.00. The discount rate is a decimal rather than a whole-number percentage.

Declaring types makes the purpose of each input clearer and helps VBA catch some mistakes. Common types include Long for whole numbers, Double for numbers that can include decimals, String for text, Boolean for True or False, Date for dates and times, and Currency for monetary values that need fixed decimal precision. Variant can hold different kinds of values, but using it without a reason may make code harder to understand and validate.

VBA passes arguments ByRef by default, meaning a procedure can change the variable supplied by the caller. ByVal passes a copy of the value instead. The example uses ByVal so the function does not modify the caller’s variables. When designing a function, decide whether changing an input is truly intended; for calculation functions, ByVal is often the clearer choice.

You can also make an argument optional by declaring it Optional and providing an appropriate default value. This lets callers omit that argument. Be deliberate about defaults: a default rate, for example, should be obvious and appropriate for the task rather than silently surprising the person using the function.

Calling a function from an Excel worksheet

To make a worksheet custom function, put a Public Function in a standard module in the workbook’s VBA project. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then choose Insert > Module. You can also use Developer > Visual Basic. On Mac, the Visual Basic Editor shortcut is commonly Option+F11, or Fn+Option+F11 on keyboards where the function keys have other duties; the Developer tab is another route.

With prices in A2 and discount rates in B2, enter this formula in C2 to calculate the result for that row:

=NetAfterDiscount(A2,B2)

For A2 equal to 100 and B2 equal to 0.15, the result is 85.00. The function name in the cell must match the name in the VBA declaration. Excel supplies the cell values as arguments, runs the function, and displays the returned value. If the function is not available, check that the code is in a standard module, that it is Public, and that macros are permitted by the workbook’s security settings.

A workbook containing VBA should be saved in a macro-enabled format such as .xlsm; saving it as a regular .xlsx file removes its VBA project. If you share a workbook, recipients may need to enable macros according to their organization’s security policies. Only enable macros in files from sources you trust.

Calling a function from another VBA procedure

A function can also supply a result to a Sub or another function. A Sub performs an action but does not return a value in the way a Function does. The following example calls the earlier function and displays its result:

Public Sub ShowNetPrice()
    Dim result As Currency

    ' Calculate a sample price after a 15 percent discount.
    result = NetAfterDiscount(100, 0.15)
    MsgBox result
End Sub

Here, result is declared as a Currency variable, and the function’s returned value is assigned to it before the message appears. In a real workbook, code might pass values from cells or use the result in a later calculation instead. Before running a macro like this, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z. On Mac, the equivalent Undo shortcut is Command+Z, but it does not reverse VBA macro actions either.

Built-in functions and custom functions

VBA includes built-in functions for common tasks. For example, Len returns the length of a string, InStr searches for text within another string, and Date returns the current system date. These are available to VBA code without writing your own procedure. Excel also has worksheet functions, and some can be called from VBA through Excel’s object model, but the syntax and available functions depend on how they are called.

A custom function is worth writing when a calculation or transformation is repeated, has a meaningful name, or would make a worksheet formula unwieldy. Before creating one, check whether an ordinary Excel formula or an existing worksheet function already handles the need. Built-in worksheet formulas are often easier for other spreadsheet users to inspect, while VBA can be useful for specialized logic or when the same operation is needed in several macros.

A worksheet function should generally calculate and return a value, not change the workbook. A custom function entered in a cell is not a good place to write to other cells, change formatting, show a message box, or perform other user-interface actions. Excel may recalculate a function more than once, so side effects can be unpredictable. Use a Sub for actions such as updating a report or formatting a range.

Choosing between Function and Sub

Choose Function when the main purpose is to produce a result that another procedure or a worksheet can use. Choose Sub when the purpose is to carry out an action, such as importing data, creating a sheet, or displaying a dialog. A Sub can still calculate internally and assign values to cells; the distinction is that it is not called as a worksheet formula to return a value.

A quick decision checklist:

Does the caller need a result to use in a calculation? Use a Function.
Does the procedure mainly change something or carry out a sequence of actions? Use a Sub.
Should the procedure work in a worksheet cell? Make it a public custom function in a standard module and keep it focused on returning a value.
Will another developer need to understand it? Use a descriptive name, explicit argument types, and comments for non-obvious logic.

Common errors and practical limits

If a function reaches End Function without assigning its name, VBA returns the default value for the return type. For a number that is generally zero; for a string, it is an empty string; for an object, it is Nothing. Those defaults can make a coding mistake look like a valid result. Assign a result deliberately on every expected path, and consider how invalid inputs should be handled.

For example, a discount function may need to decide what to do if the rate is below zero or above one. It might reject such inputs with an error, or apply a documented rule. Do not silently change unexpected data unless that behavior is intentional. VBA supports error handling with statements such as On Error, but error handling should explain or manage a real failure rather than hide it.

Worksheet custom functions also depend on Excel being able to recognize and recalculate them. A misspelled function name, a disabled macro, code stored in the wrong module, or a workbook saved in the wrong file format can prevent normal use. When a function depends on values outside its listed arguments, Excel may not know when those values change; passing dependencies as arguments generally makes recalculation behavior easier to follow.

For troubleshooting, check the function declaration and module location, confirm the workbook’s file type, and test the procedure with simple known inputs. In the Visual Basic Editor, Debug > Compile VBAProject can reveal certain syntax or declaration errors. It does not prove that the calculation is logically correct, so compare the output against a hand-worked example too.

Frequently Asked Questions

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

A Function returns a value through its function name, so another procedure or a worksheet formula can use that result. A Sub performs actions and does not return a value as a function result. Use the one that matches the procedure’s purpose.

Can I use a VBA function in an Excel cell?

Yes. A suitable custom function must generally be Public and placed in a standard module in the workbook’s VBA project. The workbook must retain its VBA code, usually by being saved as .xlsm, and macros must be allowed by the applicable security settings.

Why does my custom function show #NAME? in the worksheet?

Excel may show #NAME? when it cannot recognize the function. Check spelling, confirm the function is Public and in a standard module, and verify that the workbook containing the code is open and saved with VBA. Also check whether macros are disabled.

Can a VBA function change cells?

A worksheet custom function should return a value rather than change cells, formatting, or Excel’s interface. Such changes are not appropriate for a formula that Excel may recalculate repeatedly. Put workbook-changing actions in a Sub or another macro instead.

Does Ctrl+Z undo a VBA function or macro?

No. Excel’s Undo command does not undo changes made by VBA macros, including actions initiated by a Sub. Save a separate copy before running a macro that changes workbook content. A worksheet function that only calculates a value normally updates as part of Excel’s formula calculation rather than as an undoable macro action.

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.