VBA Function in Excel — Return Values & Custom Worksheet Functions (UDF)

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-07

TL;DR — A Function is a procedure that hands a value back. You return that value by assigning it to the function's own name — there is no Return keyword in VBA. And because a Function returns a value, you can type it straight into a worksheet cell as a custom formula (a UDF):

Function AddTax(price As Double, rate As Double) As Double
    AddTax = price * (1 + rate)     ' assign to the function's NAME — this is the return
End Function

In any cell:   =AddTax(A2, 0.2)     ' your own formula, sitting next to SUM and VLOOKUP

That one capability — living inside a cell — is the entire reason Functions exist alongside Subs. Everything below builds on it.

The mental model: a Function is a Sub that reports back

A Sub is a worker who does a task and walks away. A Function is a worker who does a task and then tells you the answer. Same machinery — a named block of code, arguments in — with one addition: a value comes out.

That output is what makes a Function usable where a Sub never can be: inside a worksheet cell. Excel's grid is built to display the result of an expression, so anything that returns a value — SUM, XLOOKUP, or your AddTax — can sit in a cell. A Sub returns nothing, so Excel has nothing to put in the cell; type =GreetUser() and you get #NAME?. Hold onto that picture and the Sub-vs-Function decision answers itself.

The one rule: return by assigning to the function's name (not Return)

This is the bug that catches everyone coming from Python, JavaScript, or C#:

Function Discount(price As Double) As Double
    Discount = price * 0.9      ' ✅ VBA returns by assigning to the function's own name
End Function

Function Broken(price As Double) As Double
    Return price * 0.9          ' ❌ not how VBA works — silently returns 0
End Function

Return exists in VBA, but only as the partner of the ancient GoSub — it does not return a value. If you write Return (or simply forget to assign the name at all), the function compiles fine and quietly returns the default for its type — 0, "", or Empty. No error, just wrong answers. If a UDF keeps returning 0, you almost certainly forgot to assign to its name.

You can assign the name as many times as you like (it's a normal variable inside the function); the value it holds when the function ends is what comes back:

Function Grade(score As Long) As String
    Grade = "Fail"              ' default
    If score >= 50 Then Grade = "Pass"
    If score >= 80 Then Grade = "Distinction"
End Function

Sub vs Function — the one-line decision

Forget the long comparison tables. The whole choice is one question:

Does the caller need an answer back? Yes → Function. No → Sub.

A useful tell: if you find yourself making a Sub stuff its result into a global variable so another procedure can read it, you wanted a Function. Return the value properly instead of smuggling it through a global.

Make it a worksheet function (UDF) — the killer feature

Put the Function in a standard module (Insert → Module, not a sheet or ThisWorkbook module), and it instantly becomes available in the grid and in the formula AutoComplete:

Function InitialsOf(fullName As String) As String
    Dim parts() As String
    parts = Split(Trim(fullName), " ")
    InitialsOf = Left(parts(0), 1) & Left(parts(UBound(parts)), 1)
End Function

=InitialsOf("Ada Lovelace")  →  "AL"

This is where VBA pays off for non-programmers around you: you write the logic once, and your colleagues use it like any built-in function, no code in sight.

The rule that breaks every new UDF: a worksheet function can only return to its own cell

Here is the limitation that generates a thousand forum posts. When a Function is called from a cell, Excel runs it in a protected mode where it cannot change anything — it can't write to other cells, can't set a colour, can't rename a sheet, can't MsgBox. It may only compute and return the value for the cell that called it.

Function ColorMe(c As Range) As String
    c.Interior.Color = vbYellow   ' ❌ silently does NOTHING when called from a cell
    ColorMe = "done"
End Function

People write this, see the text appear but no colour, and conclude VBA is broken. It isn't — UDFs are pure by design. If you need to change the sheet, that's a job for a Sub (triggered by a button or an event), not a UDF. Knowing this line saves hours: cell formula → compute and return only; change the workbook → use a Sub.

Optional and typed arguments make a UDF feel native

Two touches make a custom function pleasant to use:

Function Net(gross As Double, Optional rate As Double = 0.2) As Double
    Net = gross / (1 + rate)       ' rate defaults to 0.2 if the caller omits it
End Function

Optional with a default lets =Net(A2) and =Net(A2, 0.19) both work. Declaring the return type (As Double, As String, As Boolean) instead of leaving it Variant makes the function faster and the intent obvious.

The opinion: don't reinvent a built-in

VBA Functions are for logic Excel doesn't already have. If WorksheetFunction.SumIf, TEXTJOIN, or XLOOKUP already does the job, call that — a native function is faster, recalculates correctly, and needs no macro-enabled workbook. Reserve your own Functions for genuinely custom rules: a company-specific tax band, a messy-string parser, a domain calculation no built-in covers. A UDF that wraps SUM is a liability; a UDF that encodes your business rule is an asset.

Common Function mistakes (and the fix)

When the formula logic is the real work — skip the VBA

A custom Function is the right tool for one reusable calculation. But when the task is "reconcile these two exports, flag the rows that don't match, and total the differences by month," you don't want to hand-write and maintain that. ExcelMaster Agent takes that sentence in plain English and produces the result — no UDF to debug, no macro-enabled file to ship. Try it free →

Related guides

• VBA Sub in Excel — Procedures, Calling & Why Your Macro Is Just a Sub
• VBA ByRef vs ByVal — Why Your Variable Changed After a Call
• VBA MsgBox in Excel — Yes/No, Buttons & the Brackets Rule
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

What is a Function in VBA? A Function is a named procedure that performs work and returns a value to the code that called it. You define it with Function Name(args) As Type … End Function and return the result by assigning it to the function's own name.

How do I return a value from a VBA function? Assign the value to the function's name: inside Function Total() As Double, write Total = 42. VBA has no Return statement for this — Return belongs to the old GoSub and does not pass a value back. The last value assigned to the name before the function ends is what gets returned.

Why does my VBA function return 0 or blank? Because you never assigned a value to the function's name (or you used Return, which doesn't work in VBA). The function then returns the default for its declared type — 0, "", or Empty. Add FunctionName = result before End Function.

How do I create a custom function (UDF) in Excel? Put a Function in a standard module (Insert → Module), give it a return type, and assign the result to its name. Then type =YourFunction(...) in any cell. It appears in formula AutoComplete like a built-in function.

Why can't my UDF change other cells or colours? When Excel calls a Function from a worksheet cell, it runs in a restricted mode that can only return a value — it cannot modify the workbook (no writing to cells, formatting, or MsgBox). To change the sheet, use a Sub triggered by a button or event instead of a UDF.

When should I use a Function instead of a Sub? Use a Function when the caller needs a value back — a calculation, a lookup, a cleaned string — or when you want to call it from a worksheet cell. Use a Sub when the procedure only does something (formats, exports, deletes) and returns nothing.