Excel VBA Functions: How They Work and When to Use Them

VBA & macros · Updated November 21, 2026

Excel VBA functions are procedures that perform a calculation or task and return a result; you can write your own functions or call many of Excel’s built-in worksheet functions from VBA code. Use a VBA function as a worksheet formula when you need a reusable custom calculation, and use a Sub procedure when the goal is to change cells, formatting, or workbook structure.

What “Excel VBA function” can mean

The phrase can refer to three related things, so it helps to separate them. First, VBA has its own built-in functions, such as Len for string length, Left for text from the start of a string, IsNumeric for checking a value, and Date for the current date. Second, Excel has worksheet functions such as SUM and AVERAGE, which VBA can call in code. Third, a developer can write a custom VBA function, often called a user-defined function or UDF, and use it in an Excel cell.

All three can be useful, but they are not interchangeable. A VBA function is a kind of procedure in the Visual Basic for Applications language. A worksheet function is an Excel calculation. A UDF is written in VBA but can be called from a worksheet formula, much like a built-in Excel function. Knowing which kind you mean makes it easier to find the right syntax and avoid putting code in the wrong place.

Function procedures versus Sub procedures

A function procedure returns a value. In VBA, it returns that value by assigning it to the function’s own name. A Sub procedure performs actions but has no return value. For example, a function could calculate a discount amount, while a Sub could place a label in a cell or format a report. Both can accept arguments, but only a function can be used as a worksheet calculation.

A function declaration usually identifies its visibility, name, inputs, and return type. Choosing an explicit type such as Double, String, or Boolean makes the intended result clearer and can help catch mistakes. Arguments declared with ByVal are passed as values; VBA’s default is ByRef, which can allow a procedure to change a variable passed to it. When a procedure should not change an input variable, ByVal is often the clearer choice.

Writing a simple custom function

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then choose Insert > Module. Put a public function in that standard module. The example below calculates a gross amount from a net amount and a rate; it deliberately leaves the inputs as arguments so the same function can be reused with different values.

Option Explicit

Public Function GrossWithRate(ByVal netAmount As Double, ByVal rate As Double) As Double
    ' Returns the amount after applying the rate.
    GrossWithRate = netAmount * (1 + rate)
End Function

With the net amount in A2 and the rate in B2, enter =GrossWithRate(A2,B2) in C2. If A2 contains 100 and B2 contains 0.08, the result is 108. The rate is a decimal, so 8% is represented by 0.08; a cell formatted as a percentage can display that value as 8%. The formula depends on A2 and B2, so Excel can recalculate it when either input changes.

On Excel for Mac, VBA is available in the desktop app, although some VBA features and objects differ from Windows. You can open the editor from Tools > Macro > Visual Basic Editor. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use desktop Excel for this work.

Calling Excel worksheet functions from VBA

VBA includes many functions of its own, but Excel’s worksheet functions may be a better fit for familiar spreadsheet calculations. In a VBA procedure, Excel exposes many of these through Application.WorksheetFunction. For example, a macro can use Excel’s SUM calculation and place the answer in a cell. The range should be qualified with a worksheet reference so the code does not accidentally use whichever sheet happens to be active.

Option Explicit

Public Sub PutColumnTotalInCell()
    Dim dataSheet As Worksheet
    Dim total As Double

    ' Use a named sheet rather than relying on the active sheet.
    Set dataSheet = ThisWorkbook.Worksheets("Data")
    total = Application.WorksheetFunction.Sum(dataSheet.Range("B2:B10"))
    dataSheet.Range("B11").Value = total
End Sub

This procedure assumes a worksheet named Data and numeric values in B2:B10. WorksheetFunction methods can raise a VBA runtime error in cases where the Excel function would return an error, such as a lookup with no match. For cases where an error value needs to be handled as data, Application.Match can be more convenient than Application.WorksheetFunction.Match, because it can return a Variant containing an Excel error value that code can test with IsError.

Not every worksheet function is exposed through the WorksheetFunction object, and names or argument behavior can vary between VBA and worksheet use. Check Excel’s VBA Object Browser or the function’s documentation rather than assuming that every cell function has a directly callable method. For operations that do not need custom VBA logic, a normal worksheet formula is often simpler and easier for another person to inspect.

Using a UDF in a workbook

Once a public UDF is in a standard module, Excel can usually call it from a cell by its function name. It can also be used inside a larger formula, copied down a column, or supplied with cell references and constants. If the workbook is distributed to someone else, its VBA project must be present and macros must be permitted for the UDF to calculate. Save the file in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm) rather than ordinary .xlsx, which does not retain VBA code.

A UDF is intended to calculate and return a result, not to carry out workbook actions. A function called from a cell should not try to edit other cells, change formatting, add sheets, or display user-interface prompts. Put those actions in a Sub procedure, which can be run as a macro or called from a button. Keeping calculations separate from actions makes a workbook easier to understand and reduces surprising recalculation behavior.

Excel tracks the cell references passed as UDF arguments. If code reads other cells that are not among its arguments, Excel may not know that those cells affect the result and may not recalculate the UDF when they change. Pass all relevant inputs as arguments where practical. There is also an Application.Volatile method that can mark a UDF for recalculation whenever Excel recalculates, but volatility can increase calculation work in a large workbook and should not be used as a substitute for clear inputs.

Where the code goes and how to run it

For a worksheet UDF, use a standard module in the Visual Basic Editor, not a worksheet cell or the formula bar. A function placed in a worksheet code module or workbook module may be less straightforward to use or share. To run a Sub macro from Windows Excel, use Alt+F8, select the macro, and choose Run; the Developer tab’s Macros command is another route. On Mac, use Tools > Macro > Macros. The Developer tab can be enabled on Windows through File > Options > Customize Ribbon, then selecting Developer.

Before running any macro that changes a workbook, save a separate copy: macro actions cannot be undone with Ctrl+Z. This is especially important for code that clears ranges, overwrites values, or changes many sheets. A small, non-destructive test on a copy is a sensible first step. For example, a macro can write a status beside the currently selected cell:

Save a copy of the workbook before running this macro; its cell edit cannot be reversed with Ctrl+Z.

Option Explicit

Public Sub MarkSelectedCell()
    Dim target As Range

    ' Write a status in the cell immediately to the right.
    Set target = ActiveCell
    target.Offset(0, 1).Value = "Reviewed"
End Sub

On a workbook with multiple sheets, confirm the active sheet and selected cell before running a macro that relies on ActiveCell. For important automation, explicit references to a named worksheet and range are safer than relying on the current selection.

Common errors and practical safeguards

Several recurring problems are straightforward to prevent. If Excel displays #NAME? for a custom function, check that the function name is spelled correctly, that the code is in the workbook you are using, and that macros are enabled under the applicable security policy. If a function is not available in another workbook, confirm that the file containing the VBA project is open or that the code has been included in the destination workbook.

  • Wrong procedure type: A worksheet cell cannot use a Sub as a formula. Use a Function for a returned calculation.
  • Missing or mismatched inputs: Check argument order, data types, and whether a rate is entered as 8% or as 8.
  • Unqualified references: Range("A1") can refer to the active sheet. Qualify ranges with the intended worksheet when writing reliable macros.
  • Errors in source data: Empty cells, text in numeric ranges, and Excel error values can affect calculations. Decide whether to reject, ignore, or report such values.
  • Security prompts: Do not enable macros in an unfamiliar file just to make a function work. Only enable code from a source you trust and follow your organization’s security rules.

VBA’s Round function also differs from Excel’s worksheet ROUND for some halfway values: VBA uses banker’s rounding in relevant cases, while Excel’s worksheet function rounds halves away from zero. If a financial or reporting rule requires a particular rounding convention, select and document the intended function rather than swapping them casually.

When VBA is worth using—and alternatives

A VBA UDF is useful when a calculation is repeated, has logic that is awkward to express in a worksheet formula, and needs to be available wherever the workbook is used. It may be less suitable when the workbook needs to work in a browser, when recipients cannot enable macros, or when colleagues need to understand every calculation without opening the VBA editor. A custom function also creates a maintenance obligation: someone needs to preserve the code, explain its inputs, and update it when requirements change.

Before writing VBA, consider these alternatives:

  • Built-in worksheet formulas: Prefer these for standard calculations, lookups, and text operations when they meet the need.
  • Named formulas or LAMBDA: In Microsoft 365 or Excel 2024, LAMBDA can define reusable formula logic without VBA, though it is not a VBA procedure.
  • Power Query: Useful for repeatable data import and transformation tasks that do not require custom cell-by-cell macros.
  • Office Scripts: A separate automation option in supported Microsoft 365 environments, particularly for web workflows; it is not VBA and has different capabilities.

For a one-off action such as formatting a report, a Sub macro is more appropriate than a UDF. For a simple calculation, a worksheet formula avoids macro security and cross-platform issues. Choose the smallest tool that meets the requirement and that the workbook’s users can maintain.

Frequently Asked Questions

How do I create a VBA function in Excel?
Open the Visual Basic Editor, choose Insert > Module, and write a public Function procedure with its inputs and return type. Save the workbook as an .xlsm file if you need to keep the VBA project.

Can I use a VBA function in a cell?
Yes. A public UDF in a standard module can usually be entered as a cell formula, provided the workbook containing the code is available and macros are allowed. A Sub procedure cannot be called as a worksheet formula.

Can Excel for the web run VBA functions?
No. Excel for the web does not create, edit, or run VBA macros. Open the workbook in desktop Excel to use its VBA code.

Can a VBA function change other cells?
A function used in a worksheet formula should return a result rather than change other cells or formatting. Use a Sub macro for workbook actions, and save a copy before running code that changes data.

What is the difference between VBA functions and worksheet functions?
VBA functions belong to the VBA language, while worksheet functions belong to Excel’s formula engine. VBA code can call many worksheet functions through Application.WorksheetFunction, and a custom VBA UDF can be called from a worksheet cell.

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.