Array formula help!

[alibini]

Can anyone tell me why the first of the following functions works when used in an array formula and the second does not?

Very simple test:

Please Login or Register  to view this content.

when i use it in an array formula using {} brackets such as:
{=test(A1:A20)}
it works

BUT, when I try to make the following work I keep getting #VALUE! error:

Please Login or Register  to view this content.

I know the function is working, but cannot get it to work as part of an array formula.
I have been trying the following but keeps giving me an error.

{=+ArrayCapFloor(A1:A20,33,107)}

Am I missing something????

Thanks help.

[Marcol]

Try

Public Function ArrayCapFloor(myarray As Variant, floor As Long, cap As Long) as Variant

[alibini]

Hi Marcol,

Actually I had tried that already and sadly doesn't work. I'm really not sure what the missing part of this puzzle is....