Any fix or work around for ExcelAsyncUtil.Run with multipleCells

[Shahanshah khan]

I am trying to fire Excel function as Async function via ExcelAsyncUtil.Run where user has selected multiple Cells that business need i have tabular data

But for first invocation I get new values perfect.. If i try again the same function U know just refresh function return old value after i have putted debugger The delegate is not getting invoked at all second time.

Is there any alternate way to fix this , I only need to invoke one service Async once data is back i want to render it . I want to refresh the data again n again.

Sample code:-

  [ExcelFunction(Name = "GenerateDummySequenceAsync", IsMacroType = true)]

        public static object GenerateDummySequence()

        {

            object asyncResult = ExcelAsyncUtil.Run("reGenerateSequenceAsync", null, () =>

            {

                try

                {

                    //Just to mimic Io /network opration

                    Thread.Sleep(5000);

                    return new[,]

                    {

                        {"Hi", "There"}, {"Hello", "StaleValue"},

                        {"TimeInvoked", DateTime.UtcNow.ToLongTimeString()}

                    };

                }

                catch (Exception ex)

                {

                    Debug.WriteLine(ex.Message);

                    throw;

                }

            });

            // Check the asyncResult to see if we're still busy

            if (asyncResult.Equals(ExcelError.ExcelErrorNA))

                return new[,] { { "#Waiting" } };

            ;

            return asyncResult;

        }

Thanks in Advance

[Govert van Drimmelen]

The combination of an async function with an array formula is problematic, due to quirk in the Excel RTD implementation that Excel-DNA uses for async functions.

I don't know of a workaround for the issue you describe for array formulas.

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

[Shahanshah khan]

Thanks Govert,

is there any way i can forcefully calculate a function

what i am thinking is invoke the method Async n return Waiting 

Once My Service call end i put the data in cache n invoke that method as part of callback end.

But not sure how can i force fully call the function given i have ExcelReference. I used to do the same in ManagedXll.

can you please help me in that.

[Govert van Drimmelen]

The RTD mechanism should be the best way to get Excel to recalculate a particular cell.

One way to avoid the array formulas is to have a single cell that makes the async call, but just returns a 'handle' into an internal data structure. Then you have an array formula that is not async which takes the handle as input parameter and returns the array from the internal data structure. It's a bit more tricky to use, but should work reliably.

Another approach is to use the IObservable mechanism to update the data, instead of the single-value async functions.

Can you explain how ManagedXLL worked with such async array formulas?

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Shahanshah khan [shah2...@gmail.com]
Sent: 17 September 2015 03:34 PM
To: Excel-DNA
Subject: Re: [ExcelDna] Any fix or work around for ExcelAsyncUtil.Run with multipleCells

[Shahanshah khan]

In Managed Xll i used  

Excel.AsyncRangeRefreshFormula(reftext);

where Reftext id EclerefrenceText,

So what it does this invoke that method again.

As i have putted the data in cache , so next invocation will find data in cache n I have time Bound data cache which keep data for 30 sec just to mitigate multiple calls

Is there any example i can use to try IObservable implementation. or simple mechanism to for recalculate the Excel function 

[Govert van Drimmelen]

Do you know whether the ManagedXLL implementation of Excel.AsyncRangeRefreshFormula(reftext); would clear the undo stack?

It might be similar to something like this in Excel-DNA:

ExcelAsyncUtil.QueueAsMacro(() =>

{

    // Use the COM API to re-enter the formula, which will trigger a recalculation:

    Range targetRange = xlApp.Range[targetAddress];

    targetRange.FormulaArray = targetRange.FormulaArray;

});

I'm not sure how this would work, and will would have some side-effects like clearing the user's undo stack.

I'll try to make an array example with the IObservable approach when I get a chance.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Shahanshah khan [shah2...@gmail.com]

Sent: 17 September 2015 03:56 PM

To: Excel-DNA
Subject: Re: [ExcelDna] Any fix or work around for ExcelAsyncUtil.Run with multipleCells

[Shahanshah khan]

Thanks Govert,

i didn't knew that, is there any thing i can follow to  implement IObservable, sorry for the trouble..
Thank you very very much,  

[Govert van Drimmelen]

The async batch sample would be a good start. It has a Task wrapper that implements the IExcelObservable interface.

You can find that sample on GitHub: https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Shahanshah khan [shah2...@gmail.com]

Sent: 17 September 2015 04:13 PM

To: Excel-DNA
Subject: Re: [ExcelDna] Any fix or work around for ExcelAsyncUtil.Run with multipleCells

Thanks Govert,

i didn't knew that, is there any thing i can follow to  implement IObservable, sorry for the trouble..
Thank you very very much,  

[Carlos Rangel]

The normal behavior of the Run Asynchronous function is:

1. When executing by first time, it triggers the delegate asynchronously, meanwhile the returning object will be #NA!

2. Once the Asynchronous call executes the delegate (the result is stored in cache so as to get it when the Run Asynchronous Function is called again), it triggers a CallBack to the original Function in order to get the result.

3. The Original Function Fires again, to get the result, so that executes back the Run Asynchronous Function, but this time it does not executes the delegate, it just get the results from the cache.

In your case you will get a Matrix from your function, to handle it without doing the less, you will need to use Formula array (selecting the group of cells accordingly to the result matrix and then pressing Shift+Control+Enter), this will render the results on the cells.

Note: Unfortunately, the Run from ExcelAsyncUtil does not work well with Formula Array, you will need to create a new Function that behaves the same.