Formulas always execute when the workbook opens, when using refereces

[armandos...@gmail.com]

Hi, Govert, hope you are having a good day,

First of all I would like to thank you for all your answers in the group, they are really helpful and you are always answering everything.

We are currently maintaning an Excel Add which uses Excel Dna. We used to support simple custom formulas, but we decided to add the support of using references in the parameters of our formulas. This means we started to use the attribute AllowReferences in our parameters (ExcelArgument). After this change we noticed that our formulas are executing when the workbook opens. This is causing some strange behaviors in our Addin. 

First of all, we would like to know if this is the expected behaviour when using references. We would like to know if there is a way to avoid our formulas execute when the workbook is opening.

This is not our production code, but I managed to replicate the behaviour using this code. It is a formula that returns "Hello True", if you send a reference as parameter and "Hello false" if not.

public class Class1
    {
        [ExcelFunction(IsMacroType =true)]
        public static object Hello([ExcelArgument( AllowReference =true)] object reference)
        {
            var isReference = reference is ExcelReference;
            var prms = new object[1];

            if(isReference)
                prms[0] = (reference as ExcelReference).GetValue().ToString();    
            else
                prms[0] = reference.ToString();

            return ExcelAsyncUtil.Observe("Hello",prms, () => new ExcelObservableClock(reference));
        }
    }

    class ExcelObservableClock : IExcelObservable
    {
        private object Param { get; set; }

        public ExcelObservableClock(object param)
        {
            Param = param;
        }

        public IDisposable Subscribe(IExcelObserver observer)
        {
            var isReference = Param is ExcelReference;  
            var value = "Hello " + isReference;

            observer.OnNext(value);
            return new ActionDisposable();
        }

        class ActionDisposable : IDisposable
        {
            public ActionDisposable()
            {
            }
            public void Dispose()
            {
                Debug.WriteLine("Disposed");
            }
        }
    }

Hope you can help us,

Armando

[Govert van Drimmelen]

--------------------------------------------------
Excel-DNA is now registered on GitHub Sponsors.
If you want to encourage further development and support, please sign up for a monthly contribution.
--------------------------------------------------

Hi Armando,

Functions that are both IsMacroType=true and have at least one AllowReference=true parameter are automatically considered as volatile by Excel.

This means they will recalculate on any sheet change, which I presume is even worse for you than calculating when opening the workbook.

Is there a specific reason why you need the IsMacroType=true option?

I think this volatile behaviour is because of the extra ability of IsMacroType=true functions to read other parts of the sheet, though I don't have a simple example at hand to show why the interaction between these options make for a particular problem. But I presume it's Excel saying that if you have the power of these options together, the only way to ensure a consistent workbook is to recalculate you function every time.

You can override the volatile flag inside the function, by calling XlCall.Excel(XlCall.xlfVolatile, false). But I would not do that without some thought about why you need to do so. 

 There are some other considerations when using the RTD-based features (ExcelAsyncUtil.Observe, in this case) and deciding what should happen when the workbook is opened. You might want the function to calculate at that point, so that the real-time IObservable feed gets re-connected. I changed some of this behaviour between versions -  in the Excel-DNA source code I see this:

       [Obsolete("Can't support the NoAutoStartOnOpen option anymore - call without ExcelObservableOptions")]
        public static object Observe(string callerFunctionName, object callerParameters, ExcelObservableOptions options, ExcelObservableSource observableSource)

So it looks like I tried to have an option to not start the RTD server when the workbook is opened, but it was problematic and removed.

That makes me think your RTD-based functions will recalculate when the workbook opens, even if they are not volatile.

Could that be true? 

I hope this gives you some ideas for where to look and try further experiments.

-Govert

[armandos...@gmail.com]

Hi Govert, thanks a lot for your response,

The reason why we use IsMacroType option, I think is because during the execution of the formulas we need to recover information about the workbook. We invoke functions like this one:

XlCall.Excel(XlCall.xlSheetNm, caller)

and also this one:

 var caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
 var res = caller.GetValue();

About the RTD functions, your are right, even if I remove the IsMacroType option, the formulas still execute when I open the workbook. So, as far as I understand, the only options we have to avoid this behavior are:

1) Try to override the volatile flag inside the function, by calling XlCall.Excel(XlCall.xlfVolatile, false) (not recommended)

2) Remove the use of RTD functions 

Is this correct?

Thanks a lot, Govert

[Govert van Drimmelen]

Hi Armando,

 

You definitely don’t need IsMacroType=true in order to call

    XlCall.Excel(XlCall.xlSheetNm, caller)

 

But I think you do need IsMacroType=true to get the old cell value with

    var res = caller.GetValue();

 

It is a bit of a danger sign that you are using the old value of the cell somehow.

It might mean you’re going a far off the normal path, especially mixing that in with RTD-based functions.

 

Otherwise your understanding is right - there are two recalculation issues:

• Your function is volatile because of the combination of IsMacroType=true and AllowReference=true. You can switch this volatility off as discussed, but you might be better off trying to remove the old value story, and thus getting rid of IsMacroType=true.
• Your function will recalculated when the workbook opens if there are ‘live’ RTD functions when it was saved. That’s the intended behaviour. There is a mechanism to track the last RTD value when opening, but a bit involved to set up – basically you have to write your own RTD Server (based on ExcelRtdServer base class), and pre-register with COM. You might also be able to tweak your Observables to read from some local persistent cache that you keep, but that would depend on what you are actually doing.

 

-Govert

 

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of armandos...@gmail.com
Sent: 14 November 2022 17:38
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Re: Formulas always execute when the workbook opens, when using refereces

 

Hi Govert, thanks a lot for your response,

 

The reason why we use IsMacroType option, I think is because during the execution of the formulas we need to recover information about the workbook. We invoke functions like this one:

 

XlCall.Excel(XlCall.xlSheetNm, caller)

 

and also this one:

 

 var caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);

 

 

About the RTD functions, your are right, even if I remove the IsMacroType option, the formulas still execute when I open the workbook. So, as far as I understand, the only options we have to avoid this behavior are:

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/691d6c0e-3ff2-48d7-8bcd-da2d7f7dfa99n%40googlegroups.com.

[armandos...@gmail.com]

Thanks Govert, this is really helpful