XLCall.Excel failing

Apr 28, 2015 at 10:10 AM
Hi,

I'm trying to resize an array using the Resize XlCall [https://exceldna.codeplex.com/SourceControl/latest#Distribution/Samples/ArrayResizer.dna]. I'm calling this resize inside a IExcelObservable's Subscribe method. The XlCall.Excel call throws an exception in this case.

ExcelDna.Integration.XlCallException was unhandled by user code
HResult=-2146233088
Message=Exception of type 'ExcelDna.Integration.XlCallException' was thrown.
Source=ExcelDna.Integration
StackTrace:
   at ExcelDna.Integration.XlCall.Excel(Int32 xlFunction, Object[] parameters)
   at ExcelDnaSample.Sample.TickerObservable.<>c__DisplayClass4.<Subscribe>b__3(Object s, ElapsedEventArgs e) in c:\Workspace\ExcelDnaSample\ExcelDnaSample\ExcelDnaSample\Sample.cs:line 48
   at System.Timers.Timer.MyTimerCallback(Object state)
InnerException:

Here is the entire code
public class Sample
    {
        [ExcelFunction(Description = "My first function")]
        public static object Stringify(double value)
        {
            return ExcelAsyncUtil.Observe("Stringify", value, TickerFunction(value));
        }

        public static ExcelObservableSource TickerFunction(double value)
        {
            var source = new ExcelObservableSource(() => new TickerObservable(value));
            return source;
        }

        public class TickerObservable : IExcelObservable
        {
            private readonly double _value;

            public TickerObservable(double value)
            {
                _value = value;
            }

            public IDisposable Subscribe(IExcelObserver observer)
            {
                var increment = 0;
                var timer = new System.Timers.Timer();
                timer.Interval = 200;
                timer.Elapsed += (s, e) =>
                {
                    var res = new object[2, 3];
                    res[0, 0] = "BID VOL";
                    res[0, 1] = "PRICE";
                    res[0, 2] = "ASK VOL";
                    res[1, 0] = _value;
                    res[1, 1] = 200;
                    res[1, 2] = _value + increment;
                    var a = XlCall.Excel(XlCall.xlUDF, "Resize", res);
                    observer.OnNext(res);
                    increment++;
                };
                timer.Start();

                return new TickerDisposable(timer);
            }
        }

        public class TickerDisposable : IDisposable
        {
            private readonly System.Timers.Timer ticky;
            public TickerDisposable(System.Timers.Timer timer)
            {
                ticky = timer;
            }

            public void Dispose()
            {
                if (ticky != null)
                    ticky.Dispose(); // or Stop, or etc..
            }
        }
    }
Any help would be much appreciated.

Regards,
Ganesh
Coordinator
Apr 28, 2015 at 10:23 AM
Hi Ganesh,

I don't think you'll be able to call the C API from that point in your code - you need to be in a 'macro' context for the resize call to work.
I've not yet been able to make array resize work with asynchronous or RTD functions.

You'll find various discussions on the topic here: https://groups.google.com/forum/#!searchin/exceldna/resize$20async

-Govert
Apr 29, 2015 at 2:19 AM
HI Govert,
Thanks for your prompt response. Is there any other way I can wrtie the array to a range? I tried doing the below, but it doesn't seem to work.
var res = ExcelAsyncUtil.Observe("Stringify", value, TickerFunction(value));
            if (res.Equals(ExcelError.ExcelErrorNA))
                return;

            var arr = (object[,])res;
            var activeSheet = (ExcelReference)XlCall.Excel(XlCall.xlSheetId);

            int rowStart = 3;
            int colStart = 1;
            int rowEnd = arr.GetLength(0);
            int colEnd = arr.GetLength(1);
            var target = new ExcelReference(1, 1, 0, 2, activeSheet.SheetId);
            var headers = new object[0, 3];
            headers[0, 0] = "BID";
            headers[0, 1] = "PRICE";
            headers[0, 2] = "ASK";
            target.SetValue(headers);
            target = new ExcelReference(rowStart - 1, rowEnd - 1, colStart - 1, colEnd - 1, activeSheet.SheetId);
            target.SetValue(arr);
It would be great if I can achieve this.

Thanks.

Regards,
Ganesh
Coordinator
Apr 29, 2015 at 9:39 AM
Edited Apr 29, 2015 at 9:43 AM
Hi Ganesh,

Excel does not support calling SetValue inside a UDF.
For your example, where the array will always be the same size, it will be easiest to just not do the Resize. Then your function can be entered as an array formula over the three cells (you select the three cells, type in the formula and press Ctrl+Shift+Enter to put it in as an array formula. That should work fine with async functions.

For that case you can just return the result of ExcelAsyncUtil.Observe directly in your UDF.
Once that work, you can make it a bit easier for users to press a button that expands the formula to the right size (so a kind of manual resizing). This is the approach discussed here: http://www.technicana.com/resizing-excel-arrays and https://newtonexcelbach.wordpress.com/2015/04/14/re-sizing-array-functions/.
I think this approach should work with the async functions, though I haven't tried it. (That danger is that the initial call that returns #N/A confuses the macro...)

-Govert