How to return a result from a VBA function

Question

How do I return a result from a function?

For example:

Public Function test() As Integer     return 1 End Function 

This gives a compile error.

How do I make this function return an integer?

Answer 1

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 = 1 End Function 

Example usage:

Dim i As Integer i = 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 Range Set 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 = 2 End Function 

Documentation: Function Statement

Answer 2

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.