A VBA Function calculates and returns a value; a Sub carries out actions and does not return a value. Use a Function when you need a result to use in code or, in suitable cases, an Excel cell; use a Sub to automate tasks such as formatting a report or copying data.
What Function and Sub Mean in VBA
Function and Sub are two kinds of procedures in Visual Basic for Applications (VBA), the programming language used to automate desktop Office applications such as Excel. Both can contain instructions, accept arguments, and be called from other procedures. The defining difference is their purpose and return behavior: a Function has a return value, while a Sub does not.
A Function’s return value is assigned to the function’s own name. In this example, the function accepts a sales amount and a tax rate, then returns the calculated tax:
Public Function SalesTax(ByVal subtotal As Double, ByVal rate As Double) As Double
' Return the tax amount for the supplied subtotal and rate.
SalesTax = subtotal * rate
End Function
If subtotal is 100 and rate is 0.08, the function returns 8. The type after the argument list, As Double, declares the return type. A Sub instead starts with Sub and ends with End Sub; it has no return-type declaration because it does not return a value.
How a Function Returns a Result
Within a Function, assigning a value to the procedure name sets the value that will be returned. The procedure can assign that name more than once, for example as different branches of an If statement run. If execution reaches End Function without an explicit assignment, VBA returns the default value for the declared type: for example, zero for a numeric type or an empty string for a String. That default may conceal a missed calculation, so it is usually clearer to assign a meaningful result on every intended path.
The function can then be used in another procedure as part of an expression. With the SalesTax function above in a standard module, this procedure displays the returned value:
Sub ShowTax()
Dim amount As Double
Dim tax As Double
' Calculate tax on an illustrative amount of 100 at 8 percent.
amount = 100
tax = SalesTax(amount, 0.08)
MsgBox tax
End Sub
Here, tax receives 8. The variables are declared before use, and Double is appropriate for many decimal calculations. For exact currency handling, consider how rounding should work in the specific task; a numeric result alone does not define a business’s rounding policy.
A Function returns one value through its return mechanism, but that value need not always be a simple number or string. VBA can return a Variant, an array held in a Variant, or an object reference. An object-returning Function uses Set when assigning the object to the function name. For everyday Excel automation, however, a scalar return value is often the easiest design to understand and test.
What a Sub Does
A Sub is the natural choice for a sequence of actions. A report-formatting procedure might change a cell’s appearance, create a sheet, display a message, or save a file. It can read and write workbook content, but it does not provide a return value that can be placed on the right side of an assignment or used as an Excel worksheet formula.
For example, this Sub bolds the heading in cell A1 on the worksheet named Report:
Sub FormatReport()
Dim ws As Worksheet
' Format the report heading in this workbook.
Set ws = ThisWorkbook.Worksheets("Report")
ws.Range("A1").Font.Bold = True
End Sub
ThisWorkbook means the workbook containing the VBA code. That is often safer than ActiveWorkbook, which means whichever workbook happens to be active when the code runs. If the named worksheet does not exist, this example raises an error, so a larger automation should confirm the sheet name or handle the error appropriately.
A Sub may still communicate information in other ways. It can show a message, write a result into a cell, or change a variable passed to it by reference. Those approaches do not make it a Function: the Sub itself still has no return value that can be assigned in an expression.
How to Call Each Procedure
When you need a Function’s result, call it in an expression and store or use that result. The earlier example uses tax = SalesTax(amount, 0.08). A Function may also be called in a worksheet formula when it is set up as an Excel user-defined function (UDF), described below.
A Sub is called as an instruction. A Sub with no arguments can be called by writing its name on a line. With arguments, VBA has a parentheses rule that often trips up beginners: without the Call keyword, do not enclose the argument list in parentheses. With Call, use parentheses. For example, the forms are UpdateReport 2026 and Call UpdateReport(2026). This is a syntax distinction, not a difference in what the procedure does.
Functions also have call syntax rules, particularly when you ignore the returned value. In most code, assigning or otherwise using the result makes the intent clear. If a procedure is being used only for its side effects, ask whether it should instead be a Sub. Choosing the right procedure type helps readers understand whether a result is expected.
Using a Function in an Excel Worksheet
A public Function in a standard VBA module can often be used as a custom worksheet function. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and add the function there. On a Mac, the Visual Basic Editor can be opened from the Developer tab; the keyboard shortcut may be Option+F11 or require Fn as well, depending on the keyboard and system settings.
Assuming the SalesTax function is in a standard module and the worksheet has 100 in A2 and 8% in B2, enter =SalesTax(A2,B2) in another cell. The result is 8. The function must be visible to Excel as a worksheet UDF: make it Public rather than Private, and place it in a standard module rather than relying on code stored in a worksheet or workbook object module.
Worksheet UDFs are not a replacement for macros. During worksheet calculation, Excel expects a UDF to calculate and return a result; it cannot use a UDF formula to change other cells, formatting, or the Excel environment. Use a Sub, often run from the Macro dialog or a button, for those actions. Also remember that Excel for the web does not create, edit, or run VBA macros; use desktop Excel for VBA work.
When distributing a workbook containing VBA, save it in a macro-enabled format such as .xlsm. Excel’s macro security settings affect whether code runs. Do not enable macros in a file from an untrusted source simply because a workbook asks you to; VBA can make changes beyond the visible worksheet.
Choosing Between a Function and a Sub
Start by asking what the caller needs. If it needs a calculated answer, choose a Function. If the goal is to perform an operation, choose a Sub. A report macro might call several Functions to calculate totals and then use those results while writing or formatting the report.
- Choose a Function for reusable calculations such as a commission amount, a text cleanup result, or a value derived from supplied inputs.
- Choose a Sub for tasks such as importing a file, formatting a range, creating a worksheet, or displaying a dialog.
- Use both when an action depends on a calculation: let a Function calculate, then let a Sub apply or present the result.
Do not choose a Function only because its name sounds more general. A Function that silently edits cells or changes application settings is harder to reason about, especially if it is used in worksheet formulas. Similarly, a Sub that calculates a value but gives callers no clear way to receive it may be awkward to reuse. Keep calculation and action separate when that makes the code easier to maintain.
Arguments, Scope, and Common Errors
Both procedure types can accept arguments. In VBA, arguments are passed ByRef by default, which means a procedure may change the caller’s variable; ByVal passes a value instead. The SalesTax example marks its inputs ByVal because it only needs to read them. Explicit declarations make intent easier to see and reduce accidental changes.
Procedures also have scope. Public makes a procedure available to other code in the project, while Private limits its use to the module where it is declared. For a worksheet UDF, visibility and module placement matter as well as the Function keyword. A correctly written Function may still not appear to work in a cell if it is Private or placed in the wrong kind of module.
Common problems include misspelling a procedure name, passing the wrong number or type of arguments, referring to a worksheet that does not exist, and expecting a Sub to produce a value. A Function can also return an unexpected default if its return variable is never assigned. When debugging, check the first highlighted line, verify the procedure declaration and argument list, and test the calculation with simple inputs. Use Debug > Compile VBAProject in the Visual Basic Editor to catch many compile-time errors before running the code.
Running Macros and Protecting Your Workbook
In desktop Excel for Windows, a public Sub in a standard module can be run from Developer > Macros, or by pressing Alt+F8 and selecting it. On Mac, use the Developer tab’s macro controls; keyboard shortcuts can differ by keyboard configuration. A Function intended for a worksheet is normally evaluated by entering its formula, not by running it from the Macro dialog.
Before running a macro that edits data, save a separate copy of the workbook. VBA changes cannot generally be undone with Ctrl+Z, and running a macro may clear Excel’s undo history. On Mac the corresponding undo shortcut is typically Command+Z, but that does not make VBA actions reliably undoable. Test automation on a copy, especially if it deletes sheets, overwrites ranges, or changes files.
For basic diagnostics, use breakpoints and step through code in the Visual Basic Editor, or add temporary messages and remove them after debugging. The Immediate window is useful for inspecting values while paused in code. These tools help distinguish a calculation problem from a workbook reference or execution problem without relying on trial and error in the original file.
Frequently Asked Questions
Can a VBA Sub return a value?
No. A Sub has no return value. It can write a result to a worksheet or alter a ByRef argument, but if callers need a value as the result of a procedure, use a Function.
Can a VBA Function change cells?
A Function called from a worksheet formula should calculate and return a result, not change other cells or formatting. Use a Sub for workbook-changing actions. A Function called from other VBA code can have side effects, but separating those effects from calculations generally makes code clearer.
Can I use a VBA Function in an Excel formula?
Yes, if it is set up as an accessible worksheet UDF, typically a Public Function in a standard module in a macro-enabled workbook. Enter it like a formula, for example =SalesTax(A2,B2), and use desktop Excel because Excel for the web does not run VBA.
Why does my custom Function not appear in Excel?
Check that it is declared Public, is in a standard module, and has a valid declaration. Confirm that macros are permitted for the workbook and that the file is saved in a macro-enabled format. A Function stored in a worksheet code module is not exposed like a standard-module UDF.
Should I use Function or Sub for a calculation?
Use a Function when the calculation’s result must be returned and reused. Use a Sub when the main purpose is an action. For a task that calculates and then changes the workbook, a Sub can call one or more Functions and use their returned values.
Related
- Splinter Cell Split Jump: How the Wall Move Works in the Games
- Conditional Formatting Definition and Practical Examples
- Power Query Join Tables: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Evaluate: How It Works and When to Use ItIn Excel VBA, Application.Evaluate interprets a text expression using Excel’s formula language and returns the result as a…
- VBA Nested Dictionary: Create, Read, and Update DataIn VBA, a nested dictionary is a Scripting.Dictionary whose item is another dictionary. Create an outer dictionary, create…
- Excel VBA Hyperlink: Add, Edit, and TroubleshootExcel VBA does not have a built-in Hyperlink() function: to create a clickable link in a macro, use…
- MS Access VBA String Functions: A Practical GuideMS Access VBA string functions let you extract, search, compare, clean, and assemble text in procedures and functions.…
- Excel VBA AutoFilter Multiple Criteria: A Practical GuideTo filter a range with Excel VBA using multiple allowed values in one column, pass an array to…
- VBA Input Box for Password: Masking and Safer OptionsVBA’s built-in InputBox cannot mask a password: characters appear as the user types. To show dots or asterisks…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.