Exception when calling RTD server from Excel

I wrote a VSTO Excel addin using Visual Studio 2010 and after having managed to work around most of the obstacles Microsoft throws into the path of the righteous developer, I finally have to admit defeat.

My project contains a Ribbon with some controls, a custom task pane that allows users to search a database via a RESTful interface and a RTD server that lets them put this data in their worksheets. So far, so ... well, painful, I guess: After a lot of struggle with Interop, ComVisibility and AppDomains (what a great idea!), my current status is as follows.

In the worksheets, I call a wrapper function for RTD like this (snipped):

Public Function call(value as String)
    Dim addin as Office.ComAddIn
    Set addin = Application.ComAddIns("MyAddin")
    addin.Object.RTD(value)
End Function

This is (part of) the addin class:

namespace Some
{
   [Guid("...")]
   [ComVisibleAttribute(true)]
   [ClassInterface(ClassInterfaceType.AutoDual)]
   public class MyAddin {
      [snip]

      public String RTD(String value)
      {
         String returner = null;
         try
         {
            returner = Globals.ThisAddin.Application.WorksheetFunction.RTD(SERVERID, "", value);
         }
         catch(COMException ce) 
         {
            returner = ce.StackTrace;
         }
         return returner;
      }
   }
}

And the relevant part of the RTD Server class:

namespace Some 
{
   [Guid("...")]
   [ComVisibleAttribute(true)开发者_运维百科]
   [ClassInterface(ClassInterfaceType.AutoDual)]
   [ProgId("MyRTDServer")]

   public class Server : Excel.IRtdServer
   {
      [snip]
   }
}

In Debug mode I:

1. Create a, empty new workbook
2. Add an "=RTD(...)" formula to a cell
3. Add a wrapper function call "=call(...)" to a cell
4. Save the workbook
5. Open the workbook
6. Stop Debugging and start it again
7. Open the workbook

I observe:

• At 3, everything works fine
• At 5, everything works fine
• At 7, when recalculating my cells, I get a Unable to get the RTD property of the WorksheetFunction class exception in the cell of 3) and #N/A in the cell of 2). However, I can see that the topics are registered in the RTD server and as soon as the data is available, the exception is replaced with the correct data. Also, if I do NOT recalculate the cells, they display the saved value and then correctly update to the retrieved value once the data is available.

If in deployed mode, I observe:

• At 2, I get #N/A
• At 3, I get a Unable to get the RTD property of the WorksheetFunction class exception

Any help, please? :(

EDIT:

Testing the same procedure with a very basic RTD server in a blank Addin project shows exactly the same results: a loaded excel file displays #N/A if an RTD-formula is recalculated before the server has the data available. I would like to inquire: WTF?

Cheers, Che

---

The 'Unable to get the RTD property....' error is just the Excel object model's way of saying there was an error in the function call.

Does your RTD server have any dependencies on the rest of your add-in? The fact that 2) fails when deployed, makes it look like the RTD server needs some other bits to be initialized before it runs happily. The way the VSTO add-in is put together, your assembly is likely to be loaded totally separately for the RTD server and for the COM add-in, and in your problematic cases the RTD instance is loaded first.

Perhaps you can test with a sample RTD server instead of your own, just to confirm that the error is there and not in the wrapper or elsewhere in the VSTO add-in. Then you can dig into your RTD server to see what goes wrong.

-Govert

Excel-DNA - Free and easy .NET for Excel