In Excel VBA, finish a function procedure with End Function. To leave the function early from inside its code, use Exit Function; these statements are not interchangeable, and neither should be confused with the separate VBA statement End.
What End Function Means in VBA
A VBA function is a named procedure that can calculate or retrieve a value. It begins with a Function declaration and ends with End Function. The closing statement tells VBA that the function’s code is complete. It is required even when the function contains only one line, and it does not itself specify what value the function returns.
For example, a custom function can multiply a price by a tax rate and return the total:
Public Function AddTax(ByVal amount As Double, ByVal rate As Double) As Double
' Return the amount after tax.
AddTax = amount * (1 + rate)
End Function
Here, Function starts the procedure, AddTax is its name, the text in parentheses defines its inputs, and As Double declares the type of result. Assigning a value to AddTax sets the function’s return value. End Function then closes the procedure.
In a worksheet, enter =AddTax(A2,B2) in a cell if the amount is in A2 and the tax rate is in B2. For instance, an amount of 100 and a rate of 0.08 return 108. Save a copy of the workbook before running any macro: VBA macros cannot be undone with Ctrl+Z.
How a VBA Function Returns a Value
Unlike some languages, VBA does not use a separate return statement for a normal function result. Instead, assign the result to the function’s own name. The function returns the last value assigned to that name when it finishes. That assignment can appear before the final line or, where appropriate, on multiple branches of the code.
For example, the following function checks for a zero denominator and returns an Excel division-by-zero error in that case:
Public Function SafeRatio(ByVal numerator As Double, ByVal denominator As Double) As Variant
' Return an Excel error rather than divide by zero.
If denominator = 0 Then
SafeRatio = CVErr(xlErrDiv0)
Exit Function
End If
SafeRatio = numerator / denominator
End Function
The function’s return type is Variant because it may return either a number or an Excel error value. When the denominator is nonzero, the final assignment calculates the ratio; when it is zero, the function assigns the error and exits before reaching that calculation. A common bug is to calculate a value in a local variable but never assign it to the function name. In that case, VBA returns the function’s default value instead, such as zero for a numeric type or an empty string for a string type.
For a function that returns an object, use Set when assigning the object to the function name. For example, a function returning a worksheet would use an assignment such as Set FindSheet = ThisWorkbook.Worksheets(sheetName). Object-returning functions also need a plan for a missing object, since a failed lookup can raise an error or leave the result as Nothing.
End Function Versus Exit Function
End Function is the procedure’s required closing marker, normally placed on its own line after all of its statements. Exit Function is an optional instruction inside the procedure that immediately leaves it. Use the latter when a condition means that the remaining code should not run—for example, after returning an error or handling an invalid input.
- Use
End Functiononce to close the function’s definition. - Use
Exit Functiononly when a particular path should stop before the normal end. - Assign the return value first if the early exit should return a specific result.
A function can contain more than one Exit Function for separate conditions, but it still has one End Function at the bottom. Adding an early exit does not remove the need for the closing marker. Conversely, placing End Function inside an If block is not how to make a conditional return; close the procedure once, after the full block structure.
End Function Versus the End Statement
The word End by itself is a separate VBA statement. It abruptly stops execution of the project, rather than simply closing the current function. It can interrupt other running code and reset module-level state, so it is generally not a suitable way to exit a function or stop a macro during ordinary work.
This difference explains a frequent search question: should you type “End function” or just “End”? For a function, the proper procedure terminator is the two-word statement End Function. For a normal early return, use Exit Function. Do not use the bare End statement as a shortcut: it does not mean “finish this function neatly.”
Other VBA procedure types have their own closing statements. A sub procedure ends with End Sub, a property procedure with a matching form such as End Property, and control structures such as If blocks also have their own closing keywords. VBA’s editor often inserts the matching terminator when you complete a declaration, but it is still useful to recognize which statement belongs to which structure.
Writing and Running a Function in Excel
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, Option+F11 may work; some keyboards require Fn as well. You can also open the editor from the Developer tab by choosing Visual Basic. If the Developer tab is hidden, enable it through Excel Options on Windows or Excel’s ribbon settings on Mac.
- In the editor, choose Insert > Module to create a standard module.
- Type or paste a complete function, including its declaration and
End Function. - Return to the worksheet and call a public function from a cell, or call it from another VBA procedure.
- Save a workbook containing VBA as an Excel Macro-Enabled Workbook (
.xlsm) if you need to keep the code.
A custom worksheet function is often called a user-defined function, or UDF. A public function in a standard module can generally be used in a worksheet formula, provided its arguments and return value are appropriate for worksheet use. Excel for the web can open and edit many workbooks, but it does not create, run, or edit VBA macros; use the desktop Excel application for this code. Excel for Mac supports VBA in the desktop application, though some features and keyboard combinations can differ from Windows.
Choosing Between a Function and a Sub
Use a Function when the code is meant to produce a value, such as a calculation, a text result, or a lookup result. That value can be used by another VBA procedure or, for a suitable public UDF, in a worksheet formula. Use a Sub when the main purpose is to perform an action, such as formatting cells, copying data, or displaying a message. A sub does not return a value through a function name and closes with End Sub.
For example, a tax calculation is a natural function because the caller needs a number. A routine that applies formatting to a selected range is usually a sub because its purpose is to change the workbook rather than supply a result. A function can have side effects in VBA, but worksheet UDFs are most reliable when they calculate and return a value instead of trying to alter other cells or control Excel’s interface.
Do not confuse a function’s ability to return a value with a macro’s ability to change the workbook. A UDF entered in a worksheet formula is not a general replacement for a macro. Excel restricts what worksheet functions can do, and a function that attempts to modify cells outside its formula cell is not a dependable way to automate those changes.
Common End Function Errors and Fixes
- “End Function without Function” or a related compile error: Check for a missing, misspelled, or mismatched
Functiondeclaration. Also check that everyIf, loop, and other block has its own correct closing statement. - “Expected End Function”: The function may be missing its final terminator. Add
End Functionafter the function’s statements, not inside a conditional block. - The function returns zero or blank: Confirm that every successful path assigns the intended value to the function’s name. A local variable assignment alone does not set the function result.
- The procedure will not run from a worksheet: Check that the function is public, is in a standard module, and is saved in a macro-enabled workbook. Also confirm that the formula uses the correct function name and argument order.
- The workbook opens without the expected macro behavior: Excel may have blocked macros because of its security settings or the file’s source. Only enable macros in files you trust; do not lower security protections simply to run unknown code.
When the editor highlights a line after a compile error, inspect the surrounding procedure as well as that exact line. A missing End If or an incorrectly nested block can make the closing End Function appear to be the problem even though the actual mismatch occurs earlier. In the Visual Basic Editor, use Debug > Compile VBAProject to check the project for compile errors.
Frequently Asked Questions
Do I need End Function in every VBA function?
Yes. Every VBA function procedure must be closed with End Function, including a short function with only one executable line.
Can I use Return to end a VBA function?
VBA does not use a standalone Return statement to set a function’s result. Assign the result to the function name, then let the procedure reach End Function, or use Exit Function for an early exit.
What is the difference between End Sub and End Function?End Sub closes a sub procedure, which does not return a value through its name. End Function closes a function procedure, which can return a value.
Can a function have more than one Exit Function?
Yes. You can use multiple early exits for different conditions, but each path that exits should set an appropriate result if the caller needs one. The function still needs its final End Function.
Does End Function run the code below it?
No. It marks the end of that function. Code that should run as part of the function must appear before the terminator; code for another procedure must be placed in that separate procedure.
Key Takeaway
Use End Function to close a VBA function, assign the intended result to the function’s name, and use Exit Function only when you need to leave early. The bare End statement is different and can stop execution abruptly. Keeping those three cases distinct makes function code easier to compile, understand, and maintain.
Related
- Excel Named Ranges in Data Validation: How to Use Them
- Excel Unable to Remove Duplicates? Common Causes and Fixes
- Excel Text to Columns: How to Undo and Restore Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Array to Sheet: Write Data with Range.ValueTo write a VBA array to an Excel worksheet, assign a two-dimensional array to a range of the…
- Excel VBA Array Maximum Size: Limits and Practical TipsThere is no single, guaranteed maximum number of elements for an Excel VBA array: its usable size depends…
- VBA Macro Error Handling: Patterns, Examples, and FixesVBA macro error handling lets a procedure respond to runtime problems instead of stopping without explanation. Use On…
- VBA MsgBox Scroll: Limits and Scrollable AlternativesNo: VBA’s built-in MsgBox has no setting or argument that adds a scrollbar. For long text, use a…
- Excel VBA: Check Whether a Worksheet ExistsTo check whether a worksheet exists in Excel VBA, loop through the target workbook’s Worksheets collection and compare…
- Import Multiple Files Using VBA in Excel: A Practical GuideYes. In Excel for Microsoft 365 on Windows, VBA can loop through files in a selected folder, open…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.