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
– Christian, Commented Jul 12, 2020 at 19:21

JNevill · Accepted Answer · 2024-10-23 17:31:53Z

54

This seems unnecessary since the Array() function documentation clearly states that the function returns a Variant type, but VBA is a strange place. If you declare an array variable (of type Variant), then set it using Array() you can then successfully pass the variable into your function.

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.

edited Oct 23, 2024 at 17:31

answered Oct 21, 2014 at 17:46

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

3therk1ll Over a year ago

The dude abides

user1422348 Over a year ago

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.

HansUp · Accepted Answer · 2014-10-21 17:53:05Z

34

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, 2014 at 17:53

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

1 Comment

cup Over a year ago

The reference for ParamArray is learn.microsoft.com/en-us/office/vba/language/concepts/… Interesting that VBA doesn't even highlight ParamArray or change the case.

Collectives™ on Stack Overflow

How to pass an array to a function in VBA?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related