How to get VBA Subroutine to call a function that passes an array to another function within the subroutine

Question

I am trying to get my VBA subroutine to run a function that creates an array and then passes that array to another function which further processes those array items. I have made an example that is extremely simple and functions exactly like my actual code. it is as follows:

Sub SubRoutine()
    ProcessArray CreateArray
End Sub

Function ProcessArray(Arr() As Variant) As Variant
End Function
    
Function CreateArray() As Variant
    Dim Array1(1 To 4) As Variant
    CreateArray = Array1
End Function

It's just the two functions and the subroutine which calls those two functions. The compiler refuses to compile my code and explains to me that:

Compile error:

Type mismatch: array or user-defined type expected

To which I can only say that everything is the same data type and the argument passed is indeed an array. Just in case you were wondering, yes I have tried with an array that has had data allocated to it.

Daniel · Accepted Answer · 2012-12-13 20:39:55Z

4

Change this line:
Function ProcessArray(Arr() As Variant) As Variant
to this:
Function ProcessArray(Arr As Variant) As Variant

This way your function will now accept a Variant that contains an array, instead of looking for an array of Variants. As you said, a subtle but significant difference.

answered Dec 13, 2012 at 20:39

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

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

2 Comments

user1902208 Over a year ago

Anyt tips for getting a function to return an array of strings without coughing up a type mis-match error (example below): Function CreateArray() As String() Dim Array1(1 To 4) As String CreateArray = Array1 End Function

Daniel Over a year ago

@user1902208. that snippet doesn't return an error. You'd just call it like dim x() as string: x = CreateArray.

Mr_Moneybags · Accepted Answer · 2012-12-14 19:16:34Z

I think what still wasn't answered is why
1. MySub(MyArg)
works just fine, but
2. MyOtherSub(MyArg1, MyArg2)
does not

This blog entry explains it well.

Essentially, you can pass an argument which would normally be byref as byval:
Call MySub(Arg1, (Arg2)) 'Arg1 is passed byref, Arg2 is passed byval

Code #1. works because VBA thinks you are passing one argument byVal so it doesnt count as parentheses being used in a Sub call without the Call keyword. As mentioned in other answers, no parentheses are allowed in a Sub call without the Call keyword.

The Call keyword requires Params in parentheses. So with the Call keyword Call MySub(MyArg) would actually pass MyArg byRef because the parentheses are used as the enclosure for arguments.

Code #2 does not work because there is no valid way to justify the parentheses in the syntax (it is only allowed with the Call keyword.

Collectives™ on Stack Overflow

How to get VBA Subroutine to call a function that passes an array to another function within the subroutine

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related