VBA: How to pass arrays in Function Calls?

[Guest]

You are assigning the first element of an array of integers with the string
Won. This array can only hold integers. Try this...

Function Testx(Arg)
' Function should return the word "Won".
Dim Cols(3) As Integer
Call Testy(Cols())
Testx = Cols(1)
End Function

Sub Testy(Cols() As Integer)
Cols(1) = 123
MsgBox "Done" <-- Calling never gets to this line.
End Sub

 

[fisherofsouls]

More generally, make use of Paramarray to pass back and forth whatever
you like !

Example:

Put this in the declare space:

Public MyCols() 'Default to Variant data type;
also Cols is not a
'great idea for a
variable name as it is reserved to
'VB in many contexts

Then try the following procedures:

Function Testx(Arg)
' Function should return the word "Won".
Call Testy(MyCols())
Testx = MyCols(1)
End Function


Sub Testy(ParamArray MyCols() As Variant)
MyCols(1) = "Won"
MsgBox "Done" <-- Calling never gets to this line.
End Sub

Nick

 

[Mike Mertes]

Mac,

Firstly, if you want to assign "Won" to an element in your array you'll have
to declare it as type string, not integer. That may be your problem there.
Also note that an array declared arr(3) has 4 elements, not 3. (0 is the
first unless option base is specified.)

Your Arg doesn't appear to be doing anything, maybe you meant Function
Testx() as <type> ?

Otherwise I can see no reason your code wouldn't run.

-Mike

 

[Mac Lingo]

This code dies at the "Msgbox" Line.

Can you give me an idea why. And what do I do to make it work as it should?

Function Testx(Arg)
' Function should return the word "Won".
Dim Cols(3) As Integer
Call Testy(Cols())
Testx = Cols(1)
End Function

Sub Testy(Cols() As Integer)
Cols(1) = "Won"
MsgBox "Done" <-- Calling never gets to this line.
End Sub

Thanks,
Mac Lingo
Berkeley, CA

 

[Tom Ogilvy]

There really is no role for a parameter array in this unless the user is
going to pass elements of his array individually.

To use a parameter array for this, you would have to do it this way:

Sub ABC()
Debug.Print Testx("abc")
End Sub

Function Testx(Arg)
' Function should return the word "Won".
Dim MyCols(1 To 3) As Variant
Call Testy(MyCols())
Testx = MyCols(1)
End Function


Sub Testy(ParamArray MyCols() As Variant)
MyCols(LBound(MyCols))(1) = "Won"
MsgBox "Done"
End Sub

So you would have your original array in the first element of the parameter
array. More complex than necessary and outside the intent of the OP.