Managed Automation Add-ins
I've been thinking more about calling unmanaged XLL UDFs from managed code than about managed automation add-ins lately, but an automation add-in issue came up the other day, and I thought I'd throw it out there. If you want to build managed UDFs without going to the extreme of managed XLLs, you have a couple of simple choices:
· You can use a VBA wrapper approach as described in Paul Stubbs' post here:
· Or build an automation add-in instead, as described by Eric Carter here:
… and if you’re going to build a managed automation add-in, you’ll want to shim it, which you can do with the COM Shim Wizard v2.3, described here:
I've blogged about managed UDFs via automation add-ins many times in the past, including the following:
· Implementing IDTExtensibility2 in an Automation Add-in
· Example Shimmed Automation Add-in
· Excel Interop Types in Shimmed Automation Add-ins
If you want to build a simple managed automation add-in that does not implement IDTExtensibility2, Eric's excellent post covers the basic procedure. If you do choose this approach, there are a few additional issues you should bear in mind:
· ProgId limitations
· Add-in Manager limitations
· Function Wizard limitations
· Default interface limitations.
Let's take each of these in turn. First, ProgIds. It is common practice in managed code to specify namespaces and typenames that are verbose, but this can easily exceed the maximum allowed length of a ProgId (39 characters). If you do this, there's no indication of the problem until you try to register the add-in with Excel – and even then, Excel simply tells you that the add-in is invalid, without providing any more detail. To avoid this problem, you should always specify a ProgId explicitly, using the ProgId attribute. Also remember that underscores are legal in managed code, but illegal in ProgIds.
Second, Excel's Add-in Manager lists automation add-ins by their registered ProgId, and there is no way to change this behavior. This is an Excel limitation on automation add-ins.
Third, Excel's Function Wizard lists each automation add-in as its own separate function category. The category name is the add-in's ProgId, and you cannot specify a different category name for UDFs in automation add-ins. You also cannot specify function descriptions, argument descriptions, or help for automation add-in UDFs. These are limitations imposed by Excel on automation add-ins, and are described here.
Lastly (as in Eric's post), you can simply attribute the UDF class with ClassInterfaceType.AutoDual. This generates a class interface, which in other contexts is usually frowned upon. A class interface is a generated interface that exposes all public methods, properties, fields and events of the managed type. The basic problem with this is that any changes in a future version to the layout of the type or any base types will break (early-bound) COM clients that bind to the interface. For details see here and here. I discussed the use of class interfaces in automation add-ins in an earlier post. In the specific case of automation add-ins, where the early-bound vtbl interface is not used, the inherent fragility of class interfaces is not in practice a problem. However, there is another problem: display of unwanted methods in the Function Wizard.
Your automation add-in class will implicitly derive from System.Object (all managed classes do). Making the class ComVisible and attributing it with ClassInterfaceType.AutoDual will expose the underlying System.Object methods, Equals, GetHashCode, ToString and GetType. These will be in the typeinfo for the add-in, and will therefore show up in Excel's Insert Function dialog. One approach to prevent these members from showing up is to override them and make them ComVisible(false), as shown below:
[ComVisible(false)]
public override bool Equals(object obj)
{
return base.Equals(obj);
}
[ComVisible(false)]
public override int GetHashCode()
{
return base.GetHashCode();
}
[ComVisible(false)]
public override string ToString()
{
return base.ToString();
}
The problem with this approach is that System.Object.GetType is non-virtual so we cannot override it. So, even if we hide the other 3 methods this way, we'll still be left with GetType.
A second approach is to avoid using a class interface. Instead, specify the UDF interface explicitly, and make it ComVisible(true). Also, attribute your add-in class with ClassInterfaceType.None. A sample class that uses this approach (and suitable explicit ProgId and GUID) is shown below:
[ComVisible(true)]
public interface ISimpleUDFs
{
double MultiplyTwo(double x, double y);
}
[ClassInterface(ClassInterfaceType.None)]
[ComVisible(true)]
[Guid(SimpleUDFs.Guid)]
[ProgId("SimpleAutomationAddIn.SimpleUDFs")]
public class SimpleUDFs : ISimpleUDFs
{
public SimpleUDFs()
{
}
#region Automation Add-in Registration
internal const string Guid = "C426EC79-2F31-44bf-BB0A-BE9A357FA5B3";
const string SubKeyName = @"CLSID\{" + Guid + @"}\Programmable";
[ComRegisterFunction]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(SimpleUDFs.SubKeyName);
}
[ComUnregisterFunction]
public static void UnRegisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(SimpleUDFs.SubKeyName);
}
#endregion
public double MultiplyTwo(double x, double y)
{
return x * y;
}
}
Bear in mind, however, that without a class interface, the default interface on the add-in class will be the first one implemented (that is, ISimpleUDFs in the example above). That means that if your add-in class implements additional interfaces, they will not be visible to automation clients, and any methods defined in such interfaces will not be available as UDFs. That may be a limitation you can live with – after all, you could work around this by simply implementing one interface per class, and exposing multiple classes from your add-in. It may also, of course, be a deliberate part of your design.
If you want to shim your automation add-in (which you should do), you can either write your own shim, or use the COM Shim Wizard. If you want to use the COM Shim Wizard, you need to implement IDTExtensibility2 in your add-in class – this is because the Wizard searches explicitly for this interface when generating a shim for any add-in (both regular COM add-ins and automation add-ins). If you don't need IDTExtensibility2 in your add-in for anything else, then you can simply implement each of its methods in the default manner generated by Visual Studio, for example:
public void OnConnection(
object Application, ext_ConnectMode ConnectMode,
object AddInInst, ref Array custom)
{
throw new Exception("The method or operation is not implemented.");
}
On the other hand, if you do need IDTExtensibility2 (perhaps because you want to get access to Excel's exposed COM OM from your automation add-in), then you're back to generating a class interface for your add-in class (so that you can expose more than one real interface via the combined class interface).
Comments
- Anonymous
April 01, 2008
Sir,Pls. describe the abouve in V. Basic 2008.Thanks. - Anonymous
September 02, 2008
Hi thereI have created a managed automation addin for Excel using C#, exposing some UDFs. All works fine, including deployment, until the addin is installed on a machine that has a mixed culture environment; for example German Excel on English Windows or vice versa. In this case there is a problem that parameters expressed as cell references result in the UDF yielding a #VALUE error. For example DoSomething(B1) results in #VALUE. B1 contains a string in this case. This only seems to happen on mixed installations. I do not know where to look or how to fix this. Any help would be more than welcome!! - Anonymous
September 02, 2008
FrankSz - this looks like a locale ID (LCID) issue. This is a known issue when developing Excel solutions in a mixed culture environment. For more information, see here: http://support.microsoft.com/kb/246501/.FYI - VSTO solves this problem via its LCID Proxy. Although you can only use this with VSTO solutions, its worth reading the documentation so you can understand the problem: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.excellocale1033proxy.aspx and http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.excellocale1033attribute.aspx. - Anonymous
September 25, 2008
Wish I found this article sooner, wasted about a day trying to put help in the function wizard window.However I have found you can do something like thisif (xl.CommandBars["Standard"].Controls[1].Enabled == false){return "In the function window";}to return a different message. You are very limited on charaters and also useful if you dont want your udf to evalute when in the function window.