How to pass an array to a function in VBA?

Question

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?

Answer 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.

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:

Function processArr(arr() As Variant) As String     processArr = Replace(Join(arr()), " ", "") End Function 

If you are into the whole brevity thing.