UDF doesn't update if global variable updates

[Gordon Y]

Hi Govert,

If I store a global variable using one funciton, and call it from another function, the second function doesn't seem to automatically update unless I make it volatile. See below example:

        static string global_name;

        [ExcelFunction(Description = "My first .NET function")]
        public static string SayHello(string name)
        {
            global_name = name;
            return "Hello " +  global_name;
        }

        //[ExcelFunction(IsVolatile = true)]
        public static string SayHello2()
        {
            return "Hello " + global_name;
        }

So if in the spreadsheet, I set A1 = SayHello(A2), and B1 as SayHello2(). Then my B1 doesn't change if I change the value of A2. 

What's a better way to achieve this?

Thanks for your help!

Gordon

[Brian Satola]

Re-calculation is controlled by Excel, which is based on cell references in the functions (SayHello2 has none). Therefore, you cannot expect SayHello2 to update if global_name updates, as it is blind to the change (i.e., there is no trigger to update B1 on a change to A1 as far as Excel is concerned).

[Kedar Kulkarni]

A better way to achieve it could be using asynchronous RTD functions.. it has a learning curve and needs the excel calculation mode to be automatic.  We just need to push a new value to the observable stream using onnext and Excel will refresh it.

example https://github.com/Excel-DNA/Samples/tree/master/RtdClocks 

The formula updates even if we do not press F9. This could be an overkill for a simple use case but it is possible.

--
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/f35ddb3f-90e0-4a0d-b083-006b8e05f28fn%40googlegroups.com.

[Gordon Y]

Thank you Kedar. Looks like my step is to understand this RTD mechanism.

Please feel free to share anything that you think that may be helpful. I'm learning everything from scratch, including C#.

Thanks!

Gordon

[Kedar Kulkarni]

a quick and dirty example..  GetAsyncValue should refresh if any event updates global state (call SetGlobalStateValue).

using ExcelDna.Integration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reactive;
using System.Reactive.Linq;
using System.Reactive.Subjects;
using System.Reactive.Threading.Tasks;
using System.Text;
using System.Threading.Tasks;

namespace excelDnaReactiveExample
{
    public static class Functions
    {
        [ExcelFunction(Description = "Set the global state value")]
        public static object SetGlobalStateValue(int i)
        {
            GlobalState.ValueSubject.OnNext(i);
            return i;
        }

        [ExcelFunction(Description = "Returns the global state value")]
        public static object GetGlobalStateValue()
        {
            return GlobalState.ValueSubject.Value;
        }

        [ExcelFunction(Description = "Async UDF that updates when global state changes")]
        public static object GetAsyncValue()
        {

            string functionName = "GetAsyncValue";
            object paramInfo = null; // could be one parameter passed in directly, or an object array of all the parameters: new object[] {param1, param2}
            return ObservableRtdUtil.Observe(functionName, paramInfo, () => GlobalState.ValueSubject.AsObservable());
        }

        public static class GlobalState
        {
            private static int _value;
            public static BehaviorSubject<int> ValueSubject { get; } = new BehaviorSubject<int>(_value);

            public static int Value
            {
                get => _value;
                set
                {
                    _value = value;
                    ValueSubject.OnNext(value); // Notify subscribers when the value changes
                }
            }
        }

        public static class ObservableRtdUtil
        {
            public static object Observe<T>(string callerFunctionName, object callerParameters,
                Func<IObservable<T>> observableSource)
            {
                return ExcelAsyncUtil.Observe(callerFunctionName, callerParameters,
                    () => new ExcelObservable<T>(observableSource()));
            }

            // An IExcelObservable that wraps an IObservable
            class ExcelObservable<T> : IExcelObservable
            {
                readonly IObservable<T> _observable;

                public ExcelObservable(IObservable<T> observable)
                {
                    _observable = observable;
                }

                public IDisposable Subscribe(IExcelObserver excelObserver)
                {
                    var observer = new AnonymousObserver<T>(value => excelObserver.OnNext(value), excelObserver.OnError,
                        excelObserver.OnCompleted);
                    return _observable.Subscribe(observer);
                }
            }
        }
    }
}

thanks 

Kedar

(Looking for my next assignment - kedarkulkarni78 at gmail)

[Gordon Y]

Oh wow, this is very helpful, open me up to the observable pattern. Thank you so much!

[Gordon Y]

I see the word "RTD" server is mentioned a lot from my other google searches. But this doesn't seem to set up any server at all, and allow states to happen. When's an RTD server needed?

[Kedar Kulkarni]

this is just my personal opinion..  I usually avoid volatile formulas as much as possible. 

By default a sum formula - if the arguments (precedents) don't change - the sum output wont change. so there is no need to create a RTD server for the same. 

If I have a formula that needs to update the value even if the precedents have not changed then I would create RTD server to update the cell when a new value is available. 

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/d3daf5c2-ed54-4d39-9039-f6afdad7abafn%40googlegroups.com.

[Gordon Y]

Thanks, this solution actually works perfect for me, for my little experiment. 

[Gordon Y]

Hi Kedar,

I realized that when I pack the solutions for deployment, the System.Reactive does not get packed together with it. Is there a way to pack the Reactive as well? Here's the error message when I run my excel with the xll instead of using Visual Studio:

System.IO.FileNotFoundException: Could not load file or assembly 'System.Reactive, Version=6.0.0.0, Culture=neutral, PublicKeyToken=94bc3704cddfc263' or one of its dependencies. The system cannot find the file specified.
File name: 'System.Reactive, Version=6.0.0.0, Culture=neutral, PublicKeyToken=94bc3704cddfc263'
   at ClassLibrary2.DatasetState.SetGlobalStateValue(Int32 i)
   at ClassLibrary2.Class1.setup_deals(Object[,] deal_list)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

Thanks,

Gordon

[Govert van Drimmelen]

Hi Gordon,

See this message about packing extra file into the single file  -packed.xll

https://groups.google.com/g/exceldna/c/O_0AJUFlWbQ/m/G2yeJyqdAAAJ

-Govert