Er...tell me again why I can't pass ParamArray to a Function?

[baobob]

Y'all set me straight a year ago on how to "pass" ParamArray to a
another Function (sorry about the Google; don't live in the real
world):

http://groups.google.com/group/micr...read/thread/7a2c59b91968aae5/00f9ce718998a088

Since then, this works:

Function Foo(ParamArray VParams()) As String
Dim V() As Variant
V = VParams
Foo = Bar(V)
End Function

Function Bar(VParams) As String
Dim VParam As Variant
For Each VParam In VParams
FlogSelfForInvestingIn(VParam)
Next
Bar = "Dow go bye-bye."
End Function

***

What I don't understand is why you must use an intermediate var. in
Foo. Why can't you do the more intuitive:

Function Foo(ParamArray VParams()) As String
Foo = Bar(VParams())
End Function

Function Bar(ParamArray VParams()) As String

etc.

Thanks much.

***

 

[baobob]

Aw, damn it.

My Q was prompted by several hours' worth of trouble today in trying
to remember & reconstruct the syntax I just told you works for me.

Turns out, it appears intermediate var V() is NOT necessary. This
seems to work:

Function Foo(ParamArray VParams()) As String
Foo = Bar(VParams)
End Function

Function Bar(VParams) As String

etc.

So my only Q now is why Bar's parameter can't be parallel to Foo's.

Thanks again.

***

 

[Bob Phillips]

Because when VParams hits Foo, it is many different parameters, VBA collects
it into an array for ease of use, so you are passing the array on, not each
separate parameter.

 

[baobob]

Bob:

OK, thanks very much for answering. I think that sets me straight.

***

 

[Charles Williams]

I can't get that to work unless I use ByVal as in

Function Bar(Byval VParams) As String

Which is doing a copy anyway, even if its not explicit.

If I don't use ByVal I get a compile error 'Invalid use of ParamArray'

(The reason I don't want to do a copy is for performance reasons when the
parameter is a formula creating a large result set)

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com