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 · Accepted 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

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  amp  Arr N      Next N     processArr   finalStr End Function   And then call the function like this      processArr  foo    bar

[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