Development

Writing a simple Excel COM addin

There are not a lot of references that actually tell you how to do this. There are many that tell you partially how to do it, but they don’t give you the full info.

This might now be out-of-date - I think you can do this much more straightforwardly in Excel 2007

We will be writing an “In-Process” COM add-in

  • Start a new project
  • Right-click on the solution, Properties, then “Configuration Properties”,Build,Register for COM Interop,TRUE

Write An Interface for the functions you want to implement.

public interface IMyFunctions {
    string myGetVersion();
  }

Write an implementation for the interface

namespace Junk {
 public class MyFunctions : IMyFunctions {
   public string myGetVersion() {
    return "0.1";
   }
 }
}

Add in COM interop stuff.

Note that quite a few places will tell you to use ClassInterfaceType.AutoDual. Don’t. All that does is ‘magically’ write an interface for this function to export, as it is the interface thats exposed, not the class. By writing the interface, you save that bother.

Note also that only the first named interface is exposed, so if you need multiple interfaces, you will need to create a superinterface for them all.

using System;
using System.Runtime.InteropServices;
 
namespace Junk {
 [ClassInterfaceAttribute(ClassInterfaceType.None), ComVisible(true)]
 public class MyFunctions : IMyFunctions {
   public string myGetVersion() {
    return "0.1";
   }
 
   #region windowsStuffThatsNotUnderstandable
   [ComRegisterFunctionAttribute]
   public static void RegisterFunction(Type type) {
     Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
   }
 
   [ComUnregisterFunctionAttribute]
   public static void UnregisterFunction(Type type) {
     Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type),false);
   }
 
   private static string GetSubKeyName(Type type) {
     string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable";
     return s;
   }
   #endregion
 }
}

Write an implementation for IDTExtensibility2 interface.

This allows you to listen in to when your add-in gets loaded and unloaded. It will allow you to add and remove menus too.

Add reference to Extensibility.dll, and Office Objects v11.

Note that only the first interface is exposed to COM, so the IDTExtensibility2 thing shouldn’t show up.

using Extensibility;
using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
 
namespace Junk {
 [ClassInterfaceAttribute(ClassInterfaceType.None), ComVisible(true)]
 public class MyFunctions : IMyFunctions, IDTExtensibility2 {
 
   Excel.Application application;
 
   public string myGetVersion() {
    return "0.1";
   }
   #region windowsStuffThatsNotUnderstandable
   [ComRegisterFunctionAttribute]
   public static void RegisterFunction(Type type) {
     Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
   }
 
   [ComUnregisterFunctionAttribute]
   public static void UnregisterFunction(Type type) {
     Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type),false);
   }
 
   private static string GetSubKeyName(Type type) {
     string s = @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\Programmable";
     return s;
   }
   #endregion
 
   #region IDTExtensibility2_Implementation
   public void OnAddInsUpdate(ref Array custom) {}
   public void OnBeginShutdown(ref Array custom) {}
 
   public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom) {
     this.application = (Excel.Application) application;
   }
 
   public void OnDisconnection(ext_DisconnectMode disconnectMode, ref Array custom) {
     this.application = null;
   }
 
   public void OnStartupComplete(ref Array custom) {}
   #endregion
 }
}

Add in to Excel

It will be under Tools, Add Ins, Automation

Disting it out

When building an MSI, for some reason I couldn’t get it ro register the assembly automatically.

run:

c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\RegAsm /codebase yourdll.dll

from the deployment directory.

Performance of Excel Add Ins written in C

Writing COM add-ins in C# works fine.

There are some pages out there that say they are incredibly slow. However, the methodology is potentially flawed.

Basically it comes down to how many times you jump through the COM/C# interface. For complex functions that return arrays (like an array formula) there is a single call, which returns a bunch of data. This is incredibly quick.

The widely quoted article is here: http://www.codematic.net/excel-user-defined-functions.htm (seems to have ben moved), but other slightly strange figures are here: http://www.codematic.net/Excel-development/Excel-Visual-studio/excel-user-defined-functions.htm

The target page has 57,000 or so formulae on a single sheet. Due to the interfacing between the CLR and Excel this will give the worst possible result for C#. Many sheets I’ve used however have a low number (1-100) of more complex functions. C# will perform just fine in this case. As always, your milage may vary.

References

http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx

comments powered by Disqus