On Fri, 4 May 2007 14:22:38 +0100, "Norman Jones"
wrote:

Hi Ron,

'------------------
Hmmm,

Trying out Gary's S suggestion, which works, it seems what is happening is
that, for some reason, if I use rg.text within the UDF, it screws up the
dependency tree. Whereas if I use rg.value (or just rg) within the UDF, it
does not.

Strange.
'------------------

In Charles W's discussion of UDF's, did you see
his comments on the use of cell properties other than .
value or .formula?

See:

Referencing cell formatting properties
http://www.decisionmodels.com/calcsecretsj.htm

---
Regards,
Norman


Here's what I saw and, when I read it, I didn't think it was applicable.

-----------------------------------------------
Referencing cell formatting properties

If your UDF references cell properties other than .value or .formula
(ie .Bold) there are some occasions when these properties may be undefined when
your UDF is evaluated. One such occasion is renaming a worksheet in automatic
mode. If this happens you may need to explicitly recalculate your function.
--------------------------------------------

The properties are not undefined. What is happening, according to my
experimentation (using breakpoints within the UDF), is that the UDF is being
calculated prior to the cell referenced in the Function declaration, and is not
recalculated after the precedent cell is calculated.

Within the UDF, changing rg.text to rg.value causes the function precedent cell
to be calculated first.


By the way, I sent a note to Charles W and I seemed to have piqued his
interest. He's planning to look into it.


--ron