Re: [ExcelDna] Question to ExcelAsyncUtil

[Naju Mancheril]

There is a simpler way. Just call your slow function with ExcelAsyncUtil.Run. It will be run on a worker thread. When the value is done, it will be passed by to Excel.

You won't be rewriting the formula, so if your slow function relies on params, it will also update if the params change.

Example:

    private static int MySlowFunction(int ms) {
      Thread.Sleep(ms);
      return 7;
    }

    [ExcelFunction("Util.FuncAsync")]
    public static object FuncAsync(int ms) {
      return ExcelAsyncUtil.Run("sleep", 0, () => MySlowFunction(5000));
    }


See https://groups.google.com/d/topic/exceldna/ezHtJyju_rQ/discussion for some more examples.

[Govert van Drimmelen]

Hi,

You can check the return value of ExcelDnaUtil.Run, and process
further. It will always be ExcelError.ExcelErrorNA when the async
function is still outstanding.

So something like this should work:

public class MyAddIn : IExcelAddIn
{
public void AutoOpen()
{
// Have to initialize ExcelAsyncUtil before use
ExcelAsyncUtil.Initialize();
}
public void AutoClose()
{
}
}

public static class MyFunctions
{

private static int MySlowFunction(int ms)
{
Thread.Sleep(ms);
return 7;
}

public static object FuncAsync(int ms)
{

object result = ExcelAsyncUtil.Run("sleep", ms, () =>
MySlowFunction(5000));
if (result.Equals(ExcelError.ExcelErrorNA))
{
return "!!!BUSY";
}
else
{
return result;
}
}
}

-Govert

On Sep 15, 7:37 pm, CSharp Dummy <csharpdu...@gmail.com> wrote:
> Thanks - that works well.
>
> Any chance yo get rid of the #N/A?

[Naju Mancheril]

Yeah, this is part of the contract for ExcelAsyncUtil.Run(). It assumes that the function output is completely determined by the function params (0 in this case. Now that I think, it should probably take ms here).

Is this your problem? You can pass in any param object which implements Equals() and GetHashCode() and it will reevaluate the function when you pass in a new (not equal) params object.

[Naju Mancheril]

If you have more complicated criteria for when the function should be evaluated (say a ticking price stream), then you will need to do something slightly more complicated.

[CSharp Dummy]

"Now that I think, it should probably take ms here."

Excellent that solved it.

[Govert van Drimmelen]

Hi Vincent,

I suggest you make two wrappers - one to change the #N/A return value
of your function to some other intermediate value, and the second to
change the ExcelAsyncUtil.Run return value again.

So you have:

private static object realAsyncFunction(string param)
{
// might return ExcelErrorNA
}

private static object safeReturnFunction(string param)
{
object result = realAsyncFunction(param);
if (result == ExcelError.ExcelErrorNA)
return "###NA###";
else
return result;
}

public static object excelAsyncFunction(string param)
{
object result = ExcelAsyncUtil.Run("excelAsyncFunction", param,
delegate { return safeReturnFunction(param); });

if (result == ExcelError.ExcelErrorNA)
return "#WAIT - Busy"; // Actually busy
else if (result == "###NA###")
return ExcelError.ExcelErrorNA; // Function returned #N/A
else
return result;
}

You should be able to wrap this in some utility function.

Do you think that would work?

-Govert


On Nov 29, 7:08 pm, Vincent Bouret <vincent.bou...@gmail.com> wrote:
> Any way of changing the default wait return value of ExcelAsyncUtil.Run?
> Our regular process could return a ExcelErrorNA in some cases as the final
> result (and not just waiting for calculation's end).
>
> Vincent

> > > > Seehttps://
> > groups.google.com/d/topic/exceldna/ezHtJyju_rQ/discussionfor
> > > > some more examples.

[ngm]

Hi Vincent,

I think Govert's solution will work. You can also return a sentinel object instead of the "real N/A" and translate it accordingly. Here's an example:


    private static readonly object ReturnNA=new object();


    [ExcelFunction(Name="FuncAsync")]

    public static object FuncAsync(int ms) {

      var result=ExcelAsyncUtil.Run("sleep", ms, () => MySlowFunction(5000));
      if(result.Equals(ReturnNA)) {
        return ExcelError.ExcelErrorNA;
      }
      if(result.Equals(ExcelError.ExcelErrorNA)) {
        return "!!!BUSY";
      }
      return result;
    }


    private static object MySlowFunction(int ms) {
      Thread.Sleep(ms);
      return ReturnNA;
    }

[Naju Mancheril]

How does the interface to your stock price library look?

If it returns an IObservable, then you can use ExceAsyncUtil.Observe.

If it uses polling, then you can try something like the batching server attached to this thread

https://groups.google.com/forum/?fromgroups=#!topic/exceldna/ezHtJyju_rQ

If it uses some other kind of callback, then maybe there's a better way.

[Naju Mancheril]

Sorry, I noticed that you asked this question in a more recent thread and got a response from Govert.

https://groups.google.com/d/topic/exceldna/3t8Q616k8QA/discussion

I'm going to stop monitoring this thread. Please use the new one for all further questions.