Asynchronous spreadsheet calls in a Managed XLL

Once I got the simple managed Real Time Data servers working in Excel I decided it was time to add asynchronous worksheet functions. This is the last major feature on my todo list and once it’s complete I’ll be ready to start thinking about beta testing my Managed XLL product.

The idea behind asynchronous worksheet functions is that some worksheet functions may take a long time to complete and it would be better to run them in the background rather than have them block Excel’s recalculations whilst they complete. This means that the Managed XLL needs to manage a thread pool for the asynchronous worksheet function execution and we need to deal with a few issues around passing results back to the sheet. The work that I did on managed RTD servers helped a lot for the asynchronous worksheet functions as I’d already solved the problems of calling into the correct AppDomain from arbitrary threads. Passing results back to the sheet was a bit more thorny; the asynchronous worksheet functions need to be marked as volatile so that Excel will recalculate them whenever a recalculation happens (even if none of their inputs have changed), and the Managed XLL needs to maintain state for each asynchronous call so that it knows if the call is ready to return results, or needs to be run again, or whatever. Finally we need some way of telling Excel to recalculate a given cell when the asynchronous worksheet function is complete; luckily Excel’s COM automation interface comes to the rescue here. The result is pretty cool and once I had the basic asynchronous nature of the calls working I added the ability for them to return interim results as well. I still need to do some work to get all of this to work in versions of Excel prior to 2007 but that shouldn’t be too hard, I hope.

From an addin developer’s perspective asynchronous worksheet functions are as easy as this:

      [AsyncWorksheetFunction]
      static public int AsyncWorksheetFunction1(
         [Optional] [DefaultValue(10000)] int delay)
      {
         Thread.Sleep(delay);
          
         numCalls1++;
    
         return numCalls1;
      }

or maybe this if you want to return interim results or abort a call if requested to do so…

      [AsyncWorksheetFunction]
      static public int AsyncWorksheetFunction2(
         [DefaultValue(10000)] int delay,
         IAsyncWorksheetFunctionCall call)
      {
         for (int i = 0; i < delay; ++i)
         {
            Thread.Sleep(1);
 
            if (call.BreakRequested())
            {
               break;
            }
 
            if (i % 100 == 0)
            {
               call.ReturnInterimResult(i);
            }
         }
 
         numCalls2++;
 
         return numCalls2;
      }

As with my ‘results objects’ there are XLL worksheet functions and commands that allow you to monitor the state of async calls and next on the list is a simple dialog that will allow you to monitor and control the thread pool and the work queue into it.