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