UDF that participates in circularity always dirty

[Dean Meyer]

If a UDF reads a cell that's part of circularity, then even if it
doesn't use the value (even if values converge or don't change), the
"Calculate" flag never goes away. I'm guessing the UDF cell always
appears dirty.

Try this:

A1: "=B1"

B1: "=TestUDF(A1)"

Public Function TestUDF(InRange As Range) As Variant
Dim TestDouble As Double
TestDouble = InRange.Value
TestUDF = 1
End Function

I'm in XL2003. In manual calc mode. I press [CTRL-ALT-SHIFT-F9].
The "Calculate" flag won't go away.

Am I doing something wrong? Is there a work-around?

--Dean

 

[Charles Williams]

I dont think this behaviour is special to UDFs, it happens with ordinary
formulae as well:

if you have circular refs and iteration is checked the status bar shows
calculate.

For more details on Excel's calculation process with circular references see
http://www.decisionmodels.com/calcsecretsk.htm

and
http://www.decisionmodels.com/calcsecretsc.htm


Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Dean Meyer]

Perhaps I wasn't clear. I'm fully aware of circular references. If you note
my example, no circularity is created; the UDF always returns the value "1"
regardless of the value of cell A1. It's the reading of A1 in the UDF that
causes circularity, even though the value is not used.

The problem arose with a complex cost distribution algorithm that cannot be
written in Excel formulas; it's done in a UDF which sets up some global
arrays based on analysis of a set of rows. Once done, another UDF sits on
those rows and reads the array for each row. The algorithm requires a circle
of dependence, but my cost distribution routine avoids circularity of values.
Thus, no iteration is required for the calculations to come out perfectly,
but it still calculates (no change in values each iteration) and the
"Calculate" flag won't go away.

Any ideas or workarounds would be appreciated!

 

[Charles Williams]

Your dependencies are circular because inside the UDF the executed code
looks at A1.
Excel detects circularities in the initial phase of the calculation where
the calculation chain is not broken just because a calculated value does not
change.

You could try these 2 approaches if you cannot live with the "Calculate"
Message

1. Use Testdouble=Inrange.Text instead of InRange.Value
But note that this gets the formatted value rather than the real value, and
when InRange has not yet been calculated it will give you the previous Text
value for InRange.
So this may well not work for you.

2. Switch Iteration on and use an IF statement in the UDF to bypass
referencing InRange on the second iteration (or whenever its not needed)
(you can set a global switch or Application.Iteration should be be False on
the first calculation and true on subsequent iterations)


Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Dean Meyer]

Charles, thanks for staying with me.

1) Unfortunately, ANY read of the cell causes circularity, even if the
value is not utilized in the function. I tries just an IsEmpty(InRange) and
it caused circularity.

2) Not referencing the cell on the second iteration didn't help.

Any other ideas, or clues as to why this is happening? What should happen
is when the return value is the same on the second iteration, it should
declare victory and turn off the Calculate flag.

 

[Charles Williams]

Any other ideas, or clues as to why this is happening? What should happen

is when the return value is the same on the second iteration, it should
declare victory and turn off the Calculate flag.

Here is a more long-winded explanation:

Excel determines that it has circular references when the dependency chain
cannot be purged of uncalculated cells.
That is the situation your workbook creates, because when Excel executes
your UDF it finds a reference to Inrange.value.
That reference IS a dependency.
It is also an uncalculated cell which cannot be calculated because it
depends on your UDF.

So Excel correctly declares circularity, because the dependency chain
contains unresolved cells.

If you have Iteration checked then after Excel has used its conventional
calculation process it will switch to the Iterative calculation process on
the circular cells and their dependencies. The iterative calculation process
will stop when ANY of these conditions occur:

1. Iterations reach Maxiters
2. No cells change in value by more than Maxchange
3. There are no uncalculated dependencies anymore (ie the circularity has
disappeared)

If you do not want this to happen you have to setup a situation where on the
final iteration excel does NOT execute a reference to Inrange.Value so that
condition 3 occurs. There may be a reference in the code, but if its not
executed then Excel will not find a dependency. This is also true for an IF
statement in a formula, (which is the approach recommended on the website
pages I referred you to for working with circularities).

I have suggested 2 example approaches to achieve this, both of which work
under some circumstances but not others. I am sure you can find an approach
that works for you.

The other alternative is just to live with the "Calculate" message since its
not doing any harm.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[Dean Meyer]

It may be that Excel realizes it's fully calculated when no cells
change in value by more than Maxchange. It does quit before
MaxIters. But it leaves the Calculate flag up.

And it would be nice if not refering to those cells on the interative
second pass (and thereafter) did indeed solve the problem. But as I
said before, it does not.

The UDF remains dirty because it references a cell, not because it
uses its value or state. As I said, even IsEmpty creates this
condition. This hardly constitutes true circularity!

So I think this amounts to a bug with no cure. Instead of fast array
math, I'll have to go row by row, cell by cell, and skip any cells
involved in circularity.

Charles, thanks for trying to help.

--Dean