Excel VBA Sub vs Function: Key Differences and Examples

In Excel VBA, a Sub performs actions but does not return a value as its result; a Function calculates and returns a value. Use a Sub to automate tasks such as formatting cells or copying data, and use a Function when you need a result that another procedure—or a worksheet formula—can use.

What a Sub and a Function have in common

Both are VBA procedures: named blocks of code that can accept inputs, contain variables and decision logic, and be called from elsewhere in a workbook’s VBA project. Each begins with a declaration and ends with a matching End Sub or End Function. Both can be placed in a standard module, and both can be declared Public or Private to control whether other parts of the project can call them.

The central difference is the procedure’s result. A Sub does work; a Function produces a value. That distinction affects how you call each procedure and where you can use it. A button or keyboard shortcut can run a Sub, while a Function can be used in an expression in VBA. A suitable public Function in a standard module can also be called from an Excel cell formula.

Neither procedure is automatically better or faster. The right choice depends on whether the main purpose is to carry out an action or calculate a result. A larger automation project may use both: a Sub can organize the work, and Functions can handle calculations or reusable checks within it.

How a Sub works

A Sub can change workbook contents, apply formatting, display a message, or perform other actions. It does not have a return-value assignment like a Function. You can run a Sub directly from the Visual Basic Editor, assign it to a button, or call it from another procedure. A Sub can also accept arguments, so the caller can tell it what to work on.

For example, this Sub applies a ten-percent reduction to the numeric value in the active cell. It changes the workbook rather than returning a calculated result to the caller:

Public Sub ApplyTenPercentReduction()
    Dim target As Range

    ' Work on the currently selected cell.
    Set target = ActiveCell

    If IsNumeric(target.Value) Then
        target.Value = target.Value * 0.9
    End If
End Sub

This example is deliberately simple, but it also illustrates a practical risk: the macro acts on whichever cell is active when it runs. A production macro should identify its target range clearly, validate inputs, and avoid relying on a user’s current selection unless that is intentional. Save a copy of the workbook before running a macro. Excel’s Ctrl+Z undo command cannot undo changes made by VBA.

In Excel for Microsoft 365 on Windows, you can open the VBA editor with Alt+F11. On a Mac, the shortcut may depend on the keyboard and function-key settings; use the Visual Basic command on the Developer tab if needed. Excel for the web does not create or run VBA macros, so use desktop Excel for these examples.

How a Function returns a value

A Function returns its result by assigning a value to its own name. The data type in its declaration describes the kind of value it is expected to return. Here, the Function accepts two numbers and returns their product:

Public Function SalesTax(amount As Double, rate As Double) As Double
    ' Return the tax amount, not the amount including tax.
    SalesTax = amount * rate
End Function

The caller can store that result, combine it with another calculation, or display it. For example, if a Sub declares a variable named tax, it can call the Function and assign the returned number to that variable:

Public Sub ShowTaxExample()
    Dim tax As Double

    ' Calculate tax on 100 at a rate of 8 percent.
    tax = SalesTax(100, 0.08)
    MsgBox tax
End Sub

The result is 8, because 100 multiplied by 0.08 equals 8. The Function returns the tax amount only; it does not add that amount to the original 100. Naming and documenting what a Function returns helps prevent a caller from confusing tax, total price, and tax rate.

In VBA, a Function can also finish early with Exit Function, and it can return different values on different branches by assigning to its name. If execution reaches End Function without an assignment, the result is the default value for its return type—often zero for a numeric type or an empty string for a String. It is usually clearer to set a deliberate result on every path.

Calling procedures and using their results

A Sub call is a statement: it tells VBA to perform an action. You can call it by name, with its arguments, from another procedure. Parentheses are used around arguments when the call’s result is being assigned or used as part of an expression. VBA also supports the Call keyword, though many examples omit it.

A Function call is an expression because it supplies a value. That value can be assigned to a variable, compared with another value, or passed into a different procedure. For readability, assigning a meaningful result to a well-named variable is often easier to maintain than placing a long Function call inside a complicated expression.

Procedures can be organized into a useful sequence. A Sub might check that required input cells are filled, call a Function to calculate a result, then write that result to a designated cell and format the output. The Function handles the calculation; the Sub handles the interaction with the workbook. This separation makes it easier to reuse the calculation without repeating the code that changes cells.

A Sub can communicate information indirectly—for example, by changing a cell or by modifying an argument passed ByRef. That does not make it a Function with a normal return value. If the main purpose is to calculate one result that callers should use, a Function is usually the clearer design.

Arguments, data types, and scope

Subs and Functions can both have parameters, which are the inputs listed in their declarations. Parameters help make a procedure reusable: a calculation can accept different numbers each time, and a Sub can receive a range rather than being tied to one hard-coded location. Use a suitable data type, such as Double for many decimal calculations or Range for a cell or group of cells.

VBA passes arguments ByRef by default, meaning a procedure may be able to change the caller’s variable through that argument. Use ByVal when the procedure should receive a value without changing the caller’s variable. For objects such as ranges, understand what is being passed before assuming that ByVal prevents changes to the object itself: it controls the argument variable, not a general promise that the underlying workbook object cannot be changed.

Scope controls where a procedure can be called. A Public procedure is available to other code in the VBA project; a Private procedure is limited to its containing module. A Function intended for use as a worksheet formula should generally be Public and stored in a standard module, rather than declared Private or placed as an ordinary helper in a worksheet or workbook object module.

Return types also matter. A Function declared As Double should return a numeric result; a Function declared As String should return text. A mismatched or invalid value can cause a type mismatch or other runtime error. Clear parameter names, descriptive procedure names, and explicit types make it easier to spot mistakes when revisiting code.

Using a Function in an Excel worksheet

A public Function in a standard module can often be called from a worksheet cell as a user-defined function, or UDF. For example, after adding the SalesTax Function above to a standard module in the workbook, a cell formula can call it with a cell containing an amount and another containing a rate. The function’s name then appears as the formula’s calculation, much like a built-in Excel function.

Worksheet UDFs are best for returning a value based on their inputs. They are not a replacement for macros that edit the sheet: a worksheet UDF should not be used to change other cells, apply formatting, or perform workbook actions. Instead, have a Sub make those changes. A UDF’s recalculation also depends on its formula inputs and Excel’s calculation behavior; do not assume that it will run like a button-driven macro whenever any unrelated workbook event occurs.

To share a workbook containing VBA code, save it in a macro-enabled format such as .xlsm. A standard .xlsx workbook does not retain VBA macro code when saved in that format. Recipients may also need to allow macros under their organization’s security policies. Excel for the web can open many macro-enabled workbooks, but it cannot run the VBA code; use desktop Excel to execute the macro or UDF.

How to choose between a Sub and a Function

Start by asking what the caller needs. If the goal is an action, choose a Sub. If the goal is a value that should be returned and reused, choose a Function. This simple test covers most cases, but the context of use helps confirm the choice:

Choose a Sub to import data, clean a range, format a report, display a prompt, or respond to a button click.
Choose a Function to calculate a score, convert a value, test a condition, or return a result another procedure needs.
Use both when a workflow has a calculation and a separate action, such as calculating a total and then writing it to a report.
Use a built-in Excel formula when it already does the job and the result should remain transparent and recalculable without enabling VBA.

Functions are not only for worksheet formulas: they can be called from VBA too. Likewise, a Sub can accept inputs and perform sophisticated logic. The deciding factor is not complexity, but whether the procedure’s intended output is an action or a returned value. A well-designed Function is usually easier to test because its inputs and result can be examined separately from workbook changes.

Common mistakes and practical checks

A common mistake is trying to enter a Sub name in a worksheet cell as if it were a formula. A Sub does not return a worksheet value, so it cannot serve as a normal cell formula. Use a Function for a formula result, or run the Sub from the VBA editor, a button, or another macro.

Another frequent issue is defining a Function in the wrong place or making it Private when a worksheet needs to find it. For a worksheet UDF, put a Public Function in a standard module and check that the workbook containing it is open. If the formula displays an error or the function is missing from formula suggestions, confirm the procedure declaration, module location, spelling, and macro settings.

Also distinguish a calculation error from a procedure that has not run. A Function used in a cell is governed by Excel’s formula calculation, whereas a Sub must be explicitly run or called. If a Function depends on information that is not represented by its arguments, Excel may not know when to recalculate it. Pass the relevant inputs as arguments where practical instead of relying on hidden state such as the active cell.

Finally, remember that macros can make changes that are difficult to reverse. Test code on a copy, use specific ranges rather than broad selections, and consider what happens when an input is blank, text, or outside the expected range. VBA’s error handling can make larger procedures more robust, but it should report or handle a real problem rather than silently conceal it.

Frequently Asked Questions

Can a Sub return a value in VBA?
A Sub has no Function-style return value. It can communicate through a ByRef argument, a changed workbook cell, or another shared mechanism, but a Function is usually clearer when the caller needs a calculated result.

Can I call a Function from a Sub?
Yes. A Sub can call a Function and store or use the returned value. For example, the tax example assigns the result of SalesTax(100, 0.08) to a variable before displaying it.

Can I use a Sub in an Excel cell formula?
No. A worksheet formula needs a value, and a Sub does not return one. Create a suitable public Function in a standard module if the calculation should be available as a worksheet formula.

Can a VBA Function change cells?
A Function called from a worksheet formula should return a result rather than edit other cells or formatting. Use a Sub for workbook changes. A Function called by VBA can contain other code, but separating calculation from side effects usually makes the project easier to understand.

Do VBA macros work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run its VBA code; the web version may display the workbook’s contents without executing the macros.

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.