How to return a result from a VBA function

For non-object return types, you have to assign the value to the name of your function, like this:

Public Function test() As Integer    test = 1End Function

Example usage:

Dim i As Integeri = test()

If the function returns an Object type, then you must use the Set keyword like this:

Public Function testRange() As Range    Set testRange = Range("A1")End Function

Example usage:

Dim r As RangeSet r = testRange()

Note that assigning a return value to the function name does not terminate the execution of your function. If you want to exit the function, then you need to explicitly say Exit Function. For example:

Function test(ByVal justReturnOne As Boolean) As Integer    If justReturnOne Then        test = 1        Exit Function    End If    'more code...    test = 2End Function

Documentation: Function Statement

VBA functions treat the function name itself as a sort of variable. So instead of using a "return" statement, you would just say:

test = 1

Notice, though, that this does not break out of the function. Any code after this statement will also be executed. Thus, you can have many assignment statements that assign different values to test, and whatever the value is when you reach the end of the function will be the value returned.

Just setting the return value to the function name is still not exactly the same as the Java (or other) return statement, because in java, return exits the function, like this:

public int test(int x) {    if (x == 1) {        return 1; // exits immediately    }    // still here? return 0 as default.    return 0;}

In VB, the exact equivalent takes two lines if you are not setting the return value at the end of your function. So, in VB the exact corollary would look like this:

Public Function test(ByVal x As Integer) As Integer    If x = 1 Then        test = 1 ' does not exit immediately. You must manually terminate...        Exit Function ' to exit    End If    ' Still here? return 0 as default.    test = 0    ' no need for an Exit Function because we're about to exit anyway.End Function 

Since this is the case, it's also nice to know that you can use the return variable like any other variable in the method. Like this:

Public Function test(ByVal x As Integer) As Integer    test = x ' <-- set the return value    If test <> 1 Then ' Test the currently set return value        test = 0 ' Reset the return value to a *new* value    End IfEnd Function 

Or, the extreme example of how the return variable works (but not necessarily a good example of how you should actually code)—the one that will keep you up at night:

Public Function test(ByVal x As Integer) As Integer    test = x ' <-- set the return value    If test > 0 Then        ' RECURSIVE CALL...WITH THE RETURN VALUE AS AN ARGUMENT,        ' AND THE RESULT RESETTING THE RETURN VALUE.        test = test(test - 1)    End IfEnd Function