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?

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

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

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")

Comments

[Christian]

Sometimes the ByRef keyword helps as well: Function test(ByRef arr() As Variant)

[cup]

The reference for ParamArray is docs.microsoft.com/en-us/office/vba/language/concepts/… Interesting that VBA doesn't even highlight ParamArray or change the case.

[3therk1ll]

The dude abides

[JNevill]

I don't know about you, but I take comfort in that. @3therk1ll I just watched that again yesterday. Good timing.