Calculation order

[Ron Rosenfeld]

I just came across something interesting, and wonder if someone can point me to
a discussion.

I noted with regard to a UDF I have to convert Roman to Arabic numerals, that
the function call seems to affect the worksheet calculation order.

The original function call was:

Function Arabic (rg as range) as Long


With this call, given the following:

A1: some arabic number
A2: =ROMAN(A1)

A3: =Arabic(A2)

A3 appeared to calculate first. So that making a change in A1 would result in
A3 showing the number that was PREVIOUSLY in A1 instead of the current number.

Moving the cells around did not make a difference.

What did make a difference was eliminating the Type specification (or changing
the type specification to Variant) for the variable in the function call:

Function Arabic (rg) as Long

or

Function Arabic (rg as variant) as long

--ron

 

[Norman Jones]

Hi Ron.

See the discussion by Charles Williams at:

Excel's Calculation Process
http://www.decisionmodels.com/calcsecretsc.htm

 

[Guest]

With regard to your Arabic function:

1. Dim rg as Range in the Header
2. Use rg.Value in place of rg.Text
3. insert Application.Volatile near the top

 

[Ron Rosenfeld]

Hi Ron.

See the discussion by Charles Williams at:

Excel's Calculation Process
http://www.decisionmodels.com/calcsecretsc.htm

Norman,

Thanks for that reference.

It is useful, but I was not able to find the explanation for what I am seeing.

He notes that there is a problem with a UDF not calculating if the precedent
cell is not referred to in the Function argument. However, in my instance, it
seems to be referred to in both types of function calls:

Function Arabic (rg as range) as Long

or

Function Arabic (rg) as Long

or (equivalent)

Function Arabic (rg as Variant) as Long.

Yet the first calculates before "rg" has recalculated, and the latter
calculates after "rg" has calculated.

Within the function, rg seems to be returning a range object in both instances.
--ron

 

[Ron Rosenfeld]

Hi Ron.

See the discussion by Charles Williams at:

Excel's Calculation Process
http://www.decisionmodels.com/calcsecretsc.htm

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.
--ron

 

[Ron Rosenfeld]

With regard to your Arabic function:

1. Dim rg as Range in the Header
2. Use rg.Value in place of rg.Text
3. insert Application.Volatile near the top

Thanks for that. Adds some insight into the issue.

Application.Volatile does not seem to be required.

For some reason, using rg.text within the UDF, with (rg as range) in the
header, screws up the dependency tree.

Either using rg as variant (or just rg) in the header; or using rg.value within
the UDF, does not screw up the dependency tree.

This seems like Odd behavior.
--ron

 

[Norman Jones]

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

 

[Ron Rosenfeld]

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

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