Excel 2007 AddIn

[PGO01]

Not sure whether this should be in here or in C# but...

I've created a very simple Excel 2007 AddIn project with VS .NET 2008. I can see the AddIn through Excel > Options, and I can use my code through the ribbon if I add ribbon elements in VS .NET.

I can't however, see any of my code through Formula > Insert Function.

Am I missing something? An attribute for example?

Many thanks

Pete

 

[SkipVought]

Did your addin have VBA coded user defined functions in a MODULE and not in a Worksheet Code Window or the ThisWorkbook Code Window?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PGO01]

I understand if done through Excel VBA it would need to be in a Module, but I'm talking about code written in .NET.

The function I want to see is public, and the class it's in is public, and the VS designer has automatically added the [System.Runtime.InteropServices.ComVisible(true)] attribute for me.

I don't understand why it's not appearing in the Insert Function list...

PS. It's digitally signed too...

 

[SkipVought]

Try this.

In the VB Editor of your MS Application, Tools > References and check the addin as a reference. Or check out the addin manager in the VB editor.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PGO01]

In the end I got it all working and visible by creating a simple C# class library with the 'Register for COM Interop' property set, and the following attributes on my class:

[Guid("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")]
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]

I also had to add the following methods:

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
    Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
    RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);
    key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String);
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
    Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);
}

private static string GetSubKeyName(Type type, string subKeyName)
{
    return @"CLSID\{" + type.GUID.ToString().ToUpper() + @"}\" + subKeyName;
}