Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a UDF in an array formula?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a UDF in an array formula?
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use a UDF in an array formula?
On 24 Apr, 12:39, "Charles Williams"
wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
copy one array formula to an array range | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |