如何从一个VBA函数返回一个结果

对于非对象返回类型，必须将值赋给函数名，如下所示:

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

使用示例:

Dim i As Integer
i = test()

如果函数返回Object类型，则必须像这样使用Set关键字:

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

使用示例:

Dim r As Range
Set r = testRange()

注意，将返回值赋给函数名并不会终止函数的执行。如果你想退出函数，那么你需要显式地说Exit Function。例如:

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

文档:函数声明

VBA函数将函数名本身视为一种变量。因此，与其使用"return"语句，你只需说:

test = 1

但是请注意，这并没有跳出函数。在此语句之后的任何代码也将被执行。因此，你可以有许多赋值语句，将不同的值赋给test，当你到达函数的末尾时，无论值是什么，都将是返回的值。

只是将返回值设置为函数名仍然不是完全，与Java(或其他)return语句相同，因为在Java中，return退出函数，如下所示:

public int test(int x) {
if (x == 1) {
return 1; // exits immediately
}


// still here? return 0 as default.
return 0;
}

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

既然是这种情况，知道可以像使用方法中的任何其他变量一样使用return变量也是很好的。是这样的:

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

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