vba 如何将数组传递给VBA中的函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26492872/
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 pass an array to a function in VBA?
提问by user2395238
I am trying to write a function that accepts an array as an argument. The array can have any number of elements.
我正在尝试编写一个接受数组作为参数的函数。数组可以有任意数量的元素。
Function processArr(Arr() As Variant) As String
Dim N As Variant
dim finalStr as string
For N = LBound(Arr) To UBound(Arr)
finalStr = finalStr & Arr(N)
Next N
processArr = finalStr
End Function
Here is how I try to call the function:
这是我尝试调用该函数的方法：
Sub test()
Dim fString as string
fString = processArr(Array("foo", "bar"))
End Sub
I get an error saying:
我收到一条错误消息：
Compile Error: Type mismatch: array or user defined type expected.
编译错误：类型不匹配：需要数组或用户定义的类型。
What am I doing wrong?
我究竟做错了什么？
回答by JNevill
This seems unnecessary, but VBA is a strange place. If you declare an array variable, then set it using Array()then pass the variable into your function, VBA will be happy.
这似乎没有必要，但 VBA 是一个奇怪的地方。如果您声明一个数组变量，然后使用设置它，Array()然后将该变量传递给您的函数，VBA 会很高兴。
Sub test()
Dim fString As String
Dim arr() As Variant
arr = Array("foo", "bar")
fString = processArr(arr)
End Sub
Also your function processArr()could be written as:
你的函数processArr()也可以写成：
Function processArr(arr() As Variant) As String
processArr = Replace(Join(arr()), " ", "")
End Function
If you are into the whole brevity thing.
如果你喜欢整个简洁的东西。
回答by HansUp
Your function worked for me after changing its declaration to this ...
在将其声明更改为此后，您的函数对我有用...
Function processArr(Arr As Variant) As String
You could also consider a ParamArraylike this ...
你也可以考虑ParamArray这样的......
Function processArr(ParamArray Arr() As Variant) As String
'Dim N As Variant
Dim N As Long
Dim finalStr As String
For N = LBound(Arr) To UBound(Arr)
finalStr = finalStr & Arr(N)
Next N
processArr = finalStr
End Function
And then call the function like this ...
然后像这样调用函数......
processArr("foo", "bar")
