Getting #VALUE! while entering UDF as an array function

[snv]

I am trying to enter my own function in an array formula context,
similar to any other pre defined funtion.
For example, let my array be
{1;4;9;16;25;36}
If I select a 6x1 range of cells and enter the formula
=SQRT({1;4;9;16;25;36})
with a ctrl+shift+enter
I get
1
2
3
4
5
6

Now I want to write a UDF that does the same thing

Function myUDF(Input As Variant) As Variant
myUDF = Input ^ 0.5
End Function

when I enter
=myUDF({1;4;9;16;25;36})
again, with ctrl+shift+enter

I get
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Interestingly enough when I enter
=myUDF(81)
I get
9

Had myUDF been

Function myUDF(Input As Variant) As Variant
myUDF = Input
End Function

IE, no modification, just return what I get
=myUDF({1;4;9;16;25;36})
returns
1
4
9
16
25
36

as expected.

The problem seems to be when I try to use myUDF in an array context
and I try to modify the input in any way. How can I get it to act
just like the
=SQRT({1;4;9;16;25;36})

And before anyone asks why I trying to emulate an already existing
function, this is just an example to illustrate the problem.

Please help and thanks in advance

Sean

 

[Ron Rosenfeld]

I am trying to enter my own function in an array formula context,
similar to any other pre defined funtion.

Take a look here for some information:

http://www.cpearson.com/excel/returningarraysfromvba.aspx

What you need to do is test your argument(s) to see if they are arrays or not;
and if they are, you need to set up an array within your code; and then equate
it to the function name.
--ron

 

[snv]

Take a look here for some information:

http://www.cpearson.com/excel/returningarraysfromvba.aspx

What you need to do is test your argument(s) to see if they are arrays or not;
and if they are, you need to set up an array within your code; and then equate
it to the function name.
--ron

Thanks for the response Ron. A coworker of mine and I just came up
with that same answer.

When I enter the UDF as an array function the entire array is passed
as a single parameter.
When I enter the pre defined excel function as an array function it
behaves as a loop where in invokes the function once for each element
of the array. So the suggested solution works, but can a UDF be
flagged to behave the same as a pre defined function?

 

[Ron Rosenfeld]

Thanks for the response Ron. A coworker of mine and I just came up
with that same answer.

When I enter the UDF as an array function the entire array is passed
as a single parameter.
When I enter the pre defined excel function as an array function it
behaves as a loop where in invokes the function once for each element
of the array. So the suggested solution works, but can a UDF be
flagged to behave the same as a pre defined function?

One way that I know of is to check in the UDF to see what type of variable has
been passed. You can use the VarType function for this. Then you process it
accordingly.
--ron

 

[Ron Rosenfeld]

Thanks for the response Ron. A coworker of mine and I just came up
with that same answer.

When I enter the UDF as an array function the entire array is passed
as a single parameter.
When I enter the pre defined excel function as an array function it
behaves as a loop where in invokes the function once for each element
of the array. So the suggested solution works, but can a UDF be
flagged to behave the same as a pre defined function?

Also, in addition to the above reference from Chip Pearson, you should also
familiarize yourself with:

http://www.cpearson.com/Excel/VBAArrays.htm
--ron