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: http://msdn.microsoft.com/en-us/library/office/gg264233%28v=office.14%29.aspx