excel - 如何从VBA函数返回结果(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?

(如何使此函数返回整数？)

  ask by Mike translate from so

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:

(如果函数返回对象类型，则必须使用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 .

(如果要退出该功能，则需要明确说出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

(文档： http : //msdn.microsoft.com/zh-cn/library/office/gg264233%28v=office.14%29.aspx)