从 VBA 中的函数返回数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31564846/
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
return array from function in VBA
提问by Rik
All,
全部，
I would like to write a function to return an array of integers so I can index them, but I am not aware of the syntax for VBA. Here is the pseudo code:
我想编写一个函数来返回一个整数数组，以便我可以索引它们，但我不知道 VBA 的语法。这是伪代码：
function getStats() as integer
dim returnVal(4) as integer
returnVal(0)=c2percent14
returnVal(1)=c3percent14
returnVal(2)=c4percent14
returnVal(3)=c5percent14
getStats=returnVal
end function
msgbox getStats(3)
where these values are all integers, or should be, and then I can index the return array for the stat that I want. Thanks.
其中这些值都是整数，或者应该是整数，然后我可以为我想要的统计数据索引返回数组。谢谢。
-Rik
-里克
采纳答案by Tim
Function getStats() As Variant
getStats is now an Array and not an Integer
getStats 现在是一个数组而不是一个整数
回答by moffeltje
Give the function the type as an array:
将函数的类型指定为数组：
function getStats() as Integer()
dim returnVal(0 to 3) as integer
returnVal(0) = c2percent14
returnVal(1) = c3percent14
returnVal(2) = c4percent14
returnVal(3) = c5percent14
getStats = returnVal
end function
Sub mysub()
Dim myArray() As Integer
myArray = getStats()
msgbox myArray(3)
end sub
回答by technoman23
I'm going to add an answer here because I'm happy to say, after hours of frustration and bad information, I finally know how to return arrays! Here is how you return an array from a function:
我将在这里添加一个答案，因为我很高兴地说，在经历了数小时的沮丧和糟糕的信息之后，我终于知道如何返回数组了！以下是从函数返回数组的方法：
Sub mysub()
Dim i As Integer, s As String
Dim myArray() As Integer 'if you declare a size here you will get "Compile error, can't assign to array"
myArray = getStats()
s = "Array values returned:" & vbCrLf
For i = 0 To UBound(myArray)
s = (s & myArray(i) & " ")
Next
MsgBox s
End Sub
Function getStats() As Integer() 'The return type must be EXACTLY the same as the type declared in the calling sub.
Dim returnVal(2) As Integer 'if you DON'T declare a size here you will get "Run-time error '9': Subscript out of range"
returnVal(0) = 0
returnVal(1) = 1
returnVal(2) = 2
'returnVal(3) = 3 This will throw an error. Remember that an array declared (2) will hold 3 values, 0-2.
getStats = returnVal
End Function
Output:
输出：
The comments I included here are very important. Although VBA is usually pretty lax, this particular thing is very picky. These are required for your function, assignment, and return to work:
我在这里包含的评论非常重要。尽管 VBA 通常很松散，但这个特殊的东西非常挑剔。这些是您的职能、分配和重返工作岗位所必需的：
- The array declared in the calling sub has to be of undeclared length.
- The function that returns an array has to be of EXACTLY same type. Even if you declare the array in the sub as a Variant and the function returns an Integer array that will not work either.
- You have to use a temporary array in the function. You cannot assign values to the function name (getStats) like you normally would in a function; you can only assign the temporary array to the function name once you have assigned all the values to the temp array. Any attempt to ReDim getStats as an array will also throw an error.
- The temp array has to be declared with length. In VBA you cannot assign values to an array at an index until you declare the length.
- 在调用子程序中声明的数组必须是未声明的长度。
- 返回数组的函数必须是完全相同的类型。即使您将 sub 中的数组声明为 Variant 并且该函数返回一个也不起作用的整数数组。
- 您必须在函数中使用临时数组。您不能像通常在函数中那样为函数名称 (getStats) 赋值；将所有值分配给临时数组后，您只能将临时数组分配给函数名称。任何将 getStats 重新定义为数组的尝试也会引发错误。
- 必须使用长度声明临时数组。在 VBA 中，在声明长度之前，您无法在索引处为数组赋值。
