How do I pass an array in a user defined function? E.g.,

=FunctionName(Array1, Constant, ...)


--
Thanks for any help

On the Worksheet =FunctionName({1,2,3,4,5},constant)

in the VBA function you then have to assign to a Variant

Function FunctionName(Array1 as variant, Constant as ...) as ...


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Peter M" wrote in message
...
How do I pass an array in a user defined function? E.g.,

=FunctionName(Array1, Constant, ...)


--
Thanks for any help

Thanks Charles. So if I want to pass only values in cells, I could say
=FunctionName({B11:B39},B8) in tjhe Worksheet, right? I do not know what a
Varian Function is, but I will look it up in Help.

"Charles Williams" wrote:

On the Worksheet =FunctionName({1,2,3,4,5},constant)

in the VBA function you then have to assign to a Variant

Function FunctionName(Array1 as variant, Constant as ...) as ...


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Peter M" wrote in message
...
How do I pass an array in a user defined function? E.g.,

=FunctionName(Array1, Constant, ...)


--
Thanks for any help

Peter M wrote...
Thanks Charles. So if I want to pass only values in cells, I could
say
=FunctionName({B11:B39},B8) in tjhe Worksheet, right? I do not know
what a
Varian Function is, but I will look it up in Help.
....

First, it's varianT, not varian.

You'll find {B11:B39} is a syntax error. If you want to pass the values
of a range to a udf, just pass the range and use the argument in the
UDF like an array. The argument will actually be a Range object, but
the Range class's default property is .Value, which will be the array
of values of the range.

You only neeed the {1,2,3;4,5,6} kind of argument if you are passing arrays
of constants to a function. If you want to pass an array of cells, use
Harlan's description of passing a range:
=FunctionName(B11:B39,B38)

then in the VBA
public FunctionName(theInputs as variant,Constant as ...)

defining theInputs as variant allows the function to either have an array of
constants as input, or a range of cells. You would have to program tests on
the variant to determine which one the function had been passed.


regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Harlan Grove" wrote in message
oups.com...
Peter M wrote...
Thanks Charles. So if I want to pass only values in cells, I could
say
=FunctionName({B11:B39},B8) in tjhe Worksheet, right? I do not know
what a
Varian Function is, but I will look it up in Help.
...

First, it's varianT, not varian.

You'll find {B11:B39} is a syntax error. If you want to pass the values
of a range to a udf, just pass the range and use the argument in the
UDF like an array. The argument will actually be a Range object, but
the Range class's default property is .Value, which will be the array
of values of the range.

I am wondering that, in the below post from Charles, after declaring "Array1
as Variant" in the user-defined function name, then in the UDF codes, should
we declare Array1 again for its dimension(s)?

Basically, how to make use of Array1 in the UDF, if Array1 has been defined
somewhere else?

Thanks,
Laurie


"Charles Williams" wrote:

On the Worksheet =FunctionName({1,2,3,4,5},constant)

in the VBA function you then have to assign to a Variant

Function FunctionName(Array1 as variant, Constant as ...) as ...


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Peter M" wrote in message
...
How do I pass an array in a user defined function? E.g.,

=FunctionName(Array1, Constant, ...)


--
Thanks for any help