Create User Defined Functions in Excel with Automation

[EDN Admin]

I am trying to develop a simple Automation addin for Excel, and not having much luck. I am currently targeting Excel 2007, but hope to carry on to 2010. I followed the instructions given on http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx and
I can activate the addin in Excel, but my function does not show up. I tried various ComVisible things but I couldnt get my UDF.
Here is my code (which creates a class library):

<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; using System;
<span style="color:Blue; using System.Runtime.InteropServices;
<span style="color:Blue; using Microsoft.Win32;

<span style="color:Blue; namespace ExcelAutomationUdfs
{
[Guid(<span style="color:#A31515; "8069C328-429C-4AAF-A5FC-9EEB6E1BABD4")]
[ComVisible(<span style="color:Blue; true)]
<span style="color:Blue; public <span style="color:Blue; class UDFs
{
<span style="color:Blue; public UDFs()
{

}

<span style="color:Green; // My UDF
<span style="color:Blue; public <span style="color:Blue; double MYPI()
{
<span style="color:Blue; return Math.PI;
}

[ComRegisterFunction]
<span style="color:Blue; public <span style="color:Blue; static <span style="color:Blue; void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, <span style="color:#A31515; "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, <span style="color:#A31515; "InprocServer32"), <span style="color:Blue; true);
key.SetValue(<span style="color:#A31515; "", Environment.SystemDirectory + <span style="color:#A31515; @"mscoree.dll", RegistryValueKind.String);
}

[ComUnregisterFunction]
<span style="color:Blue; public <span style="color:Blue; static <span style="color:Blue; void UnregisterFunction(Type type)
{

Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, <span style="color:#A31515; "Programmable"), <span style="color:Blue; false);
}

<span style="color:Blue; private <span style="color:Blue; static <span style="color:Blue; string GetSubKeyName(Type type, <span style="color:Blue; string subKeyName)
{
System.Text.StringBuilder s = <span style="color:Blue; new System.Text.StringBuilder();
s.Append(<span style="color:#A31515; @"CLSID{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(<span style="color:#A31515; @"}");
s.Append(subKeyName);
<span style="color:Blue; return s.ToString();
}
}
}
[/code]
<br/>
<br/>


View the full article