Async Array Resize

Peter van Hengel

unread,

Jul 23, 2014, 10:21:02 AM7/23/14

to exce...@googlegroups.com

Hi, I am trying to use the Async method to call a webservice and get data back, the issue is that the data being returned is an array, so once I get the results back, I want to resize the array. Its almost working, the first call is fine, but the 2nd call, with the same parameters doesn't make the call. If I change the input, then a new call is produced once, but not again (for the same params). It seems as though the realtime data server might be responding right away if the params are the same, and I'm guessing this is because I'm not actually returning the result of the ExcelAsyncUtil.Observe, rather the XLCall.Excel to resize. Would this cause issues in disposing/ending the realtime call for this set of params? Is there a way I can fool excel into thinking its done just before starting the resize? Here is a small code block:

public static object GetDataSet(object data_set_name, object data_set_params)

{

if (data_set_name is ExcelMissing || data_set_name is ExcelEmpty )

{

return "Please Provide a Data Set Name.";

}

Uri url = new Uri("http://" + http_endpoint + "/dataset/" + data_set_name.ToString() );

object result = ExcelAsyncUtil.Observe("GetDataSet", new object[] { data_set_name, data_set_params }, () => (GetJsonString(url, null).ToExcelObservable() ));

if (result.Equals(ExcelError.ExcelErrorNA))

{

logMessage("Dispatching: " + url.ToString());

return ExcelError.ExcelErrorGettingData;

}

else

{

logMessage("GotResult: " + url.ToString());

// For Testing Only, will need to parse real json results into a proper data matrix here

string json = result.ToString();

object[,] result1 = { { json, "test0,1" }, { "test 1,0", "test 1,1" } };

return XlCall.Excel(XlCall.xlUDF, "Resize", result1);

//return result1;

}

Peter van Hengel

unread,

Jul 23, 2014, 10:40:00 AM7/23/14

to exce...@googlegroups.com

I'm wondering if there is something odd going on in the ToExcelObservable part:

static class helpers

{

public static IExcelObservable ToExcelObservable<TResult>(this Task<TResult> task)

{

if (task.Equals(null))

{

new ArgumentNullException("task");

}

return new ExcelTaskObservable<TResult>(task);

}

private class ExcelTaskObservable<TResult> : IExcelObservable

{

private Task<TResult> _task;

public ExcelTaskObservable(Task<TResult> task)

{

_task = task;

}

IDisposable IExcelObservable.Subscribe(IExcelObserver observer) //Implements IExcelObservable.Subscribe

{

switch (_task.Status)

{

case TaskStatus.RanToCompletion:

observer.OnNext(_task.Result);

observer.OnCompleted();

break;

case TaskStatus.Faulted:

observer.OnError(_task.Exception.InnerException);

break;

case TaskStatus.Canceled:

observer.OnError(new TaskCanceledException(_task));

break;

default:

_task.ContinueWith(

(continuation) =>

{

switch (continuation.Status)

{

case TaskStatus.RanToCompletion:

observer.OnNext(continuation.Result);

observer.OnCompleted();

break;

case TaskStatus.Faulted:

observer.OnError(continuation.Exception.InnerException);

break;

case TaskStatus.Canceled:

observer.OnError(new TaskCanceledException(continuation));

break;

}

});

break;

}

return DefaultDisposable.Instance;

}

private class DefaultDisposable : IDisposable

{

public static DefaultDisposable Instance = new DefaultDisposable();

public DefaultDisposable()

{

// Do Nothing

}

void IDisposable.Dispose() // was public

{

// Do Nothing

Naju Mancheril

unread,

Jul 23, 2014, 11:30:12 AM7/23/14

to exce...@googlegroups.com

I don't think you want to invoke a UDF for Resize. The following code, in which I invoke ArrayResizer.Resize, worked for me. ArrayResizer is in the samples, under Async/AsyncFunctions/.

    [ExcelFunction(Name="Test.Resize")]
    public static object GetDataSet(object dataSetName) {
      var dsn=dataSetName.ParseValueOrDefault("");
      var result=ExcelAsyncUtil.Observe("GetDataSet", new object[] {dsn}, () => {
        return Task.Run(() => {
          Thread.Sleep(3000);
          var result1=new object[,] {{0, 0}, {0, 1}};
          return result1;
        }).ToExcelObservable();
      });
      if(result.Equals(ExcelError.ExcelErrorNA)) {
        return "Dispatching...";
      }
      var array=(object[,])result;
      return ArrayResizer.Resize(array);
    }

Peter van Hengel

unread,

Jul 23, 2014, 2:05:04 PM7/23/14

to exce...@googlegroups.com

Good News and Bad News - The good news is that I think you are correct about calling the ArrayResizer.Resize directly its a better approach, however it seems to work either way (both direct and calling the XLCall.Excel... I'm not sure what the difference is, but it seems unrelated - the Bad News is that it still doesn't fix the problem of the 2nd call not being fired. After reading through more posts it seems like a while back there may have been some tweaks made to prevent updates running for every cell in the array, and I'm wondering if that's what is preventing the 2nd updates from being sent.

Thank you for your help!

P

Peter van Hengel

unread,

Jul 23, 2014, 2:10:34 PM7/23/14

to exce...@googlegroups.com

I modified your example a little and was able to reproduce the issue with this simple example.

Open excel and in a cell enter

=GetDataSet("test")

In the log you will see (no idea why we get 5 dispatching...)

Running...

Dispatcing...

Running...

Dispatcing...

--- Then in another cell enter the same formula and nothing will happen in the log, but the results will be put into the sheet properly.

Very Strange...

public static object GetDataSet(object dataSetName)

{

//var dsn = dataSetName.ParseValueOrDefault("");

var result = ExcelAsyncUtil.Observe("GetDataSet", new object[] { dataSetName }, () =>

{

return Task.Run(() =>

{

logMessage("Running...");

Thread.Sleep(3000);

var result1 = new object[,] { { 0, 0 }, { 0, 1 } };

return result1;

}).ToExcelObservable();

});

if (result.Equals(ExcelError.ExcelErrorNA))

{

logMessage("Dispatching...");

return "Dispatching...";

}

var array = (object[,])result;

return Resizer.Resize(array);

}

On Wednesday, July 23, 2014 11:30:12 AM UTC-4, ngm wrote:

Naju Mancheril

unread,

Jul 23, 2014, 2:24:14 PM7/23/14

to exce...@googlegroups.com

I don't see that behavior. When I run the function into a new cell, I see another "Running..." printed. I'm surprised that you're not happy with your behavior. If the dataset is the same, then why do you want to force another web call?

If you *really* want to force another web call, then I think you can do it by including the 'caller' cell in your Observe parameters:

      var reference=XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
      if(reference==null) {
        return "No caller";
      }

      var result=ExcelAsyncUtil.Observe("GetDataSet", new object[] {dsn, reference}, () => {
...

Peter van Hengel

unread,

Jul 23, 2014, 2:50:23 PM7/23/14

to exce...@googlegroups.com

Its a bit of a catch 22, the caching is nice, but I want to be able to control it. Initially I thought it was actually the HTTP endpoint being cached (we were playing with the data being fed to excel), as it turns out its something inside of of excel so the call isn't being made at all. We are on the latest exceldna .32, .net 4.5 and excel 2010 - maybe we can rule that out. I'm surprised you see another running. To be clear, we get two running from the first run, nothing from the 2nd. (See below)

When I add the call to the excel reference as you suggest, I get really nasty behavior where it keeps calling over and over and over, see the log at the end (which is still running as I type this so I will prob need to kill excel)

Ive added debugging into our ToExcelObservable class, and I get this:

ToExcelObservable

ExcelTaskObservable

Running...

DefaultDisposable

Dispatching...

Continue RanToCompletion

Dispose

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

and again nothing on the 2nd run! I do see it says resizing, and the results as expects. Oddly its not even calling the delegate function, or the .ToExcelObservable... so it must be something sinde the ExcelAsyncUtil.

Nasty Log:

ToExcelObservable

ExcelTaskObservable

Running...

DefaultDisposable

Dispatching...

Continue RanToCompletion

Dispose

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Dispatching...

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Dispatching...

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Running...

Dispatching...

Continue RanToCompletion

Dispatching...

Dispose

Dispatching...

ToExcelObservable

ExcelTaskObservable

Dispatching...

Running...

Dispatching...

Peter van Hengel

unread,

Jul 23, 2014, 6:14:08 PM7/23/14

to exce...@googlegroups.com

So I downgraded to .30 and I have the same issue where the 2nd call isnt being made. Interesting test I did though:

If I return a string, not an array, and do not call the resize function and paste in a cell it works fine for the first and any additional calls.

However, if I use the same function (one that returns a simple string), and treat it as an array function (eg highlight a matrix of cells, use the same formula, then press Ctl, Shift, Enter), it will make one last call, then any calls after, array or non array do not work.

Any ideas?

P

On Wednesday, July 23, 2014 10:21:02 AM UTC-4, Peter van Hengel wrote:

Peter van Hengel

unread,

Jul 23, 2014, 6:32:56 PM7/23/14

to exce...@googlegroups.com

One more interesting test. If you output the data type of the result the first run you get N/A (expected), but any runs after (with the same params) you get an object[,]

public static object GetDataSet(object dataSetName)

{

//var dsn = dataSetName.ParseValueOrDefault("");

//string guid = System.Guid.NewGuid().ToString();

logMessage("GetDataSet...");

var result = ExcelAsyncUtil.Observe("GetDataSet", new object[] { dataSetName }, () =>

{

logMessage("Inside Runner...");

return Task.Run(() =>

{

logMessage("Running...");

Thread.Sleep(3000);

var result1 = new object[,] { { 0, 0 }, { 0, 1 } };

return result1;

}).ToExcelObservable();

});

logMessage(result.ToString()); // This is the strange thing, first run you get na, the 2nd run you get an object

if (result.Equals(ExcelError.ExcelErrorNA))

{

logMessage("Dispatching...");

return "Dispatching...";

}

var array = (object[,])result;

return Resizer.Resize(array);

}

On Wednesday, July 23, 2014 10:21:02 AM UTC-4, Peter van Hengel wrote:

Govert van Drimmelen

unread,

Jul 23, 2014, 6:48:20 PM7/23/14

to exce...@googlegroups.com

Hi Peter,

Calling Excel RTD (which is used to implement async and observable functions in Excel-DNA) from an array formula is problematic and mostly unsupported by Excel.

From tracing such functions, and in particular looking at the 'caller' for such a function, it seems like an array RTD function is called once for the entire array, with the correct array as caller, and then once for each cell in the array, with the first cell as caller.

If a normal function (non-array) internally calls RTD then Excel connects the RTD topic (if needed) and records some information about this - presumably a reference count for the topic and the cells calling that topic.

If a subsequent call to that function then does not call RTD, then Excel will decrement the reference count, remove that cell from its internal list, and disconnect the topic if appropriate.

For array formulas the story is much more complicated. As I recall, the first call to Excel's RTD function (for the case where the caller is the whole array) will actually fail, other than forcing the subsequent calls with the single-cell caller. In the subsequent calls (one for every array cell) the call to RTD succeed, and I guess internally sets up the internal Topic structures.

One problem is with the disconnect - if Excel RTD is not called from first call (with the full array caller), Excel will not disconnect the topic correctly. So the Excel-DNA behaviour now (not in version 0.32, but in the source check-in from 19 May 2014) is to call Excel RTD for that first call (with the full array caller), even though the topic should be complete. Then Excel calls the function again (once for each cell) and this time we don't call RTD, and then Excel properly cleans up the Topic.

So for older Excel versions (including the release v 0.32) you'll find that topics don't properly 'complete' after the Observable or async function completes (when called from array formulae). I believe the current implementation (the 19 May check-in) works right, even for array formulae.

Your last few posts about getting #N/A only for the first call, and never again, are due to this problem of the topic not 'completing' properly.

You should be testing any array-based Async / Observable stuff with the 19 May check-in.

---------------

Next (or originally), you are trying to combine RTD functions with the resizing helper. I don't know how to do this yet, but it would be nice to have something figured out.

The problem is that the resizing plan depends on the caller information to decide whether formulae resizing is required or not. But, as noted above, RTD array functions don't give reliable caller information - sometimes it's the full array, other times it's only the top-left cell (many times).

It might be so tricky that it requires building a special resizing-RTD-array feature from scratch, with a state machine that mirrors all the (undocumented and unsupported) Excel quirks above to make sure only the correct resizing and RTD calls happen. Resizing must ignore that single-cell caller calls after an array call, but the RTD must get the array call even after resizing, to have the right completion behaviour.

If you come up with a working plan, that would be awesome. A good start would be to add the caller information in your trace - this will start to reveal the unexpected Excel behaviour described above.

But currently this is not on my agenda - I need to focus the time I have on the priorities of those with formal Excel-DNA support agreements.

-Govert

Peter van Hengel

unread,

Jul 24, 2014, 1:21:33 PM7/24/14

to exce...@googlegroups.com

Hi,

Thank you for all the great details. I think I got it all working with the latest build. There are still some kinks to work out but adding the caller debugging to both my function, as well as the resize function was very telling.

I am not going to post my code becuase its still a WIP and not sure it works but here is my logic / findings let me know if you think this works.

Basically it turns out that with Array functions the number of calls is 2x the number of cells in the selected area plus two, this finishes the work.

So simply put, I setup a shared variable per call, when the first call is made, I get the calling range, this is always the array. I calculate the number of cell's in the matrix, then add two, to get the number of calls we expect. So the first cal does this, sets the counter to the number of expected calls, to this function. Then each call made, reduces the count. When finished, the counter reaches 0, and thats how I know to call the resize method on the result. All earlier calls do not call resize, only return the array like normal.

There is another, but, and that is you also need to check if resize is required, becuase if not, theres no way to tell that you dont need to resize and we get stuck in an endless loop. Its a bit complex, but I think its along the lines of how this should work.

My problem is that all the logic exists in my UDF function not is shared libraries but that can be fixed ;)

On Wednesday, July 23, 2014 10:21:02 AM UTC-4, Peter van Hengel wrote:

Casbby

unread,

Aug 4, 2014, 5:40:21 PM8/4/14

to exce...@googlegroups.com

Hi guy,

i downloaded the latest builds 81038 and 81102 but both version will cause the async and resizer into infinite loop.

[ExcelFunction("Obtains historical market data from Yahoo")]

public static object GetHistoricalDataFromYahooAsyncResize(

[ExcelArgument("Yahoo Ticker")] string ticker,

[ExcelArgument("From Date")] DateTime fromDate,

[ExcelArgument("To Date")] DateTime toDate)

{

var caller = Excel(xlfCaller) as ExcelReference;

Debug.Print(string.Format("{0}, called from GetHistoricalDataFromYahooAsyncResize", caller.ToString()));

var result = ExcelAsyncUtil.Observe("GetHistoricalDataFromYahooAsyncResize", new object[] { ticker, fromDate, toDate }, () =>

{

return Task.Run(() =>

{

var result1 = GetHistoricalDataFromYahoo(ticker, fromDate, toDate);

return result1;

}).ToExcelObservable();

});

if (result.Equals(ExcelError.ExcelErrorNA))

{

return "Dispatching...";

}

var array = (object[,])result;

caller = Excel(xlfCaller) as ExcelReference;

Debug.Print(string.Format("{0}, resized from GetHistoricalDataFromYahooAsyncResize", caller.ToString()));

return ArrayResizer.Resize(array);

}

can you guys please help?

regards

casbby

Gopi Annathambu

unread,

Dec 1, 2015, 3:05:10 PM12/1/15

to Excel-DNA

Hi Peter,

We do have the same Problem, "Subsequent call with same parameter getting loaded from Cache" Also Arrary "Resize" triggering the Infinite Loop.

I Could see in this below thread, you have resolved both issues. Could you please guide us, and share some samples of your implementation.

Regards

Gopi

Gopi Annathambu

unread,

Dec 2, 2015, 7:02:04 PM12/2/15

to Excel-DNA

Hi Ngm,

Need a help, I have tried using "reference" to force call every time . But as mentioned in this its running on Infinite Loop. Is there any other approach that I can trigger AsyncUtl.Run() call every time?

Regards

Gopi

Gopi Annathambu

unread,

Feb 11, 2016, 10:19:39 PM2/11/16

to Excel-DNA

Hi,

If you have found any solution for ExcelAsyncUtil.Run, ExcelAsyncUtil.Observe with ArrayResize Hangs Excel?

Regards

Gopi

Gabor Siklos

unread,

Jun 1, 2016, 11:08:23 AM6/1/16

to Excel-DNA

Gopi,

I had the same issue that you did, i.e. Excel going into an infinite loop. What's more is that this used to work for me. That lead me to believe that it must've been something that was introduced in a newer version of Excel-DNA. I downgraded my version of ExcelDNA from 0.33.9 to 0.32.0 (which was the version that I remembered worked before) and resize with async works now.

Hope it helps.

-Gabor

Govert van Drimmelen

unread,

Jun 1, 2016, 11:21:57 AM6/1/16

to exce...@googlegroups.com

Hi Gabor,

I’m quite surprised that you say it worked before, in v 0.32, and would like to have a closer look.

Could you perhaps help me with a simple example that I can test this with myself?

Thank you,

Govert

--
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.

Gabor Siklos

unread,

Jun 1, 2016, 11:48:13 AM6/1/16

to Excel-DNA

Hi Govert,

Here's a dummy example. I'm sorry for the long code sample - I just lumped everything into one file. Most of this (the async and ArrayResizer) is from the Samples in the distro.

Thanks,

-Gabor

using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using ExcelDna.Integration;

namespace AsyncResizeTest {
    public class ArrayResizer : XlCall {
        private static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>();

        [ExcelFunction]
        public static object[,] Resize(object[,] array) {
            ExcelReference caller = Excel(xlfCaller) as ExcelReference;

            if (caller == null) {
                return array;
            }

            int rows = array.GetLength(0);
            int columns = array.GetLength(1);

            if (rows == 0 || columns == 0) {
                return array;
            }

            if ((caller.RowLast - caller.RowFirst + 1 == rows)
                && (caller.ColumnLast - caller.ColumnFirst + 1 == columns)) {
                // Size is already good - just return the result.
                return array;
            }

            int rowLast = caller.RowFirst + rows - 1;
            int columnLast = caller.ColumnFirst + columns - 1;

            // Check for the sheet limits.
            if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1
                || columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1) {
                // Can't resize - goes beyond the end of the sheet - just return #VALUE.
                return new object[,] { { ExcelError.ExcelErrorValue } };
            }

            EnqueueResize(caller, rows, columns);
            ExcelAsyncUtil.QueueAsMacro(DoResizing);

            return array;
        }

        private static void EnqueueResize(ExcelReference caller, int rows, int columns) {
            ExcelReference target =
                new ExcelReference(caller.RowFirst,
                                   caller.RowFirst + rows - 1,
                                   caller.ColumnFirst,
                                   caller.ColumnFirst + columns - 1,
                                   caller.SheetId);

            ResizeJobs.Enqueue(target);
        }

        private static void DoResizing() {
            while (ResizeJobs.Count > 0) {
                DoResize(ResizeJobs.Dequeue());
            }
        }

        private static void DoResize(ExcelReference target) {
            object oldEcho = Excel(xlfGetWorkspace, 40);
            object oldCalculationMode = Excel(xlfGetDocument, 14);

            try {
                // Get the current state for reset later
                Excel(xlcEcho, false);
                Excel(xlcOptionsCalculation, 3);

                // Get the formula in the first cell of the target
                string formula = (string)Excel(xlfGetCell, 41, target);
                ExcelReference firstCell =
                    new ExcelReference(target.RowFirst,
                                       target.RowFirst,
                                       target.ColumnFirst,
                                       target.ColumnFirst,
                                       target.SheetId);

                bool isFormulaArray = (bool)Excel(xlfGetCell, 49, target);

                if (isFormulaArray) {
                    object oldSelectionOnActiveSheet = Excel(xlfSelection);
                    object oldActiveCell = Excel(xlfActiveCell);

                    // Remember old selection and select the first cell of the target
                    string firstCellSheet = (string)Excel(xlSheetNm, firstCell);
                    Excel(xlcWorkbookSelect, new object[] { firstCellSheet });

                    object oldSelectionOnArraySheet = Excel(xlfSelection);
                    Excel(xlcFormulaGoto, firstCell);

                    // Extend the selection to the whole array and clear
                    Excel(xlcSelectSpecial, 6);
                    ExcelReference oldArray = (ExcelReference)Excel(xlfSelection);

                    oldArray.SetValue(ExcelEmpty.Value);
                    Excel(xlcSelect, oldSelectionOnArraySheet);
                    Excel(xlcFormulaGoto, oldSelectionOnActiveSheet);
                }

                // Get the formula and convert to R1C1 mode
                bool isR1C1Mode = (bool)Excel(xlfGetWorkspace, 4);
                string formulaR1C1 = formula;

                if (!isR1C1Mode) {
                    // Set the formula into the whole target
                    formulaR1C1 = (string)Excel(xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
                }

                // Must be R1C1-style references
                object ignoredResult;
                XlReturn retval = TryExcel(xlcFormulaArray, out ignoredResult, formulaR1C1, target);

                if (retval != XlReturn.XlReturnSuccess) {
                    firstCell.SetValue("'" + formula);
                }

            } finally {
                Excel(xlcEcho, oldEcho);
                Excel(xlcOptionsCalculation, oldCalculationMode);
            }
        }
    }

    internal static class ExcelTaskUtil {

        private static object Run<TResult>(string callerFunctionName, object callerParameters,
                                           Func<CancellationToken, Task<TResult>> taskSource) {

            return ExcelAsyncUtil.Observe(callerFunctionName, callerParameters,
                                          delegate {
                                              var cts = new CancellationTokenSource();
                                              var task = taskSource(cts.Token);
                                              return new ExcelTaskObservable<TResult>(task, cts);
                                          });
        }

        private static object Run<TResult>(string callerFunctionName, object callerParameters,
                                          Func<Task<TResult>> taskSource) {

            return ExcelAsyncUtil.Observe(callerFunctionName, callerParameters,
                                          delegate {
                                              var task = taskSource();

return new ExcelTaskObservable<TResult>(task);

                                          });
        }

        private static object Run<TResult>(string callerFunctionName, object callerParameters,
                                          Func<CancellationToken, TResult> function) {

            return Run(callerFunctionName, callerParameters,
                       cancellationToken =>
                            Task.Factory.StartNew(() => function(cancellationToken), cancellationToken));
        }

        private static object Run<TResult>(string callerFunctionName, object callerParameters, Func<TResult> function) {
            return Run(callerFunctionName, callerParameters, () => Task.Factory.StartNew(function));
        }

        public static object AsyncRun<TResult>(string callerFunctionName, object callerParameters, Func<TResult> function) {
            var result = Run(callerFunctionName, callerParameters, function);

            if (result.Equals(ExcelError.ExcelErrorNA)) {
                return ExcelError.ExcelErrorGettingData;
            }

            return result;
        }

        public static object[,] AsyncRunAndResize<TResult>(string callerFunctionName, object callerParameters, Func<TResult> function) {
            var result = Run(callerFunctionName, callerParameters, function);

            if (result.Equals(ExcelError.ExcelErrorNA)) {
                return new object[,] { { ExcelError.ExcelErrorGettingData } };
            }

            return ArrayResizer.Resize((object[,])result);
        }

        // Helper class to wrap a Task in an Observable - allowing one Subscriber.
        class ExcelTaskObservable<TResult> : IExcelObservable {
            readonly Task<TResult> task;
            readonly CancellationTokenSource cts;

            public ExcelTaskObservable(Task<TResult> task) {
                this.task = task;
            }

            public ExcelTaskObservable(Task<TResult> task, CancellationTokenSource cts)
                : this(task) {
                this.cts = cts;
            }

            public IDisposable Subscribe(IExcelObserver observer) {
                switch (task.Status) {
                    case TaskStatus.RanToCompletion:
                        observer.OnNext(task.Result);
                        observer.OnCompleted();
                        break;
                    case TaskStatus.Faulted:
                        observer.OnError(task.Exception.InnerException);
                        break;
                    case TaskStatus.Canceled:
                        observer.OnError(new TaskCanceledException(task));
                        break;
                    default:
                        task.ContinueWith(t => {
                            switch (t.Status) {
                                case TaskStatus.RanToCompletion:
                                    observer.OnNext(t.Result);
                                    observer.OnCompleted();
                                    break;
                                case TaskStatus.Faulted:
                                    observer.OnError(t.Exception.InnerException);
                                    break;
                                case TaskStatus.Canceled:
                                    observer.OnError(new TaskCanceledException(t));
                                    break;
                            }
                        });

                        break;
                }

                // Check for cancellation support
                if (cts != null) {
                    return new CancellationDisposable(cts);
                }
                // No cancellation
                return DefaultDisposable.Instance;
            }
        }

        sealed class DefaultDisposable : IDisposable {

            public static readonly DefaultDisposable Instance = new DefaultDisposable();

            // Prevent external instantiation
            DefaultDisposable() { }

            public void Dispose() { }
        }

        sealed class CancellationDisposable : IDisposable {

            readonly CancellationTokenSource cts;
            public CancellationDisposable(CancellationTokenSource cts) {
                if (cts == null) {
                    throw new ArgumentNullException("cts");
                }

                this.cts = cts;
            }

            public CancellationDisposable()
                : this(new CancellationTokenSource()) {
            }

            public CancellationToken Token
            {
                get { return cts.Token; }
            }

            public void Dispose() {
                cts.Cancel();
            }
        }

    }

    public class Test {
        private static Random rand = new Random();

        private static object[,] SimpleArrayTest() {
            Thread.Sleep(3000);
            int size = rand.Next(1, 15);
            var result = new object[1, size];

            for (int i = 0; i < size; i++) {
                result[0, i] = rand.NextDouble();
            }

            return result;
        }

        [ExcelFunction(Name = "ArrayTest", IsVolatile = false)]
        public static object ArrayTest() {
            return ExcelTaskUtil.AsyncRunAndResize("ArrayTest", new object[] { }, () => SimpleArrayTest());
        }
    }

}

Govert van Drimmelen

unread,

Jun 1, 2016, 11:58:28 AM6/1/16

to exce...@googlegroups.com

Thank you very much Gabor, I'll have a closer look at this issue again.

-Govert

Govert van Drimmelen

unread,

Jun 1, 2016, 5:53:55 PM6/1/16

to exce...@googlegroups.com

Hi Gabor,

I've had a look a the behaviour of your example under v0.32 and v0.33.

I do see the problem under v0.33 where the recalculation does not stop, and that it does not appear under v0.32.

But the behaviour under v0.32 is incorrect too. The function does not recalculate with either F2+Enter or Ctrl+Alt+Shift+F9. It works because the async function is 'stuck', which is not the right behaviour either.

My basic guidance that async + array formulas should not be supported (and hence the resizer does not work for async either).

I'll look a bit more, though, and write back.

-Govert

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Govert van Drimmelen [gov...@icon.co.za]
Sent: 01 June 2016 05:57 PM
To: exce...@googlegroups.com
Subject: RE: [ExcelDna] Re: Async Array Resize

Gabor Siklos

unread,

Jun 2, 2016, 9:54:48 AM6/2/16

to Excel-DNA

Hi Govert,

Thank you very much for looking into this. From searching around it seems to be that this is and issue/feature that other people would really want as well. Anyways, Shift+Ctrl+Alt+F9 works perfectly well for me and arrays do recalculate and get resized accordingly. I had to modify my little dummy UDF a bit, so that it returns different results though:

public class Test {
private static Random rand = new Random();

private static object[,] SimpleArrayTest(int maxSize) {

Thread.Sleep(3000);

int size = rand.Next(1, maxSize);

var result = new object[1, size];

for (int i = 0; i < size; i++) {

result[0, i] = rand.NextDouble();

}

return result;

}

[ExcelFunction(Name = "ArrayTest", IsVolatile = false)]

public static object ArrayTest(int maxSize) {

return ExcelTaskUtil.AsyncRunAndResize("ArrayTest", new object[] { maxSize }, () => SimpleArrayTest(maxSize));

}

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

Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--
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 exc...@googlegroups.com.

Govert van Drimmelen

unread,

Jun 2, 2016, 10:17:11 AM6/2/16

to exce...@googlegroups.com

Hi Gabor,

Under v 0.32 and your new function, I don’t find that at recalculates and returns new values when pressing Ctrl+Shift+Alt+F9.

If you change the parameter value to a different maxSize, then it will change, but that’s because we’re making a new RTD topic.

I understand that this is a feature people would like. It just doesn’t work right in v0.32, and in 0.33 the RTD part of the problem is fixed – this disconnect now happens correctly. A side effect is that the resizer doesn’t work for async arrays.

I’m not sure whether async arrays can be made reliable, or whether array resizer can work for async arrays. But the v0.32 behaviour is not correct.

Gabor Siklos

unread,

Jun 2, 2016, 10:52:59 AM6/2/16

to Excel-DNA

Hmmm, the plot thickens...

I can actually make it recalculate/resize, but only once and then not again. If I enter a new "=ArrayTest(...)", press Enter, wait for the data to appear, and then press Ctrl+Shift+Alt+F9, it does recalculated and new values appear, but if I try to do it again nothing happens.