如何从 VBA 函数返回结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2781689/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return a result from a VBA function
提问by Mike
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?
如何让这个函数返回一个整数？
回答by Dan
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 Setkeyword like this:
如果函数返回一个 Object 类型，那么你必须Set像这样使用关键字：
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:
请注意，为函数名称分配返回值不会终止函数的执行。如果要退出该函数，则需要明确地说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
Documentation: http://msdn.microsoft.com/en-us/library/office/gg264233%28v=office.14%29.aspx
文档：http: //msdn.microsoft.com/en-us/library/office/gg264233%28v=office.14%29.aspx
回答by froadie
VBA functions treat the function name itself as a sort of variable. So instead of using a "return" statement, you would just say:
VBA 函数将函数名本身视为一种变量。因此，不要使用 " return" 语句，您只需说：
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.
但是请注意，这不会超出函数的范围。此语句之后的任何代码也将被执行。因此，您可以有许多赋值语句为 分配不同的值test，并且当您到达函数末尾时，无论值是什么，都将是返回的值。
回答by LimaNightHawk
Just setting the return value to the function name is still not exactlythe same as the Java (or other) returnstatement, because in java, returnexits the function, like this:
只是将返回值设置为函数名与Java（或其他）语句仍然不完全相同return，因为在Java中，return退出函数，如下所示：
public int test(int x) {
if (x == 1) {
return 1; // exits immediately
}
// still here? return 0 as default.
return 0;
}
In VB, the exactequivalent 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:
在 VB 中，如果您没有在函数末尾设置返回值，则完全等效需要两行。因此，在 VB 中，确切的推论如下所示：
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 exampleof 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
回答by Biju John
The below code stores the return value in to the variable retValand then MsgBoxcan be used to display the value:
下面的代码将返回值存储到变量中retVal，然后MsgBox可用于显示值：
Dim retVal As Integer
retVal = test()
Msgbox retVal
