Excel's recalculation triggers

[NTK]

I would like to get more insights about Excel's recalculation triggers. I have an ExcelFunction that is recalculated when I change a cell even though this cell has no reference to my function. I set IsVolatile = false but nothing has changed. However, when I declare the last parameter as double instead of object the recalculation is not triggered.

I'm using C#, Office 2010 and ExcelDna.Integration.dll 0.30.6.1.

Basically this is my function:
[ExcelFunction]
public static object ExcelFunctionRecalc(object x, object[] xValues, object[] yValues)
{
return Functions.TestFunction((double)x, xValues.Cast<double>().ToArray(), yValues.Cast<double>().ToArray());
}

Why objects?
In my case I have doubles and/or strings as input. The strings are then transformed into numbers.

What do I do?
Excel's calculation is set to automatic. I write about 30 new values in some cells on sheet1. On sheet2 I have 800 cells with that function above. Just changing the parameter from object to double triggers a complete different behaviour. So sometime this function is called 8000 times and seting the last paramter to double reduces this to 0 to 15 calls.

I tried any combination for this function's signature but could see the recalculation disapere only using double[] for the last parameter.
When I use a simple return 2 the recalculation won't happen.
When I put this simple return in another function into a different project the recalculation happens again.

Setting Excel's calculation mode to manual reduced the calls to that function however I want to understand the reason why Excel does these recalculations when in calculation mode automatic.

Unfortunately I was unable to isolate this behaviour any further neither reproduce it from scratch, so can't provide a simple code and Excel workbook. I thought I do understand the settings IsMacroType and IsVolatile maybe I'm missing something here.

Did some of you experience similar issues? And where can I read more about Excel's recalculation? The msdn page about xlfRegister was not helpful to me but mostly because I didn't understand it. Maybe there's more readable information out there.

Any help is appreciated!
Toby

[NTK]

What do I want?
I think my function is recognized as volatile like random numbers which are recalculated even though no input is changed but some arbitrary cell in my book.
I dont want to use manual calculation in Excel as functions should be recalculated when some input changes. But this function should only be recalculated when an input is changed.

[Patrick O'Beirne]

You need to refer to that cell with your function call. It can be a dummy, as long as Excel sees the reference it will recalc the dependent cell when the cell changes.
eg =myfunc() + thatcell*0

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Govert van Drimmelen]

Hi Toby,

Let's try this function:

    [ExcelFunction]

    public static object ExcelFunctionRecalc(object x, object[] xValues, object[] yValues)

    {

        return DateTime.Now.ToString("HH:mm:ss.fff");

    }

I make a new sheet, and into A1 I put the formula =ExcelFunctionRecalc(A2, A3:B3, A4:B4)

Every time the function recalculated, the result changes and it's easy to see.

Now if I edit any of the references cells, the value changes, but if I edit cell A5, it does not.

Do you see the same results on your Excel?

You can find wonderful Excel calculation details on Charles Williams's FastExcel / Decision Models site: http://www.decisionmodels.com/calcsecrets.htm

-Govert

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+unsubscribe@googlegroups.com.

[NTK]

This is what I did while trying to reconstruct a simple example. Same behavior as yours and exactly what I want. This is what I meant by: I cannot build this issue from scratch. Anyways, in my original book I still can trigger/untrigger changing the parameters.

Hi Toby,

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

[NTK]

Oh sorry, I forgot to thank you two!
If you know of any interesting article about this or could explain why changing a parameter has such an enormous effect on Excel, I'm happy to dig deeper.

[Govert van Drimmelen]

A function that is both marked IsMacroType=true, and has at least one 'object' parameter marked with AllowReference=true will automatically be considered as volatile by Excel.

-Govert

[NTK]

That's true. In my case I dont use IsMacrotype or IsVolatile, so defaults should be used. And I dont use AllowReference. Even using IsVolatile=false Excel recalculated many times.

[NTK]

So I tried using return DateTime.Now.Second; with my original sheet and it didnt do any recalculation. Strangely, when I use return OtherFunction(...) and in this other function I use DateTime.Now.Second it does recalculate

[NTK]

Unfortunately I was not able to reproduce this behavior when I created a new solution.... I tried to isolate the problem as far as I could and the only function on my orignal sheet is now this function so no other dependencies...

[NTK]

Thanks govert for the link I read those pages and removed conditional formatting and names which I used both frequently. But things didnt improve, so I maybe the following is happening:
"Sometimes Excel will decide that the fastest way to calculate the workbook involves calculating a particular cell more than once."

[NTK]

After doing some more research I found that my function was actually never registered as volatile (I found no ! in FunctionText). However when testing and recording a performance test it showed this weird behaviour: using in- and output parameters as object it did recalculate many times and using the last parameter as double it worked just fine.

[Govert van Drimmelen]

Hi Toby,

For a function with no IsMacroType=true and no AllowReference=true, I'm surprised that you find a difference in the calculation behaviour depending on the parameter types.

I'd very much appreciate a simple example that I can test myself.

As a starting point, perhaps you could try this simple function:

    [ExcelFunction]

    public static object ExcelFunctionRecalc(object x, object[] xValues, object[] yValues)

    {

        return DateTime.Now.ToString("HH:mm:ss.fff");

    }

It should not be volatile, and it's easy to see when it recalculates.

If you find that the Excel calculation behaviour changes when you change the parameter types, I'd really like to know.

Regards,

Govert