Can I use a UDF in an array formula?

[SantaClaus]

Hi all,

I wrote a small UDF which returns 0 or 1 depending on whether the
input cell meets certain criteria. It's something like

MyFunction(InputCell as double, Criteria1, Criteria2) as Integer

where the inputs and the outputs are single values, not arrays.

Is there a way to use it in an array formula?

For example, I have a named range called "bal". I tried entering the
array formula

{sum( 1*( MyFunction(bal,Criteria1,Criteria2)) )}

to count how many records meet my criteria, but it didn't work.

Any suggestion would be more than welcome!

Thanks a lot!

 

[Charles Williams]

Hi Santa,

You need to make your function return an array of the same size as the
InputRange:

Function MyFunction(InRange As Range) As Variant
Dim vArr As Variant
Dim j As Long
Dim k As Long

vArr = InRange.Value2

If VarType(vArr) >= vbArray Then
For j = LBound(vArr) To UBound(vArr)
For k = LBound(vArr, 2) To UBound(vArr, 2)
If IsNumeric(vArr(j, k)) Then
vArr(j, k) = Abs(vArr(j, k))
End If
Next k
Next j
MyFunction = vArr
Else
MyFunction = Abs(vArr)
End If
End Function

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[SantaClaus]

Hi Santa,

You need to make your function return an array of the same size as the
InputRange:

Thanks a lot, that's very helpful!