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?
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
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 If
End 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 If
End Function
Variant
and your goal is to return an array then something like ReDim test(1 to 100)
will trigger an error. Also, even though it is possible to treat basic type like Integers
like that it is considered somewhat unidiomatic. It makes the code harder to read. VBA programmers scan for lines which assign to the function name to understand what a function does. Using the function name as a regular variable needlessly obscures this.
ReDim
it. Dim test As Variant
followed by ReDim test(1 to 100)
is unproblematic. That you can't do this with the name of a variant function in the body of the function shows that it isn't true that you can use the return variable like any other variable in the body of the function, which is exactly the point that I was making. By the way, your use of exclamation marks seems over-the-top. In an internet context it seems vaguely troll-like, although there is nothing troll-like about your comment.
The below code stores the return value in to the variable retVal
and then MsgBox
can be used to display the value:
Dim retVal As Integer
retVal = test()
Msgbox retVal
Success story sharing
Range
for example), you need to useSet
just like you would if setting an object variable in a regular method. So if, for example, "test" were a function that returned a Range the return statement would look like thisset test = Range("A1")
.set
can lead to problems. I've had issues doing it without, but if I useset
I don't :).set test = Range("A1")
is exactly equivalent totest = Range("A1").Value
, where "test" is defined as a Variant, rather than a Range.