EXCEL calculation sequence

[CSharp Dummy]

I don't know if someone has come across the following:

When I use my functions in a chain like

=function($A$1)
=function($A$2)
=function($A$3)
=function($A$4)

I would expect for Excel to calculate them in the right sequence.
For some reason I find that Excel calculates the last one first and
this leads to false calculations.

I use the following settings:

[ExcelFunction(Description = "function", IsMacroType = true,
IsVolatile = true,IsThreadSafe = false)]

in Excel 2007.

Any ideas what could have gone wrong?

[Govert van Drimmelen]

Hi,

Setting IsMacroType=true interferes a bit with Excel's dependency
calculations, and could introduce extra evaluations of your function.
Certainly after Excel has recalculated it should have a consistent set
of values. I presume you are just noticing some unexpected evaluations
of your function.

You can read a bit more about how Excel sets up the dependency graph
and does the recalculation here: http://msdn.microsoft.com/en-us/library/bb687891.aspx
and here: http://www.decisionmodels.com/calcsecretsc.htm.

It's best to not mark a function with IsMacroType=true, unless you
really need to have it (for example if your function reads values from
the sheet that are not passed in directly).

I presume you understand the effect of IsVolatile=true. (A quirk of
Excel that is also mentioned in the Microsoft articles linked above is
that IsMacroType=true functions are automatically considered
volatile.)

Regards,
Govert

[CSharp Dummy]

No that isn't it.

I simplified the functions and just used a counter to see which one
fires first. These are my settings:

[ExcelFunction(Description = "function", IsMacroType = false,
IsVolatile = true, IsThreadSafe = false)]

Even though I chained them together, the sequence is wrong.

This is really strange.

> > Any ideas what could have gone wrong?- Hide quoted text -
>
> - Show quoted text -

[Ben Mcmillan]

according to this link:

http://www.decisionmodels.com/calcsecretsj.htm

there's no guarantee that a cell's UDF will only be called once the precedent cells are calculated.... If the precedent cells are not calculated, the function may still be called but will then be called later (again).

as it says on the link, in VBA you could use ISEMPTY to check whether a range has been calculated. I assume you can do something similar from exceldna.

Not sure whether that helps you or not.

Ben

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Govert van Drimmelen]

Hi Holger,

Excel-DNA does not interact or interfere with Excel's calculation
sequence.
So just to let us check what you are actually referring to,

* Could you post a small self-contained sample in a .dna file that
shows what you are worried about?
* Could you also compare with a UDF function defined in VBA?

The only scenario I can think of is that your function is taking an
ExcelReference (via an [ExcelArgument(AllowReference=true)] attribute)
and then not reading the value from the ExcelReference.

-Govert

On Feb 27, 4:28 pm, CSharp Dummy <csharpdu...@gmail.com> wrote:
> "If the precedent cells are not calculated, the function may still be
> called but will then be called later (again)."
>

> This would be ok, but unfortunately this is not what happens. I just
> find that the fucntions are calculated in some order and the the full
> recalculate stops.
>
> I'm able to get the right sequence when I recalculate several times,
> but I would assume that there is an easier solution.
>
> Holger

>
> On Feb 26, 11:11 pm, Ben Mcmillan <ben.mcm...@gmail.com> wrote:
>
>
>
>
>
>
>
> > according to this link:http://www.decisionmodels.com/calcsecretsj.htm
>
> > there's no guarantee that a cell's UDF will only be called once the
> > precedent cells are calculated.... If the precedent cells are not
> > calculated, the function may still be called but will then be called later
> > (again).
> > as it says on the link, in VBA you could use ISEMPTY to check whether a
> > range has been calculated. I assume you can do something similar from
> > exceldna.
>
> > Not sure whether that helps you or not.
> > Ben
>

> > >http://groups.google.com/group/exceldna?hl=en.-Hide quoted text -

[CSharp Dummy]

Govert,

You might be on to something:

"The only scenario I can think of is that your function is taking an
ExcelReference (via an [ExcelArgument(AllowReference=true)] attribute)
and then not reading the value from the ExcelReference."

Although I'm using the reference I dont actually read the value of the
reference.
That might be it - let me try it out.

> > > >http://groups.google.com/group/exceldna?hl=en.-Hidequoted text -
>
> > > - Show quoted text -- Hide quoted text -

[Govert van Drimmelen]

Hi Holger,

Yes, if your function
=function($A$4)
does not actually read the value from the reference to $A$$, then
Excel will know that your function evaluation does not depend on the
value in that cell, so Excel won't update its dependency tree.

-Govert

> > > > >http://groups.google.com/group/exceldna?hl=en.-Hidequotedtext -

[CSharp Dummy]

You are right I just tested it in VBA.
I thought mere referencing another cell would be enough to update the
dependency tree.

Didn't know that ...