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?

Sometimes the ByRef keyword helps as well: `Function test(ByRef arr() As Variant)` — Christian, Jul 12 '20 at 19:21

score 48 · Accepted Answer · answered Oct 21 '14 at 17:46

48

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.

answered Oct 21 '14 at 17:46

JNevill

46,980
4
38
63

1

The dude abides – 3therk1ll Jan 07 '22 at 09:31
1

Well, I guess I've seen something every bit as stupefying as you can see in any of those other places, and in English, too. – user1422348 Aug 05 '22 at 03:21

score 33 · Answer 2 · answered Oct 21 '14 at 17:53

33

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

answered Oct 21 '14 at 17:53

HansUp

95,961
11
77
135

1

The reference for ParamArray is https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-parameter-arrays Interesting that VBA doesn't even highlight ParamArray or change the case. – cup Aug 22 '21 at 21:36

How to pass an array to a function in VBA?

2 Answers2

Linked