Excel Add-ins and COM

It is difficult to write an Excel AddIn in .Net because of bugs in
Microsoft products. This page explains several problems that I have
had. It concerns Add-in and Com server written in C# using Visual 2005
for Excel XP (also called Excel 2002) and Excel 2003.
Other methods can be used to interface .Net with Excel, but this
methods works using Excel XP(2002), and Visual 2005. Other methods
might not work with Excel XP.

It is possible to create a .Net interface of a library that has a
Com interface. So it is possible to build your own .Net interface,
using directly Excel.exe. But you should use the one built by
Microsoft, which is pretty much the same, but with some additional
functions.

Symptom

You need Range object, you need to use advanced function in Excel.

Solution

It is difficult to know what you want to install in the GAC, in
System32, or in the Application Folder. I call the
Application Folder,
where your application will be installed (ex: C:/Program
File/MyApplication/).
Unless you have a good reason, it is better not to install things in
the GAC (thinks GAC as global variable in a program, and Application
Folder as a local variable). So I am going to explain how I install
everything in the Application Folder.

Symptom

Some of yours .Net assemblies are not found, or,

Some of your native dll are not found (ex: C++ library that you use using PInvoke in C#), or,

You do not want to put all PIA in the GAC, or,

You do not want ot modify the GAC

Solution:

Install the .Net and Native dll in the Application Folder (which will
be chosen by the user at install time, for example it will be
C:/program files/myCompany.

During the OnConnect of the Add-in, Add to the PATH environment
variable the path of the Application folder. Ex:

According to the specification of .Net, you should increment the
version of your library for each new release. Well, I strongly advice
not to. I know that it is very bad not to follow a standard, but today
(01/2007), if you have read all this page you understand that the
amount of bug and problem is pretty high. By changing your version
number you increase the complexity of the install, and the
understanding of the conflict and problems.

Symptom

Visual generated for you a version automatically
incremented. The following line is in your property file:

[assembly: AssemblyVersion("1.0.*.*")]

, or,

You have conflict with different version of the same add-in in Excel, or

Your registry base looks like a battlefield.

Solution

Keep always the same version number, and do not use star("*") in
the version number. Exemple:

AssemblyVersion("1.0.0.0")]

.

Note: this goes against the standard, so this recommendation might be
obsolete or bad in the futur.

If your function read the bold property of another cell, and that this function
is called within the Wizard, it might write in the input cell. This is a bug in Excel
2002(XP), and 2003. See my post for more information and a way to test
this: [Excel][C#][Com Server][Bug]Bug: the result of
formula

In many cases it is not possible to debug more that 60 seconds,
especially with Visual 2005+Excel. There is the following
explaination
on this MSDN
web page:
"The ContextSwitchDeadlock managed debugging assistant (MDA) is
activated when a deadlock is detected during an attempted COM context
transition".

Symptom

After around 1 minute of debug, you receive the following message, and can not continue to debug normally:

TheCLR has been unable to transition from COM context 0x157948 to COM
context 0x1577d8 for 60 seconds. The thread that owns the destination
context/apartment is most likely either doing a non pumping wait or
processing a very long running operation without pumping Windows
messages. This situation generally has a negative performance impact
and may even lead to the application becoming non responsive or memory
usage accumulating continually over time. To avoid this problem, all
single threaded apartment (STA) threads should use pumping wait
primitives (such as CoWaitForMultipleHandles) and routinely pump
messages during long running operations.

Important: this solution disable the MDA, but the MDA is a powefull
tool that helps to finds some complex bugs. By removing the MDA you
might hide a real problem. In the links above there are some solutions
to remove the problem without disabling the MDA (but they have some
drawbacks).
Fixme: one day I will have to
read the
article of Christo understand the problem...

A function called with a given number (ex:"42") is called with a different one (ex:"42.000000000002").

Symptom

You have a function MyFunction:

double static double MyFunction(double d){/*your code*/}

And you call it in Excel with a given number:

=MyFunction(exp(a1))

In the debugger, the value of d differs from one call to another (very small difference, very rare).

Solution

Keep in mind that your are working with double, and that the less significant bits of doubles can change
for a lot of reason.
Another way to think, is to imagine that the function is alwasy call by Excel with +- epsilon.

When Excel creates its dependency tree, your function might be called with uncomputed arguments. The value receive is
the default value of the type (ex: double=> 0, Range =>null...).

Symptom

Your function is called with arguments not initialized.

Solution

I did not find any good solution with .Net, although I know how to do it with xll.
I know a solution which works, but is not a good one (email me if you want to know more,
or if you have a nice solution).

Some problems are not explained in this page (example: do not use AddMonth, use EDate).
If you want me to help you, send a mail to
nielsATnvvDOTname(replace art by '@ 'and dot by
'.').
If you know other problems, you can also send me a quick email, I will add
it here. Thanks.
Niels van Vliet