User Defined Function with Array argument

[adre]

I am trying to create a User Defined Function with Array arguments that I want to use in an Array formula. After some debugging, I isolated the problem to this.

VBA Code :
Public Function MyFn(MyRng As Range) As Variant
MyFn = 2
End Function

In the worksheet,
array formula {=MyFn($H$12:$H$15)} gives the result 2.00 - Good!
array formula {=MyFn((J12:J15="GT")*$H$12:$H$15)} gives the result #VALUE! - Why?

I checked with Evaluate Formula and saw that the Formula evaluates to: MyFN({0;-16384.752816512;0;0})
So, I check the array formula {=MyFn({0;16;0;0})} and sure enough it gives the result #VALUE!

Can anyone figure out what exactly is wrong with the array formula {=MyFn((J12:J15="GT")*$H$12:$H$15)} when applied to the UDF code above? TIA.

 

[RoryA]

Your UDF is written to take a range argument, not an array.

 

[adre]

Thanks for your response.
I tried "Public Function MyFn(MyRng() As Single) As Variant" as the first line of my UDF, but not even {=MyFn($H$12:$H$15)} works in that case. I would appreciate if you point out the right syntax.

 

[RoryA]

It would appear that a Variant would be the most suitable parameter type here.

 

[adre]

Thank you. That solved my problem.