Excel Add-in. Custom Excel Worksheet Functions created with C#

Question

I am creating a class library called MyExcelFunctions. Here is all of the code written with C#:

namespace MyExcelFunctions
{
    public interface IFunctions { string OddOrEven(int number); }

    [ComDefaultInterface(typeof(IFunctions))]
    public class Functions : IFunctions
    {
        public string OddOrEven(int number)
        {
            return number % 2 == 0 ? "Even" : "Odd";
        }

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

        [ComRegisterFunction]
        public static void RegisterFunction(Type type)
        {
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
        }

        [ComUnregisterFunction]
        public static void UnregisterFunction(Type type)
        {
            Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type));
        }
    }


}

I am compiling this project and adding the assembly (.dll file) to Excel.

(Excel options -> Add-Ins -> Manage [Excel Add-ins] -> Go... -> Automation... -> Browse...).

But getting following Error:

I tried adding as Administrator but no success. What am I doing wrong?

I found this scenario here

Answer 1

I solved the problem. Everything I did above is correct except that I have to mark the classes with ComVisible(true). Here is corrected code.

namespace MyExcelFunctions
{
    [ComVisible(true)]
    public interface IFunctions { string OddOrEven(int number); }

    [ComVisible(true)]
    [ComDefaultInterface(typeof(IFunctions))]
    public class Functions : IFunctions
    {
        public string OddOrEven(int number)
        {
            return number % 2 == 0 ? "Even" : "Odd";
        }

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

        [ComRegisterFunction]
        public static void RegisterFunction(Type type)
        {
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
        }

        [ComUnregisterFunction]
        public static void UnregisterFunction(Type type)
        {
            Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type));
        }
    }

}

Then the assembly must be registered with RegAsm.exe utility.

First the project must be rebuilt. After that the regenerated assembly must be saved at destination machine. I saved it in C:\ProgramFiles\MyExcelAddin folder (must be created). Additionally I copied RegAsm.exe into the same folder. It resides in C:\Windows\Microsoft.Net\Framework\v4.0.30319. At last one has to run command prompt with Administrative privileges and go into MyExcelAddin folder. The command to type is RegAsm /codebase ClassLibraryName.dll

After these steps the class will be visible in Automation Servers list in Excel. All we have to do now is to select it and press OK button.

The method created above can be used as a formula in cells.

I hope this will help someone.