In article om,
"keepITcool" wrote:

seeing the reactions of others I looked
into this with a bit more ca

Looks like most of this is due to using the TEXT property.

That's interesting - good catch.

But it's also interesting that in the original scenario, after
demonstrating the anomaly, modifying the function to

Function MyTest(r As String) As String
myTest = r
End Function

causes it to work correctly, and then, going back to the original .Text
formula:

Function MyTest(r As Range) As String
myTest = r.Text
End Function

works correctly too...

So it may be partly due to the Text property, but it also appears to be
due to the order in which the calculation dependency table is created.

it looks like the updating sequence as follows
the values in the worksheet are updated.
the text in all cells are updated using
numberformats and other formatting.

in that case, restoring the .Text property in the macro above should
cause the lagging behavior to return, but it doesn't. I also find it
hard to believe that the Text property is not updated at the same time
as the Value property.

since a change in TEXT does not trigger
a calculation event c1 is not aware it needs another recalc.

Don't think so - if the dependency tree is properly structured, C1
should never be marked "clean" until B1 is calculated and its value and
text properties updated.

That said, I don't have a clue what's going on internally...