A VBA function is a procedure that performs work and returns a value. Its basic structure is Function Name(arguments) As ReturnType, followed by statements that assign the result to the function’s own name, and then End Function. In Excel for Microsoft 365 on Windows, functions can be called by other VBA procedures or, with some limits, entered as formulas in worksheet cells.
The basic structure of a VBA function
VBA code is organized into procedures. A function procedure starts with a declaration, contains the statements that do the work, and ends with an explicit closing line. A typical function looks like this:
Option Explicit
' Returns the sum of two numbers.
Public Function AddNumbers(ByVal FirstNumber As Double, _
ByVal SecondNumber As Double) As Double
AddNumbers = FirstNumber + SecondNumber
End Function
Option Explicit asks VBA to require variables to be declared before use. Put it at the top of a code module; it is a module setting rather than part of the function itself. Public makes the procedure available outside its module, while Function identifies the procedure type. AddNumbers is the function name, the items in parentheses are its arguments, and the final As Double declares the return type.
The underscore at the end of the first argument line is VBA’s line-continuation character. It lets a declaration span multiple lines for readability. In the function body, the expression on the right is assigned to AddNumbers, which is how this function supplies its result. End Function marks the procedure’s end; it is not interchangeable with End Sub.
What each part of the declaration means
A function declaration can specify who may call it, what it is called, what information it accepts, and what kind of result it returns. In simplified form, the pattern is:
[Public | Private] [Static] Function FunctionName([arguments]) [As DataType]
The brackets here indicate optional elements in the pattern; do not type the brackets or the vertical bar into VBA. Public and Private control visibility. Static, when used, preserves the function’s local variables between calls. The function name must follow VBA naming rules: use a valid identifier, avoid spaces, and do not use a reserved VBA word. A descriptive name such as CalculateCommission is easier to understand than a vague name such as DoIt.
The return type after As helps communicate what the function produces and lets VBA catch some incompatible assignments. Common choices include String for text, Boolean for True or False, Long for whole numbers, Double for decimal numbers, and Date for dates and times. Variant can hold different kinds of values, but using it without a reason can make code less predictable. If a function has no declared return type, VBA uses Variant.
How a function returns a value
Unlike languages that use a separate return statement, a VBA function normally returns a value by assigning it to the function’s name. In the earlier example, AddNumbers = FirstNumber + SecondNumber sets the result. The assignment can appear in a conditional branch, so different inputs can produce different results. If execution reaches End Function without assigning a value, VBA returns the default value for the declared type: for example, zero for a numeric type, an empty string for a string, or False for a Boolean.
This small example returns a text label based on a numeric score:
Option Explicit
' Classifies a score using a simple threshold.
Public Function ScoreLabel(ByVal Score As Double) As String
If Score >= 70 Then
ScoreLabel = "Pass"
Else
ScoreLabel = "Review"
End If
End Function
The return type and the assigned value should agree. VBA may convert some compatible values automatically, but relying on implicit conversions can produce surprising results, particularly when text, empty cells, or errors are involved. For a function that returns an object rather than a simple value, VBA uses Set when assigning the object to the function name. Most everyday Excel functions return values, not objects.
Arguments, data types, and optional inputs
Arguments are the inputs a caller supplies inside the parentheses. Declare each argument with a data type when possible. In VBA, arguments are passed ByRef by default, meaning the procedure can change the caller’s variable. Use ByVal when the function should receive a value without changing the original variable. This makes intent clearer and avoids many accidental changes. For simple worksheet-style calculations, ByVal is often a sensible choice.
A function can also accept an optional argument. The following example uses a default discount rate when the caller omits it:
Option Explicit
' Returns a price after applying a discount from 0 to 1.
Public Function DiscountedPrice(ByVal ListPrice As Double, _
Optional ByVal DiscountRate As Double = 0.1) As Double
If DiscountRate < 0 Or DiscountRate > 1 Then
Err.Raise vbObjectError + 1000, "DiscountedPrice", _
"DiscountRate must be between 0 and 1."
End If
DiscountedPrice = ListPrice * (1 - DiscountRate)
End Function
With a list price of 100 and no second argument, the function returns 90 because the default rate is 0.1. Supplying a rate of 0.2 returns 80. The example rejects rates below zero or above one rather than silently producing a result that may not make sense. In VBA, an optional argument with a declared type generally needs a default value, as shown. Optional arguments must follow required arguments in the declaration.
Before running any macro that changes a workbook, save a separate copy: macro actions cannot be undone with Ctrl+Z. These examples calculate values and do not edit cells, but the reminder matters when adapting VBA code to perform workbook changes.
Scope: where a function can be used
Scope determines where code can call a function. A Public Function in a standard module can generally be called by other VBA code in the project and can be available as an Excel user-defined worksheet function. A Private Function is restricted to its own module. Use private scope for helper procedures that should not appear as general-purpose entry points.
For a worksheet function, place the code in a standard module in the workbook’s VBA project. In Excel for Windows, open the editor with Alt+F11, then use Insert > Module in the Visual Basic Editor. A workbook must be saved in a macro-enabled format such as .xlsm to retain its VBA project. Excel for Mac also supports VBA, though keyboard shortcuts can depend on the keyboard and some Excel object-model features differ. Excel for the web cannot create, edit, or run VBA macros.
Not every function needs to be public or worksheet-callable. A private helper can keep repeated logic in one place while leaving the public function as the clear interface. This is useful when a calculation has several stages: the main function can validate inputs and coordinate the work, while small helpers handle specific tasks.
Calling a VBA function in Excel
Once a public function is in a standard module, a worksheet can call it much like a built-in function. If the function above is stored in the current workbook, a cell formula can use =DiscountedPrice(100) and return 90. It can also take cell references, such as =DiscountedPrice(A2,B2), where A2 contains the list price and B2 contains a decimal discount rate such as 0.2.
A function can also be called from another VBA procedure. For example, a Sub can use the returned value in a calculation or assign it to a cell. A Sub performs actions but does not return a value in the same way a Function does. If the purpose is to compute and supply a result, use a Function; if the purpose is to carry out an operation such as formatting a range or exporting a file, a Sub is usually the clearer choice.
Worksheet user-defined functions are intended to calculate and return values. They should not be designed to change other cells, alter Excel settings, or perform actions such as displaying a dialog as part of recalculation. Excel may recalculate a worksheet function more than once or at times that are not obvious to the user. Keep worksheet functions focused on predictable calculations, and use a Sub for actions that change workbook state.
Common errors and practical design choices
Many problems come from a mismatch between the declaration and the way the function is called. Check that the number and order of required arguments match, that each argument is of a compatible type, and that the function’s return assignment uses the correct name. A misspelled function name in the assignment can leave the actual function result unset, while a missing End Function can prevent the module from compiling.
Other useful checks include:
- Declare variables and use
Option Explicitso spelling mistakes do not quietly create unintended variables. - Use clear argument names and types, and validate values when invalid input would make the result misleading.
- Keep a function focused on one calculation. Break complicated work into helper functions rather than making one procedure difficult to inspect.
- Use a consistent return type and document unusual assumptions, such as whether a rate is entered as 0.2 or 20.
- When debugging, place the cursor in the procedure and use the Visual Basic Editor’s debugging tools to step through the code and inspect values.
Worksheet UDFs have additional practical limitations. A VBA UDF is not the same as a native Excel function: the workbook must support macros, and users may need to enable macros according to their organization’s security policy. UDF recalculation behavior can also differ from built-in functions, especially when a calculation depends on information Excel does not track as an argument. Passing all relevant inputs explicitly is usually easier to maintain than hiding dependencies inside the function.
Function versus Sub: choosing the right procedure
The key distinction is whether the procedure returns a value. A Function is suited to reusable calculations, transformations, and lookups whose result can be consumed by a formula or another procedure. A Sub is suited to commands and workflows, such as clearing a selected range, creating a report, or showing a form. A function can be called by a Sub, and a Sub can call functions to obtain values, but a worksheet formula cannot use a Sub as its calculated result.
Choose the simplest procedure type that matches the job. If a routine is meant to answer “what is the result for these inputs?”, a Function is a natural fit. If it is meant to answer “what action should Excel perform?”, a Sub is generally more appropriate. Keeping those roles separate makes code easier to test, reuse, and troubleshoot.
Frequently Asked Questions
How do you write a function in VBA?
Start with Function Name(arguments) As DataType, add the statements that calculate the result, assign the result to Name, and finish with End Function. Declare the arguments and return type to make the function’s purpose explicit.
How does a VBA function return a value?
Assign the result to the function’s own name, such as TaxAmount = Price * TaxRate. The function returns the value assigned when execution finishes.
Can I use a VBA function in an Excel cell?
Yes, if it is a suitable public function in a standard module in a workbook with macros enabled. Enter it like a formula, for example =DiscountedPrice(100,0.2). A VBA function cannot be run in Excel for the web.
What is the difference between Function and Sub in VBA?
A Function returns a value and can be used in expressions or worksheet formulas when eligible. A Sub performs actions and does not return a value through a function name.
Why does my VBA function return zero or a blank value?
Check that every relevant execution path assigns a value to the function name. If no assignment occurs, VBA returns the default value for the function’s return type, such as zero for a numeric type or an empty string for a String.
Related
- Conditional Formatting Between Two Values in Excel
- Excel Goal Seek for Multiple Cells: How Solver Works
- Protect Sheet and Workbook in Excel: What’s the Difference?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox New Line: Add Line Breaks to MessagesTo add a new line in a VBA MsgBox, join the message text with vbCrLf or vbNewLine where…
- VBA Left Function: How to Return Text from the StartIn VBA, use Left(string, length) to return a specified number of characters from the beginning of a string.…
- VBA Calendar UserForm: Build a Date Picker in ExcelA VBA calendar UserForm is a custom date picker that lets someone choose a date inside an Excel…
- Excel UserForm Toolbox: What It Is and How to Use ItThe Excel UserForm Toolbox is the panel in the Visual Basic Editor (VBE) that lets you add controls—such…
- VBA Function List: Built-In Functions, Groups, and UseA VBA function list is a reference to built-in functions you can call from Visual Basic for Applications,…
- VBA Access Arrays: Declare, Loop, and Resize ThemIn Microsoft Access VBA, an array is a variable that holds multiple values of the same declared type,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.