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  amp  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

User · Answer

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

User · Answer

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

[arrays] How to pass an array to a function in VBA?

Examples related to arrays

Examples related to function

Examples related to ms-access

Examples related to vba