An Excel VBA function returns a value by assigning that value to the function’s own name; VBA does not use a return statement. For example, after you place a function in a standard VBA module, a worksheet formula such as =DoubleIt(A2) can display the value that the function assigns to DoubleIt.
What “return” means in a VBA function
A function is a VBA procedure designed to produce a result. Its name serves two purposes: it identifies the procedure, and inside the procedure it acts as the variable that holds the result to be sent back to the caller. The caller might be a worksheet formula, another VBA procedure, or the Immediate window in the Visual Basic Editor.
This differs from languages that use a dedicated statement such as return value. In VBA, writing FunctionName = value sets the function’s return value. Execution then continues until the function reaches End Function, unless it exits earlier. A function can assign its name more than once; the last assignment reached before it ends is the value returned.
A Sub procedure is different: it performs actions but does not return a value. Use a Function when the caller needs an answer, such as a calculation or text result. Use a Sub when the goal is an action, such as formatting cells or creating a report.
Basic syntax and a working example
The general structure declares the function name, its inputs, and the type of value it returns. This example doubles a number:
Public Function DoubleIt(ByVal value As Double) As Double
' Assign the result to the function name.
DoubleIt = value * 2
End Function
With the number 7 in cell A2, enter =DoubleIt(A2) in another cell; the result is 14. The worksheet formula supplies the argument A2, VBA multiplies that value by 2, and the assignment to DoubleIt makes the result available to Excel. ByVal means the function receives a copy of the argument rather than changing the caller’s variable.
To add the function, open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and paste the code into the standard module. On a Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut is Option+F11, sometimes with Fn as well depending on the keyboard. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code.
How VBA and the worksheet use the returned value
Excel can call a suitably written public function in a standard module as a user-defined function (UDF). In the worksheet, its name is used like a built-in function: arguments go in parentheses, and commas separate arguments. For example, a function that accepts a price and a rate could return a calculated amount to the cell containing its formula. The displayed cell value is the function result; the code itself does not place that result in a cell.
VBA code can also call a function and store its result in a variable. The declared return type matters because it controls what kind of value the function can provide. Here, the function takes a number and returns text:
Public Function GradeLabel(ByVal score As Double) As String
' Return a label based on the supplied score.
If score >= 90 Then
GradeLabel = "A"
ElseIf score >= 80 Then
GradeLabel = "B"
Else
GradeLabel = "Below B"
End If
End Function
With 84 in A2, the worksheet formula =GradeLabel(A2) returns B. The branch that runs assigns a string to the function name, and VBA ends the procedure after reaching End Function. Notice that the code must assign a result on every path where a meaningful answer is expected; otherwise, the function may return its type’s default value instead.
Choosing a return type
Declare the return type after the closing parenthesis in the function signature, as in As Double or As String. A specific type makes the function’s intended output clearer and can help catch mistakes. Common choices include:
Doublefor many decimal calculations, andLongfor whole numbers that may exceed the smallerIntegerrange.Stringfor text, andBooleanforTrueorFalseresults.Datefor dates and times, andCurrencyfor fixed-decimal currency calculations where that type is appropriate.Variantwhen the function needs to return different kinds of values or an Excel error value.
If a function’s return type is omitted, VBA treats the return value as a Variant. That flexibility can be useful, but it can also make unexpected values harder to spot. For example, a function declared As Double should return a number that VBA can convert to a Double. Returning incompatible text can cause a type mismatch rather than a useful worksheet result.
VBA functions can also return objects to other VBA code, but the assignment uses Set, as object variables do. That is not the same as returning a normal cell value: an Excel worksheet UDF is expected to provide a value Excel can use in a cell, not an object such as a Range. Keep worksheet-facing functions focused on values such as numbers, text, dates, Boolean results, or supported error values.
Returning errors, arrays, and early exits
A function can return an Excel error value when its output type permits one. For example, a function declared As Variant can assign a VBA error value such as CVErr(xlErrValue); Excel then displays the corresponding error in the worksheet. A function declared As Double cannot simply return that error value as though it were a number. Decide whether an invalid input should produce a worksheet error, a special text result, or another defined outcome, and document that choice.
VBA functions can also return arrays, commonly by using a Variant return type and assigning an array to the function name. The way Excel displays an array result depends on the Excel version and how the UDF is entered. If a formula does not spill as expected, check the function’s return shape and the workbook’s Excel version; older Excel workflows may require entering the formula over a selected range as an array formula. A simple scalar result is usually easier to maintain when an array is not necessary.
Exit Function ends the function immediately. It does not itself specify the result, so assign the intended value first. For instance, a function might detect a blank input, assign a chosen result, and then use Exit Function to avoid running the remaining calculation. Be careful to cover all branches: an early exit without an assignment can leave the function returning its default value.
Common problems and how to diagnose them
- Excel shows
#NAME?: Check the spelling of the function name, confirm that the code is in a standard module, and make sure macros are enabled under the workbook’s security policy. A function stored in a worksheet or workbook object module may not be available to a worksheet formula in the same way. - The function returns zero or an empty result: Look for a branch that reaches
End Functionwithout assigning the function name. The default result depends on the declared return type; it is not an automatic calculation of the procedure’s last expression. - You see a type mismatch or conversion error: Compare the declared return type and argument types with the actual values. Cells can contain text, blanks, or worksheet errors even when they appear to hold ordinary numbers.
- The result does not update: Excel generally tracks worksheet cells supplied as formula arguments. If a UDF reads other cells directly from VBA instead of receiving them as arguments, Excel may not know those cells are dependencies. Pass dependent cells as arguments where practical.
Application.Volatilecan make a UDF recalculate whenever calculation occurs, but using it broadly can slow a workbook. - The function works in the editor but not in a cell: Worksheet UDFs have limits. They are meant to calculate and return values, not perform actions such as changing cell formatting, editing other cells, or displaying dialogs. Use a Sub or another appropriate workbook feature for actions.
When debugging, place the cursor inside the function in the Visual Basic Editor and use F8 on Windows to step through the code one line at a time. On a Mac, use the corresponding Step Into command in the editor; keyboard mappings can differ. Inspect the arguments and the point where the function name receives its value. For a worksheet function, also check the formula bar to confirm the actual formula and inputs.
Function versus Sub: choosing the right procedure
The central difference is whether the caller needs a returned value. A Function supplies a result, so it is suitable for a worksheet calculation such as a custom conversion or classification. A Sub does not return a function value; it is suitable for a macro that changes formatting, copies data, or creates a new sheet.
A worksheet UDF should normally be predictable: given the same arguments and workbook state, it should calculate the same result without changing the workbook. A macro can make changes, but those changes cannot be undone with Ctrl+Z in the usual Excel undo history. Before running any macro that changes a workbook, save a copy. This caution is useful even when the project also contains worksheet functions, because the workbook may include other procedures that do make changes.
Practical setup and workbook considerations
For a function to be available in a worksheet, its procedure should generally be declared Public and stored in a standard module. Give it a descriptive name that does not conflict with a cell reference or an existing Excel function. If a function is intended only for use by other VBA code, it can be private or placed in a different module context, but that changes whether users can call it directly from cells.
VBA code is not stored in a standard .xlsx workbook. Save a workbook containing VBA as .xlsm, or use another macro-enabled format suited to the project. When sharing the file, recipients may need to allow macros according to their organization’s security rules. Do not tell readers to enable all macros indiscriminately: only enable code from a source they trust.
Excel for the web can open and edit many workbook contents, but it does not run VBA macros. A workbook’s VBA UDF therefore needs desktop Excel to calculate through its VBA code. If recipients must use the function in a browser, consider replacing the VBA logic with a native worksheet formula or another supported solution.
Frequently Asked Questions
Does VBA have a return statement?
No. Assign the result to the function’s own name, such as DoubleIt = value * 2. Use Exit Function if you need to stop early, but assign the intended result before exiting.
How do I return a value from a VBA function to a cell?
Put a public function in a standard module, then enter its name as a worksheet formula, for example =DoubleIt(A2). The function must return a value Excel can use, and the workbook must be opened in desktop Excel with its VBA code available.
What happens if I do not assign the function name?
The function returns the default value for its declared type rather than the result of the last calculation expression. That can look like zero, an empty string, or another default, so assign the function name on every relevant code path.
Can a VBA function return more than one value?
It can return an array or a Variant containing an array, but how that result is entered and displayed depends on Excel’s version and array-formula behavior. For a straightforward worksheet UDF, returning one value per call is often clearer.
Can a worksheet function change another cell?
A worksheet UDF is intended to calculate a result, not edit other cells or perform workbook actions. Use a Sub macro for changes, and save a copy before running a macro because Ctrl+Z cannot undo its VBA actions.
Why does my VBA function not work in Excel for the web?
Excel for the web does not execute VBA. Open the macro-enabled workbook in desktop Excel to use the VBA function, or replace it with a worksheet formula or a non-VBA approach if browser use is required.
Related
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Cell to Range: How to Reference Cells in ExcelIn Excel VBA, a cell is already a one-cell Range object: use Set target = Worksheets("Sheet1").Range("A1") to store…
- VBA Dictionary Documentation: Setup, Methods, Properties & ExamplesFor VBA Dictionary documentation, start with Microsoft’s Dictionary object reference: it describes the Scripting.Dictionary object, which stores unique…
- Excel Macro Filter: How to Automate Data FilteringTo filter a worksheet with a macro, use VBA’s AutoFilter method on a range that includes its header…
- TG Macro: How to Record and Run Macros on Android SafelyTo make a macro with TG Macro, open the Android app, grant only the permissions it needs, record…
- Add a Hyperlink to a VBA MsgBox in ExcelYou cannot add a genuinely clickable hyperlink inside Excel VBA’s built-in MsgBox: it displays plain text and its…
- VBA Scripting.Dictionary: How It Works and How to Use ItA VBA Scripting.Dictionary stores data as unique keys paired with values, so code can look up an item…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.