ExcelDnaUtil.Application generates COMException

[SFun28]

Hi Govert,

I have the following very simple function.

[ExcelFunction( Category = "Lookup & Reference" , Description = "Does
nothing" , HelpTopic = "" , IsExceptionSafe = false , IsHidden =
false , IsMacroType = false , IsThreadSafe = true , IsVolatile =
false , Name = "Read1DArray" )]
public static object
Read1DArray( [ExcelArgument( AllowReference = true , Name = "Range" ,
Description = "A Range" )] object range )
{
Console.Write( ExcelDnaUtil.Application );
return false;
}

If I launch Excel (2010, 64-bit) in the debugger and put a breakpoint
on the first line of code, I find that the following exception is
thrown:
ExcelDnaUtil.Application' threw an exception of type
'System.Runtime.InteropServices.COMException'
base {System.Runtime.InteropServices.ExternalException}: {"The
message filter indicated that the application is busy. (Exception from
HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))"

So it seems that I am unable to call ExcelDnaUtil.Application. I am
sure the application is not busy because the worksheet is blank.

thoughts?

[Govert van Drimmelen]

Hi,

I'll speculate a bit on what you are seeing:

Your function is marked as IsThreadSafe=true, so might execute on a
thread that is not the main Excel calculation thread, where the main
Excel STA COM apartment also lives.
You can investigate what thread is running your function, and compare
with functions what are not marked IsThreadSafe=true.

So when you call ExcelDnaUtil.Application from a different thread,
Excel-DNA will retrieve the Application object from Excel, and COM
will do the cross-thread marshalling to give you a COM proxy that you
can use from your thread (the secondary calculation thread). Now,
still in your function, you essentially call back to the Application
(presumable the default property will get called, and so on). This
call fails to be marshalled across to the main thread, since the COM
processing on the main thread is mostly suspended during
recalculation. Hence you get the exception you report, stating that
the Excel object model is not available.

So, calling the COM automation model from a user-defined function is
unsupported, and certainly makes the function not thread-safe. Don't
mark such a function as IsThreadSafe=true. I superstitiously believe
marking such functions with IsMacroType=true is a good idea.

-Govert

[SFun28]

thanks, Govert! that makes sense. Since there's a performance cost
with IsThreadSafe=false, it doesn't not seem worth it to access the
object model. I wanted to use the object model to read a range's
values into memory instead of using the ExcelReference object. As you
may recall, we had a thread about the lack of support for reading
Dates into memory as DateTime objects. This issue doesn't exist when
calling the object model. The object model will return DateTime
objects. So I wanted to circumvent our limitation =).

[Govert van Drimmelen]

Certainly calling the COM automation interface kills any performance
ambitions you might have.

I think you could use one of the GET.CELL options to get the displayed
string for the cell, instead of the underlying value. (You'd have to
mark your function as IsMacroType=true, and this might also not work
for thread-safe functions.)

But then you'd be able to process the displayed string and you can
decide whether it is a date or not that way. Alternatively you could
check the formatting of the cell to see if it looks like a date/time
format. The COM automation object must be doing something like this.
I'm pretty sure the Excel engine does not know about dates, so the
Range.Value accessor is figuring it out.

-Govert

[SFun28]

good suggestions! thanks, Govert!