XlCall.Excel(XlCall.xlcCalculateNow) throws a XlCa

Question

I have an ExcelFunction that queues up some calculations:

[ExcelFunction(...)]
public static void QueueCalcs(... takes ranges ...)
{
    var calcRequests = ... builds list of calc request parameters from ranges ...
    calcRequests.ForEach(QueueCalculation);
}

public static void QueueCalculation(calcRequestParameters)
{
    var bWorker = new BackgroundWorker();
    bWorker.DoWork += bWorkerDoWork;
    bWorker.RunWorkerCompleted += bWorkerRunWorkerCompleted;
    bWorker.RunWorkerAsnc(calcRequestParameters);
}

private static void bWorkerRunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    XlCall.Excel(XlCall.xlcCalculateNow);
}

The worker completes successfully, but the OnComplete delegate throws:

Exception of type 'ExcelDna.Integration.XlCallException' was thrown

If I remove the background worker and use a regular foreach loop followed by a call to XlCall.Excel(XlCall.xlcCalculateNow), the function behaves as expected.

Is doing something like this possible?

Answer 1

You cannot call the Excel C API (XlCall.Excel or any of the ExcelReference methods) from a non-calculation thread.

Excel-DNA has a helper to let you queue work to run in a macro context on the main Excel thread - call ExcelAsyncUtil.QueueAsMacro(...).

It's not exactly clear what you're trying to do, but if you're trying to implement asynchronous functions, this doesn't seem like the right approach.... There is some built-in support for one-time async functions or real-time data feeds based on the Reactive Extensions.