Nested UDF Formula Returns Value

[ghrain22]

Hello,

I'm running into an interesting problem when I go to make a nested formula with 2 UDF's. I get a #VALUE error even though it seems like everything is evaluating correctly.

Quick Description:
Zcut: removes 0's from array
test: grabs max

For example:
=test(zcut(TRANSPOSE((IF($A5=lkup,clicks,0)))))

When I evaluate with F9:
=test(zcut(TRANSPOSE((IF($A5=lkup,clicks,0)))))
=test({735,691,688,778,651,644,609,704,708,612,616,509,620})

=test(zcut(TRANSPOSE((IF($A5=lkup,clicks,0)))))
=778

I'm not sure what's going on. Is it possible that the "test" is trying to run before the "zcut"? I'm confused, and would really appreciate any help.

Thanks!
Ghrain22

 

[Norie]

Can you post the code for the UDFs?

 

[ghrain22]

ZCut

Public Function zcut(arrIn As Variant) As Variant
    Dim tval As Variant
    Dim arrOut As Variant
    Dim i As Integer
    Dim j As Integer


    ReDim arrOut(0)
    j = 0


    For i = 1 To UBound(arrIn)
        If arrIn(i) <> 0 Then
        arrOut(j) = arrIn(i)
        j = j + 1
        ReDim Preserve arrOut(UBound(arrOut) + 1)
        End If
    Next i


    ReDim Preserve arrOut(UBound(arrOut) - 1)
    zcut = arrOut
    
End Function

test

Function test(ByRef x As Variant)
'MsgBox (Application.Subtotal(4, x))
MsgBox (Application.max(x))
test = Application.max(x)
End Function

 

[ghrain22]

So I think the problem might be with the using the zcut in a parameter for any kind of formula. because
=Max(zcut(transpose(A1:A10)))
=Sum(zcut(transpose(A1:A10)))

Also returns a "#VALUE" error. Even though
zcut(transpose(A1:A10)) evaluates to an array of values correctly.

 

[Norie]

zcut is called first but it calls test as soon as it hits the loop.

test then executes but as code stops when code execution is passed back to zcut.

If I replace test with MAX the code fails at the same point.

Have you checked what arrIn actually is when zcut is being executed?

PS If I enter the formula as an array formula it appears to work.

 

[ghrain22]

zcut is called first but it calls test as soon as it hits the loop.

test then executes but as code stops when code execution is passed back to zcut.

If I replace test with MAX the code fails at the same point.

Have you checked what arrIn actually is when zcut is being executed?

PS If I enter the formula as an array formula it appears to work.

Sorry for the late response. Entering it as an array fixed it (although I was fairly sure I had tried that already). Thank you very much for your help.