VBA Function Return String: Syntax and Examples

VBA & macros · Updated November 22, 2026

Yes. A VBA function returns a string when its declaration ends in As String; inside the function, assign the value to the function’s own name. For example, Function MakeLabel() As String returns whatever text is assigned to MakeLabel.

The Basic Syntax for Returning a String

VBA does not use a separate return statement like some programming languages. Instead, the function name acts as a variable that holds the result. A simple function looks like this:

Function MakeLabel() As String
    ' Set the value that the function will return.
    MakeLabel = "Ready"
End Function

The declaration’s As String clause specifies the function’s return type. The assignment MakeLabel = "Ready" sets the result, and VBA supplies that result when execution reaches End Function. The function name on the left side of the assignment must match the declared function name. If you omit the assignment, the function does not return the text you may have intended; for a String function, its default result is an empty string.

VBA function names are not case-sensitive, so makelabel and MakeLabel refer to the same function. It is still good practice to use the same spelling and capitalization throughout a procedure, because consistent names are easier to read and maintain.

How the Return Value Is Set

A function can assign its result at any point while it runs. When the function reaches End Function, the last value assigned to the function name is the value returned to the caller. This means a later assignment replaces an earlier one. A function can also use Exit Function to stop early after assigning a result.

Function Greeting(ByVal personName As String) As String
    ' Return a fallback when the supplied name is blank.
    If Len(Trim$(personName)) = 0 Then
        Greeting = "Hello, guest"
        Exit Function
    End If

    Greeting = "Hello, " & Trim$(personName)
End Function

In this example, Trim$ removes leading and trailing spaces, and Len checks whether anything remains. A blank or whitespace-only argument takes the early-exit path. Otherwise, the function joins a greeting, a comma and the cleaned name with the concatenation operator &. The dollar sign in Trim$ indicates the String-returning version of the function; Trim also exists and returns a Variant.

Use Exit Function when it makes a clear decision path easier to follow, not as a substitute for assigning a result. If execution exits before assigning the function name, the result remains its default value. That may be an empty string, which can hide an incomplete branch unless you deliberately choose and document that behavior.

A Complete Example in the VBA Editor

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, use Option+F11; depending on the keyboard, you may need Fn+Option+F11. In the editor, choose Insert > Module, then put a function in the standard module. A module-level declaration such as Option Explicit is also useful because it requires variables to be declared.

Option Explicit

Function FullName(ByVal firstName As String, _
                  ByVal lastName As String) As String
    Dim cleanFirst As String
    Dim cleanLast As String

    ' Remove spaces around each part of the name.
    cleanFirst = Trim$(firstName)
    cleanLast = Trim$(lastName)

    ' Join both names, avoiding an extra space if one is blank.
    If Len(cleanFirst) = 0 Then
        FullName = cleanLast
    ElseIf Len(cleanLast) = 0 Then
        FullName = cleanFirst
    Else
        FullName = cleanFirst & " " & cleanLast
    End If
End Function

The function declares two local String variables, cleans the inputs and handles missing parts before joining them. For example, FullName("Avery", "Chen") returns Avery Chen, while FullName("Avery", "") returns Avery without a trailing space. The ByVal keyword means the function receives copies of the argument values, so changing a parameter inside the function does not change the caller’s variable.

VBA procedures can be placed in different module types, but a function intended for use in a worksheet cell should normally be in a standard module. Save the workbook in a macro-enabled format such as .xlsm if it needs to retain its VBA code. Before running any macro, save a separate copy of the workbook: macro actions cannot be undone with Ctrl+Z. On Mac, use Command+Z for ordinary undo, but it also cannot reverse a macro’s changes.

Calling the Function from VBA or a Worksheet

Code in another VBA procedure can call the function and store its result in a String variable. The Call keyword is not needed when you use the returned value in an expression.

Sub ShowGreeting()
    Dim message As String

    ' Capture the function result and display it.
    message = Greeting("Morgan")
    MsgBox message
End Sub

A public function in a standard module can also be used as a user-defined function in an Excel cell, provided it uses worksheet-compatible arguments and does not try to control Excel’s interface. For example, with a name in cell A2, enter =Greeting(A2) in B2. If A2 contains Morgan, the result is Hello, Morgan. The cell formula calls the VBA function; the return value is displayed in the cell.

Worksheet UDFs are intended to calculate and return values. They should not change other cells, open dialogs or perform unrelated actions while Excel recalculates them. A function that changes workbook state can produce confusing results because Excel may call it again when dependencies or calculation settings change. Also, Excel for the web can display workbooks containing VBA but cannot create, edit or run VBA macros; use the desktop Excel app for this code.

Choosing a String Return Type and Handling Text

Declare a function As String when every successful result should be text. This makes the function’s contract clear to readers and to VBA, and it avoids relying on implicit Variant behavior. A String function can return a name, label, formatted message, code or an empty string. It cannot return a number as a numeric value; if the caller needs a number for arithmetic, declare an appropriate numeric return type instead.

Be precise about what “no result” means. An empty string, "", is a valid String containing zero characters. It differs conceptually from a space, which contains one character, and from the text "0". In worksheet cells, an empty-string result often looks blank, although it is not the same as a truly empty cell for every Excel operation. If downstream formulas or code distinguish these cases, define the function’s expected behavior and test it.

VBA’s String type cannot hold the special Null value. Assigning Null to a String can raise run-time error 94, Invalid use of Null. If an input may be Null because it comes from a database or Variant, check it before conversion or concatenation. A function that needs to represent both text and a missing or database-null state may need a Variant return type and explicit handling, rather than pretending every outcome is a String.

For text concatenation, use & rather than +. The plus operator can perform numeric addition or behave differently when values are Null, while ampersand clearly expresses joining text. Convert non-string values deliberately when formatting matters; dates, numbers and regional settings can otherwise affect how a value becomes text.

Arguments, Scope, and Naming

A function can accept zero or more arguments, and each argument can have its own type. Marking an argument ByVal is a clear choice when the function only needs to read the supplied value. VBA’s default is ByRef, which allows a procedure to change the caller’s variable in some circumstances. That side effect is usually surprising for a text-building function, so explicitly choosing ByVal makes intent easier to understand.

Functions meant to be called from another module or from a worksheet are generally declared Public, or left without a visibility keyword in a standard module, where they are public by default. A Private Function is limited to its module and will not be available as a worksheet function. Give the function a distinctive name and avoid names that conflict with built-in VBA or Excel functions. If Excel reports that a function cannot be found, check its spelling, visibility, module location and whether macros are enabled.

Common Problems and How to Diagnose Them

  • The function appears to return nothing: Look for an assignment to the function name on every intended execution path. A variable assignment such as result = "Done" does not set the return value unless the function later assigns FunctionName = result.
  • The function returns an unexpected earlier value: Search for multiple assignments to the function name. The last assignment reached is the returned value, so a later branch may overwrite the result.
  • A worksheet cell shows an error or does not recognize the function: Confirm that the function is public and in a standard module, the workbook is open with macros allowed, and the function’s arguments are supported in a worksheet formula. Check the formula name and argument count as well.
  • Text has extra spaces or missing separators: Decide whether to trim inputs, then add separators explicitly. Concatenation does not insert spaces automatically. Test blank inputs as well as normal ones.
  • Compilation stops at a declaration: Confirm that the return type is written after the closing parenthesis, as in Function LabelText() As String, and that each variable has a valid declaration. Use Debug > Compile VBAProject in the Visual Basic Editor to locate compile-time issues.

During debugging, set a breakpoint on the assignment to the function name and step through the function with F8 in the Windows Visual Basic Editor. On Mac, the function-key behavior can depend on keyboard settings, and the editor’s Debug menu provides stepping commands. The Locals window or an immediate-window check can help reveal whether a parameter contains a blank, an unexpected space or an invalid value.

When a Function Is Not the Right Choice

Use a Function when a caller needs a result, such as text to display or pass onward. Use a Sub when the procedure’s purpose is to perform an action and no returned value is needed. A Sub cannot be used as a value-producing worksheet formula. Conversely, a worksheet UDF is not a replacement for a macro that formats cells or changes workbook structure; keep calculation and workbook-changing actions separate.

If the task is simply to join or clean text in a worksheet, an ordinary Excel formula may be easier to share because it does not require VBA or macro permissions. For a reusable operation that needs custom logic, a VBA String function can make the rule consistent across many calls. The choice depends on whether the logic must be custom, whether the workbook can use macros, and whether other users need to run it in Excel for the web.

Frequently Asked Questions

How do I return a string from a VBA function?
Declare the function with As String, then assign the desired text to the function’s name. For example, inside Function StatusText() As String, use StatusText = "Complete".

Does VBA use a Return statement?
No. VBA returns a function result through an assignment to the function name. Use Exit Function when you need to leave early, after setting the intended return value.

Can a VBA String function return a blank?
Yes. Assign "" to return a zero-length string. This is different from returning a space or a true empty worksheet cell, so check how later formulas or code treat the result.

Can I use a VBA function in an Excel cell?
Yes, if it is an eligible public function in a standard module in a macro-enabled workbook and macros are enabled. Enter it as a worksheet formula, such as =Greeting(A2). Excel for the web does not run VBA.

Why is my function returning an empty string?
Most often, the code never assigns a value to the function name on the path that ran. Check conditional branches and early exits, and remember that assigning a similarly named local variable does not assign the function result.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.