Volatile function + XlCall.Excel(XlCall.xlfVolatile, false) = always non-volatile?!

[Fabian]

Hi,

I'm looking for a way to have one of my functions behave non-volatile, however to calculate once with the first recalculation of the worksheet they're being used on.

I found a few pointers from past threads, such as https://groups.google.com/forum/#!searchin/exceldna/not$20recalculating%7Csort:relevance/exceldna/yJ_uIWR6oQM/WrrEQQW4EOkJ, where some interesting approaches were shown - something like

        [ExcelFunction(IsVolatile = true)]

        public static string VolatileFunction()

        {

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

        }

        [ExcelFunction(IsVolatile = true, IsMacroType = true)]

        public static string VolatileFirstAndThenUnregister()

        {

            XlCall.Excel(XlCall.xlfVolatile, false);

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

        }

        [ExcelFunction(IsVolatile = false)]

        public static string NonVolatile()

        {

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

        }

the way it was described, this should have come close to what I'm looking for.

In practice, the VolatileFunction behaves as expected, the NonVolatile function does as well, but the VolatileFirstAndThenUnregister function does not at all - it just behaves exactly like the NonVolatile function. That is, e.g. when saving a worksheet with all 3 of them, restarting excel and loading up the worksheet again, the VolatileFirstAndThenUnregister function (as well as the NonVolatile function of course) does not recalculate at all, while the VolatileFunction does.

Given this was suggested in a past post - did anything change? In the way these are registered, or maybe in the way modern Excel versions are handling this? I'm using Excel 2014 (64bit).

Thanks!

[Fabian]

Hmm, after a bit more testing, it seems whether a cell needs volatile recalculation or not seems to be stored after each function call?! Namely, when you replace the function with

        [ExcelFunction(IsVolatile = true, IsMacroType = true)]

        public static string VolAndUnregister(bool Volatile)

        {

            XlCall.Excel(XlCall.xlfVolatile, Volatile);

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

        }

And call =VolAndUnregister(true)  in cell A1, and =VolAndUnregister(false) in cell A2, then save the workbook and reloading the entire thing;

A1 will recalculate upon restarting, while A2 will not.

Now this is very invoncenient...

[Govert van Drimmelen]

Hi Fabian,

I thin discussion you point to is a good overview of the volatile behaviour, and I don't know of anything that changed in Excel in this regard. But it has nothing to do with the situation when a workbook is first opened.

I think the first calculation when opening the sheet will only happen if your function was "volatile" when the sheet was saved - you can check inside the .xlsx structure for "volatile" stuff a bit.

This means your scheme won't work without dealing with the new workbook being opened explicitly in an event or something. Once you've made the function not-volatile and saved the book, it won't recalculate when reopening even though this function is now temporarily a 'volatile' function.

I think you're trying to make something too sophisticated here, and you'll be fighting with Excel all the way. Rather handle this by teaching to user to recalculate everything when they want to, giving them a button that does this, or check the Application events and recalculate "your" workbooks when they open.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Fabian [f.me...@gmail.com]
Sent: 16 December 2016 09:30 PM
To: Excel-DNA
Subject: [ExcelDna] Volatile function + XlCall.Excel(XlCall.xlfVolatile, false) = always non-volatile?!

--
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 https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Fabian]

Thanks Govert,

was hoping this was possible in a somewhat standardized way, but indeed it seems that's a bit ouf ot scope for Excel's usual behavior.

For future reference, I went for a somewhat experimental way now; I'm tagging all methods that need this behavior with a custom attribute;

when my addin opens, I run through all currently open workbooks + subscribe to the workbook open event for additional ones being opened.

In each workbook, I'm looking for cells calling the tagged UDFs - first a quick, not very precise search for UDF function name by .Find(), then for the candidate cells a precise check parsing the formula (with XLParse) to see if its actually a real call to these functions. Resulting cells are then tagged with Range.Dirty().

So far, this seems to work fairly well, but I can't get rid of a certain "I really shouldn't be doing this"-feeling, so for the time being will disable this approach by default, unless explicitly enabled in my app.config.

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

Hi Fabian,

That sounds like a reasonable approach, though you'd have to compare the performance of that effort with just recalculating the whole workbook.

Take not of this Range.Dirty bug ( https://social.msdn.microsoft.com/Forums/office/en-US/9e8362f3-e9f4-4642-bda6-a8fa3d893b04/rangedirty-doesnt-work-on-an-inactive-worksheet-bug?forum=exceldev ) whereby it applies to the active sheet, no matter where the Range actually points.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Fabian [f.me...@gmail.com]

Sent: 18 December 2016 06:58 PM
To: Excel-DNA
Subject: Re: [ExcelDna] Volatile function + XlCall.Excel(XlCall.xlfVolatile, false) = always non-volatile?!

To post to this group, send email to exce...@googlegroups.com.

[Fabian]

Interesting, I have tried this with multiple worksheets, but didn't come accross that bug yet - I'll need to double check again.

Calculation time of the above approach was actually fairly fast mostly due to pre-screening matches with Excel's internal Find. Calculation time of refreshing the entire workcould could be very long; I'll actually post a small preview project in this group tonight or tomorrow, will become a bit clearer then.

Thanks,

Fabian