Passing an array in a user defined function

[Peter M]

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

=FunctionName(Array1, Constant, ...)

 

[Charles Williams]

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]

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.

 

[Harlan Grove]

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.

 

[Charles Williams]

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

 

[Laurie]

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