Excel Add-ins and COM

Niels van Vliet

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.

How to create...
How to fix...
How does Excel computes your workbook
Value of an argument changes
Function called with zero and null
Add-In, COM: Other problems

How to Create...

Add-In: What is a .Net Add-in, and how to build one

An add-in:

has access to the Automation object of Office,
has write access, for example you can write a given Excel range,
can be used to add new menus, buttons, to handle the right click of the mouse,
can NOT easily add new functions to Excel (called in a cell).

Solution:

How To Build an Office COM Add-in by Using Visual Basic .NET is a poor Microsoft tutorial, which explains the basis. I strongly advice to continue to read this page, before releasing your add-in to end-users. All problems related with Add-in are prefixed by Add-in below.

Com: What is a .Net Com server for Excel, and how to build one

A COM server:

can be used to add a function to Excel, for example to write in an Excel cell '=fact(3)'.
can NOT modify the Excel page, or add menu, etc. It can only return values. Note that, of course, Excel will use the return value to update the cell!

Solution:

Create a new library (dll) project.

Check that at the assembly level, Com Visible is set to false. (Else it will increase your registry base). If you find:

[assembly: ComVisible(true)] // to remove from the property file. If not present => ok.
//replace it by:
[assembly: ComVisible(false)] //to add    to   the property file.

Add a C# file, with the follwing code:

using System.Runtime.InteropServices;
namespace MyAddIn{
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
class MyServer
{
 public double Multiply(double d1, double d2) { return d1 * d2; }

 [ComRegisterFunctionAttribute]
 public static void RegisterFunction(Type t) {
   Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
     "CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
 }
 [ComUnregisterFunctionAttribute]
 public static void UnregisterFunction(Type t) {
   Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
     "CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
 }
}
}

In the property of your project, check the box: "Register for COM interlop" (else use Regasm.exe on the dll.).
In Excel: Tools=>AddIn=>Automation=> select MyAddIn.MyServer. Call Multiply in a cell.
I strongly recommand to read all this page before releasing it to your clients. If the above example does not work, the answer can also be in this page.
You do not need the Office PIA for this given example (because the function Multiply uses only the type double. If you need to input an Excel Range, you need the PIA (see section Microsoft PIA).

FIXME explain that MsCoree not found is not a problem.

C++: What is a C++ unmanage add-in, and how to build it

If you want to code in C or C++, you can create a unmanaged add-in (without .Net).

Solution 1

Follow the tutorial Sample Excel Function Add-in in C
FIXME: explain the tricks to have HTML help in Excel.

Solution 2

Use a modified C++ which works in .Net (ex: C++ /cli).

Solution 3

Use a wrapper generator (ex: swig) to make your native C++ available from .Net. FIXME: put a link to my tutorial.

How to Fix ...

Add-In: Apply the Fix (Patch) 908002

Symptom

You inherit from Extensibility.IDTExtensibility2, but Connect, OnConnection, etc. are never called (add-in does not starts).
If you create an Add-in for Power Point, it works (i.e. Connect is called).

Solution 1

Apply the Patch 908002 to Visual studio.
In the setup project:->right click-> Properties->Prerequisites->Enable "Shared Add-in Support Update for Microsoft .Net Framework 2.0 (KB908002)".
Recompile and re-install.
Note that if it fails on some PCs (ex: for me it failed on a Windows XP in Japanese, with Office 2003), it should correct the problem to run the patch on these PC (see 908002 for more information).

Solution 2

In the directory of Excel.exe (C:\Program Files\Microsoft Office\Office10 on my PC), add an XML file called Excel.exe.config. Open this file with the notepade and add the following lines:

<?xml version="1.0" encoding="Windows-1252"?>
<configuration>
  <startup>
    <supportedRuntime version="v2.0.50727" />
    <supportedRuntime version="v2.0.50110" />
    <supportedRuntime version="v1.1.4322" />
    <supportedRuntime version="v1.0.3705" />
    <requiredRuntime version="v1.0.3705" />
  </startup>
</configuration>

The first solution is better than this one. The second solution (using XML) is not a standard solution. But note that each time I had a problem, the first and second solution had the same behaviour.

Add-In: Disable Items

For some reason, an add-in can be marked "Disabled". One reason is if the add-in failed at start-up.

Symptom

"Excel->Help->About MS Excel->Disable Items" contains your add-in

Solution

Enable it (in the same menu).
Note: read also the section Exception OnConnect.

Add-In: Exception OnConnect

No exception should be received by Excel when the Add-In start up.

Symptom

Excel crash on start-up or,
Some other add-in are not loaded or,
Your add-in seems to be terminated.

Solution

All functions that extend Extensibility.IDTExtensibility2 should catch all exception (using:
```
try {/* Your code */} 
catch(Exception){/* Log the error*/})
```

Add-In: Delegate connected to an Excel event stops to work

If you add a delegate to an Excel event, ex:

menuItems.Last.Click += new ClickEvent(MyStaticFunctionClick);

and that menuItems is a local variable, the delegate stops to be called once the local variable is garbaged.

Symptom

An event trigger an action correctly during a given period of time (ex: 1 minute), and then does not call the action anymore (ex: a button "MyButton" added to the menu bar or Excel).

Solution:

Move your local variable in a global object that is garbaged only when the Add-in is closed. The class that extends Extensibility.IDTExtensibility2 is a good place to have a private field.

Add-In, COM: Microsoft PIA

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

Download the Microsoft Office PIA
You should also read the section assembly or Dll not found

Add-In,COM: Assembly of Dll not found

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:
```
string applicationFolder=Path.GetDirectoryName(Assembly.GetCallingAssembly().Location);
//Then sets the environment variable PATH
Environment.SetEnvironmentVariable("PATH", applicationFolder + ";" +
        Environment.GetEnvironmentVariable("Path"));
```
Important note: you should check that it is not really in the path before adding it. Note 2: I added it to the path for the native library. I am not sure it is necessary for .Net assemblies.
I have reference to the 3 following .Net PIA: Microsoft.Office.Interlop.Excel, Microsoft.Vbe.Interlop and office. I also install them in the Application Folder.
Note: read the section Problem of Version.

Add-In, COM: Problem of Version

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.

COM: Com server registry

Symptom

Your Com server does not start, or stops to work when another version is installed.

Solution

I excplain the solution in the following case, modify the solution according to your case:

my com server type is MyNamespace.MyServer,
my add in wich extends Extensibility is MyNamespace.MyAddin, and the GUID of MyNamespace.MyServer is 3C380F50-AA5E-3AA3-8DDD-366000000555.

In this case:

Check all the different sections in this page (ex: Disable items and Two Dlls)
Check that the macro are enable in Excel.
Check the key named "LoadBehavior" is equal to 3 in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\Addins\MyNamespace.MyServer
Check that there the value is MyNamespace.MyAddin for HKEY_CLASSES_ROOT\MyNamespace.MyAddin
HKEY_CLASSES_ROOT\CLSID\{3C380F50-AA5E-3AA3-8DDD-366000000555} has the value MyNamespace.MyAddin
HKEY_CLASSES_ROOT\CLSID\{3C380F50-AA5E-3AA3-8DDD-366000000555}\InprocServer32 contains the name "CodeBase" which points to the dll which contains the Com server (which contains MyNamespace.MyServer)

Add-In, COM: Two Dlls with COM

Because a bug of Visual, the setup works not correctly in some cases, if you have two dll which needs to be registerd for COM.

Symptom

You have more than one dll with "Register"(for COM) different from "vsdraDoNotRegister" in the setup.
One of 2 COM servers does not works.
If you register it manually (using regasm), then it works.

Solution 1

FIXME explain the solution 1. Basically: add in the setup all assemblies one by one, do not use primary output. Solution explained in several forums, did not work for me.

Solution 2

Add a VB script to your setup which call regasm at the end of the install, and at the beginning of the uninstall.

Add-In, COM: Problem with the .Net Framework

We had some problem on some PC, the Framewok .Net 2 was corrupted.

Symptom

Some .Net files where missing. Do not remember which.

Solution

Re-install the .Net Framework. Note: see the section SP1.

Add-In, COM: Problem with the SP1

We had a very strange error message at installation time. We had to re-install the SP2.

Symptom

I describe the problems in this MSDN forum.
Quickly:"I built an MSI, using Visual 2005, Framework 2, C#, and a setup project.This MSI install an add-in and a Com server.[...] Log: Action ended 14:40:53: DIRCA_CheckFX. Return value 3."
Note: to have the log, use the following command: msiexec /i yourMsi.msi /Log logfile.txt

Solution

Install the SP2.

COM: Result of formula is output in a wrong cell

It cause some problems to Excel to handle not finite doubles. Also, there is some bugs, when a property of the cell is read.

Symptom

A function in Excel called within a given range (ex:A1) writes in other cells (ex: A2).

Solution

If your function returns a double, you should not return +-Infinity or NaN (Not a Number) to Excel. You should:

write a function:

static object CheckDouble(double d) {
   if (double.IsNaN(d)) return "NaN";
   if (double.IsNegativeInfinity(d)) return "NegativeInfinity";
   if (double.IsPositiveInfinity(d)) return "PositiveInfinity";
   return d;}

Then change your function:

"double Bar(){ return Foo();}" 
// to
"object Bar(){return CheckDouble(Foo());}"

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

Add-In, COM: Debug more than 60 seconds

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.

Solution

One solution is present on the MSDN, and more ways to fix the problems are found on the MSDN page Diagnosing Errors with Managed Debugging Assistants.
For sake of simpliciy, I just define the environment variable COMPLUS_MDA equals to 0. This disable the MDA.
Otherwise you can create a file called Excel.mda.config. This file should be in the same directory than Excel.exe. Edit it with the notepad, and add the following text:
```
<mdaConfig>
  <assistants> 
    <contextSwitchDeadlock enable="false" /> 
  </assistants>
 </mdaConfig>
```

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...

How does Excel computes your workbook

This article explains how Excel computes your workbook.

I have a lots of tips, but here are 3 important ones:

Know what volatile means, and how to use Application.Volatile.
Ctrl-Alt-F9 rebuild the dependency tree.
The sheets are computed in alphabetical order (well, it is more complicated than that, but when you name you sheet, try to have the order of calculation which match the alphabetical order).

Value of an argument changes

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.

Function called with zero and null

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).

Excel: Other problems

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

FIXME: To be moved somewhere else:

XmlSerializer won't serialize objects that implement IDictionary

Contact niels AT nvv.name for any suggestion.
Licence: GPL

Weblint