UDFs are called too often--when not necessary in dependency chain

[TomCon]

Have suspected this for a while. Added a Debug.print line to a UDF to try to see what is going on.

The Excel call to the UDF is the following.

=IF(ABS([@[Aj.Rw]])<0.7,0,[B]Get_Adj[/B](ROW([@DvBas]),[@[Aj.Rw]],[@CutLo],[@CutHi],[@[G.Pre]]))

I have put the UDF in Bold, it is called Get_Adj.

Using dependency arrows to double-check, i have verified that:

• The dependency of the cell containing the UDF is only other cells on the same table row.
• No cells are dependent on the result of the cell containing the UDF.
• Also, all cells referenced in the call to the UDF (on the same table row) are constants, contain a number typed in, not another formula. So, there is no down-the-line indirect dependency.

The UDF itself is below. I added the argument ROW([@DvBas]) for debugging purposes, for the Debug.Print line, so that i could track where the UDF is being called.

The UDF formula is filled down, in rows 550 to 1064.

Now, if i edit the UDF formula in a cell, by deleting the closing ) and then readding it, and then hitting return, and look in the code window for the rows in which the UDF was called, it varies.

• On <very rare> occasion i've found it to just be the cell in the row edited (as i expected to always be the case)
• At other times it is between 3 and several hundred additional times that the UDF is called.
• When it is called, many rows are skipped before it is called again. For instance, if i edit the formula in row 550, the rows in which it is called might be 550, and then 850 on down. Or sometimes 1002 on down. Etc. Very mysterious (to me). So, in other words, when it is called, it is not called for every cell in which the formula appears, just some of them.

It is a real performance penalty to have this UDF called so many unnecessary times (the reason i noticed it was "why is recalc taking so long?). I can express this UDF as cell formulas, so i may rewrite it, tho it is a pretty complex formula that is best split over several partial-result cells, so i thought it would be easier to maintain as a UDF.

Any thoughts on this?

Thanks!

The UDF Code:

Function Get_Adj(r As Integer, AjRw As Double, CutLo As Double, _
    CutHi As Double, GPre As Double) As Double
s = Sgn(AjRw)

Debug.Print (r)

If s < 0 Then
    op = "MAX"
    Cutp = CutLo
Else
    op = "MIN"
    Cutp = CutHi
End If

If GPre * -s + AjRw * -s > Cutp * -s Then
        'Apply full Aj.Rw
        Get_Adj = AjRw

ElseIf GPre * -s < Cutp * -s Then
        'Apply only small addition limited to Abs(3)
        Get_Adj = Evaluate("=" & op & "(" & -3 * -s & "," & AjRw / 8 & ")")

Else
    'Case other,G.Pre+Aj.Rw exceeds [Cut] limit
    Full = AjRw + GPre
    Get_Adj = AjRw + Cutp - Full + _
        Evaluate(op & "(" & -3 * -s & ",(" & (Full - Cutp) / 4 & "))")
End If

End Function

 

[TomCon]

OK, actually this is MUCH WORSE.

I am now finding that if i enter any formula or CONSTANT (just a number) anywhere in this workbook, this UDF is called some number of times.

AND i have another workbook open, and if i enter any formula or number in that other workbook, this UDF is also called.

None of those actions could possibly affect the dependency chain. So, it is almost like it is being treated as VOLITLE, though it is not. But not every instance of the appearance of the UDF is updated, only some of them.

Yikes...avoid UDFs altogether?