How to pass an array to a function in VBA? How to pass an array to a function in VBA? vba vba

How to pass an array to a function in VBA?


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.


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 = finalStrEnd Function

And then call the function like this ...

processArr("foo", "bar")