Excel VBA Function vs Sub: Differences and Examples

VBA & macros · Updated November 21, 2026

In Excel VBA, a Function calculates and returns a value, while a Sub performs an action and does not return a value. Use a Function when you need a result you can use in a formula or other code; use a Sub for tasks such as formatting cells, writing data, or displaying a message.

Function vs Sub in Plain Language

Both Functions and Subs are VBA procedures: named blocks of code that Excel or another procedure can run. Their key difference is what happens when the code finishes. A Function provides a return value. A Sub does not. That distinction affects how you call each procedure and what jobs it suits.

Think of a Function as a calculator: it receives inputs, works something out, and gives back an answer. A Sub is more like an instruction: it carries out a task, such as putting a label in a cell. A Function can also be called by a Sub, and a Sub can call a Function to get a value. They are complementary tools, not competing versions of the same feature.

What a VBA Function Does

A Function has a name, optional parameters, and a return type. In VBA, you return a value by assigning it to the function’s own name. This example adds two numbers:

Public Function AddTwo(ByVal firstNumber As Double, ByVal secondNumber As Double) As Double
    ' Return the sum of the two input values.
    AddTwo = firstNumber + secondNumber
End Function

The two parameters are inputs, and As Double after the closing parenthesis specifies that the function returns a number that can include decimals. The line AddTwo = firstNumber + secondNumber sets the answer. Merely calculating an expression without assigning it to the function name would not return that answer.

A Function can return different kinds of values, including numbers, text, dates, or a Boolean value such as True or False. It can also return an object when declared appropriately. Choosing a return type helps make the procedure’s purpose clear and can expose type mismatches earlier. If the code needs to return a text result, for example, declare the function As String.

What a VBA Sub Does

A Sub is appropriate when the main purpose is to do something rather than calculate an answer for its caller. For example, this Sub writes a status in cell A1 of the workbook containing the code:

Public Sub WriteStatus()
    Dim destination As Range

    ' Change Sheet1 to the name of an existing worksheet.
    Set destination = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    destination.Value = "Complete"
End Sub

The procedure has no return type after its parentheses, and it does not assign a result to a procedure name. It changes a cell instead. If the workbook does not contain a worksheet named Sheet1, the code will produce an error, so replace that name with the relevant sheet name before running it.

Subs are often used for macros that format reports, copy or clear ranges, update several cells, open a form, or coordinate a sequence of steps. A Sub can accept parameters too; it simply cannot return a value in the same way a Function does. It can still produce visible effects, such as changing a workbook or showing a message.

Arguments, Return Values, and Calling Syntax

Arguments are the actual values supplied when a procedure runs; parameters are the named inputs declared in its procedure line. The AddTwo Function above has two parameters. With the numbers 7 and 5 supplied, it returns 12. From VBA, a caller can store that result:

Public Sub ShowSum()
    Dim answer As Double

    ' Store the value returned by the Function.
    answer = AddTwo(7, 5)
    MsgBox answer
End Sub

Here the Function supplies a value that the Sub can use. By contrast, a Sub call is an instruction, not an expression that can be assigned to a variable. VBA commonly calls a Sub without the Call keyword, as in WriteStatus. If using Call, put the arguments in parentheses; without Call, a procedure call with arguments is generally written without parentheses. Following one style consistently makes code easier to read.

Parameters can be marked ByVal or ByRef. ByVal passes a copy of a value, so changing the parameter inside the procedure does not change the caller’s variable. ByRef passes the variable by reference and can allow the procedure to change it. VBA uses ByRef by default, so explicitly writing ByVal where appropriate helps readers understand whether the caller’s value is meant to be changed.

Using a Function in an Excel Worksheet

A suitable public VBA Function can be called from a worksheet formula. For Excel to expose a regular user-defined worksheet function, put a Public Function in a standard VBA module, rather than placing it in a worksheet’s code module or the ThisWorkbook module. In the Visual Basic Editor, use Insert > Module, then add the Function there.

For the AddTwo Function above, assume A2 contains 7 and B2 contains 5. Enter this formula in C2:

=AddTwo(A2,B2)

The result in C2 is 12. Excel recalculates the formula when its referenced inputs change, subject to the workbook’s calculation settings. The UDF needs to be available in the workbook or in an accessible add-in. If the workbook is closed or the function is missing, Excel may show a #NAME? error.

Worksheet UDFs have important limits. They are designed to return a result based on their arguments; they generally should not change other cells, alter Excel settings, or perform actions such as changing a selection. Excel may reject or behave unpredictably with side effects from a UDF called by a cell formula. For work that changes the workbook, use a Sub, typically triggered manually, by a button, or by an appropriate event procedure. A normal VBA Function can do more when called from other VBA code, but worksheet-call restrictions still matter when it is used as a formula.

How to Choose Between a Function and a Sub

Start with what the caller needs. If it needs a value to continue a calculation, use a Function. If the procedure’s purpose is to make a change or carry out a task, use a Sub. Some larger solutions use both: a Sub handles the workbook workflow, while one or more Functions calculate values that the Sub then writes or uses.

  • Choose a Function for reusable calculations, conversions, classifications, or checks that return a result.
  • Choose a Sub to edit ranges, apply formatting, create a report, or run a sequence of actions.
  • Use both when a task needs calculations and workbook changes; keep the calculation logic separate when that improves clarity.

Do not choose a Function merely because it sounds more reusable. If the procedure’s real job is to change cells, making it a worksheet-callable Function can create confusing formulas and side effects. Conversely, a Sub is inconvenient when other code needs its calculated answer, because it cannot return that answer directly. Clear names also help: names such as CalculateDiscount and FormatReport signal different purposes.

Creating, Saving, and Running VBA Code

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or select Developer > Visual Basic. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. In the editor, choose Insert > Module for a standard module, then enter the code. On Mac, use Developer > Visual Basic if the Developer tab is available; the editor shortcut is commonly Option+F11, sometimes with Fn depending on the keyboard. VBA is not supported for running macros in Excel for the web.

To run a public, argument-free Sub from the Excel interface, select Developer > Macros, choose its name, and select Run. You can also run it from the Visual Basic Editor with the cursor inside the procedure and press F5; on Mac, function keys may require Fn. A Function designed for a worksheet is normally entered as a cell formula rather than run from the Macros dialog.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the code. A standard .xlsx workbook cannot store VBA macros, and saving in that format can remove them. Only enable macros in files from sources you trust. Before running a macro that changes data, save a copy of the workbook: macro changes cannot be undone with Ctrl+Z. On Mac, use Command+Z for ordinary undo, but it does not restore changes made by a macro either.

Common Errors and Misconceptions

A frequent mistake is writing a Function but forgetting to assign its result to the Function’s name. The procedure may run, but the caller can receive the default value for the return type rather than the intended answer. Another common issue is a mismatch between the declared return type and the value supplied, which can lead to conversion problems or a runtime error.

If Excel displays #NAME? for a custom function, check that the function name is spelled correctly, the code is in a standard module, and the workbook containing it is open and macro-enabled. Also confirm that the function is declared Public and does not require arguments that the formula omits. If VBA reports that a Sub or Function is not defined, check spelling, scope, and whether the procedure is in a module accessible to the caller.

Another misconception is that a Function cannot change anything at all. VBA Functions can be used in code and may interact with objects when called from VBA, but worksheet UDFs are not a reliable way to trigger changes elsewhere in the workbook. Keep worksheet functions focused on returning values. Use a Sub for workbook actions so a reader can tell from the code how the workbook may change.

Frequently Asked Questions

Can a VBA Sub return a value?
A Sub does not return a value as a Function does. If the caller needs a result, use a Function, or have the Sub write the result to an object or variable passed by reference when that design is appropriate.

Can I call a VBA Function from a worksheet cell?
Yes. A public Function in a standard module can usually be used in a formula if its inputs and behavior are suitable for a worksheet UDF. Save the file in a macro-enabled format and keep the workbook or add-in containing the function available.

Can a Function change cells in Excel?
A Function called from VBA may interact with workbook objects, but a Function used in a worksheet formula should generally return a result rather than change other cells. Use a Sub for deliberate workbook changes.

Can a Sub have arguments?
Yes. A Sub can accept one or more parameters and use the values supplied by its caller. The difference is that it does not return a value through the procedure name.

Why does my custom Function return zero or a blank?
Check that the code assigns the intended value to the Function’s name on every relevant path. Also confirm the declared return type and the formula’s arguments; a missing assignment can leave the result at its default value.

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.