如何将数组传递给VBA中的函数？

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?

我究竟做错了什么？

2 个解决方案

#1

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.

如果你是整个简洁的事情。

#2

Your function worked for me after changing its declaration to this ...

在将声明更改为此后，您的功能对我有用...

Function processArr(Arr As Variant) As String

You could also consider a ParamArray like 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")

#1