with Automation Add-ins

Introduced in Excel 2002.
It is possible to define worksheet functions.
Automation add-ins use COM Interop to provide additional worksheet functions that can be used in your workbooks.
These are COM DLLs (ActiveX DLLs).
Automation Add-ins can be in-process or out-of-process COM servers
https://www.quantnet.com/threads/how-to-create-udf-in-excel-using-c-and-visual-studio-2008.1260/


The registration of the worksheet functions is done automatically when the COM component is registered.
This automatic registration takes place because of the ComRegisterFunction and ComUnregisterFunction attributes.
Excel will only be able to access these functions once the corresponding registry sub-keys have been added.
[System.Runtime.InteropServices.Optional]



Creating

Open Visual Studio 2017.
New Project, Class Library (.NET Framework)
Change the Name to "ExcelAutomationAddin"
Change the Location to somewhere on your C drive.
References, Add Reference, Assemblies > Extensions, Add "Microsoft.Office.Interop.Excel" (version 15)


using Excel = Microsoft.Office.Interop.Excel; 

namespace ExcelAutomationTest5
{
    [System.Runtime.InteropServices.Guid("B2AEEA31-CB67-4F3E-9F6E-BA484B59D23C")]
    [System.Runtime.InteropServices.InterfaceType(
        System.Runtime.InteropServices.ComInterfaceType.InterfaceIsDual)]
    [System.Runtime.InteropServices.ComVisible(true)]
    public interface IBetterFunctions
    {
        string AddTwoNumbers(double value1, double value2);
        string ToUpperCase(string input);
        string NumberOfCells(Excel.Range Range);
    }


    //[System.Runtime.InteropServices.ProgId("ExcelAutomationTest3.BetterFunctions")]
    //[System.Runtime.InteropServices.ComDefaultInterface(typeof(IBetterFunctions))]

    [System.Runtime.InteropServices.Guid("9819051F-E917-4841-8991-7FFBEC1187C9")]
    [System.Runtime.InteropServices.ClassInterface(
        System.Runtime.InteropServices.ClassInterfaceType.None)]
    [System.Runtime.InteropServices.ComVisible(true)]
    public class BetterFunctions : IBetterFunctions
    {
        public BetterFunctions()
        { }

        public string AddTwoNumbers(double value1, double value2)
        {
            double retvalue;
            double val1;
            double val2;

            try
            {
                System.Globalization.CultureInfo oldCI =
                      System.Threading.Thread.CurrentThread.CurrentCulture;

                System.Threading.Thread.CurrentThread.CurrentCulture =
                      new System.Globalization.CultureInfo("en-US");

                Double.TryParse(value1.ToString(), System.Globalization.NumberStyles.Any,
                      System.Globalization.CultureInfo.CurrentCulture, out val1);

                Double.TryParse(value2.ToString(), System.Globalization.NumberStyles.Any,
                     System.Globalization.CultureInfo.CurrentCulture, out val2);

                retvalue = val1 + val2;

                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                return retvalue.ToString();
            }
            catch (System.Exception ex)
            {
                return ex.ToString();
            }
        }

        public string ToUpperCase(string input)
        {
            string retvalue = "";

            try
            {
                System.Globalization.CultureInfo oldCI =
                       System.Threading.Thread.CurrentThread.CurrentCulture;

                System.Threading.Thread.CurrentThread.CurrentCulture =
                       new System.Globalization.CultureInfo("en-US");

                retvalue = input.ToUpper();

                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                return retvalue;
            }
            catch (System.Exception ex)
            {
                return ex.ToString();
            }
        }

        public string NumberOfCells(Excel.Range Range)
        {
            int retvalue;

            try
            {
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Excel.Range r = Range as Excel.Range;

                retvalue = r.Cells.Count;

                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                return retvalue.ToString();
            }
            catch (System.Exception ex)
            {
                return ex.ToString();
            }
        }

        //public double CalculateArea(object Range)
        //{
        // Excel.Range r = Range as Excel.Range;
        // return Convert.ToDouble(r.Width) * Convert.ToDouble(r.Height);
        //}

        //public string ToUpperCase2(string input)
        //{
        // return input.ToUpper();
        //}

        //public double AddNumbers(
        // double Number1,
        // object Number2,
        // object Number3)
        //{
        // double result = 0;
        // result += Convert.ToDouble(Number1);

        // if (!(Number2 is System.Reflection.Missing))
        // {
        // Excel.Range r2 = Number2 as Excel.Range;
        // double d2 = Convert.ToDouble(r2.Value2);
        // result += d2;
        // }
        // if (!(Number3 is System.Reflection.Missing))
        // {
        // Excel.Range r3 = Number3 as Excel.Range;
        // double d3 = Convert.ToDouble(r3.Value2);
        // result += d3;
        // }
        // return result;
        //}


        [System.Runtime.InteropServices.ComRegisterFunctionAttribute]
        public static void RegisterFunction(Type type)
        {
            Microsoft.Win32.RegistryKey regkey;

            Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
                     Method_GetSubKeyName(type, "Programmable"));

            regkey = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(
                      Method_GetSubKeyName(type, "InprocServer32"), true);

            regkey.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll",
                      Microsoft.Win32.RegistryValueKind.String);
        }

        [System.Runtime.InteropServices.ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(Type type)
        {
            Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
                     Method_GetSubKeyName(type, "Programmable"), false);
       }

        private static string Method_GetSubKeyName(
            Type type,
            string subKeyName)
        {
            System.Text.StringBuilder sbuilder;
            sbuilder = new System.Text.StringBuilder();
            sbuilder.Append(@"CLSID\{");
            sbuilder.Append(type.GUID.ToString().ToUpper());
            sbuilder.Append(@"}\");
            sbuilder.Append(subKeyName);
            return sbuilder.ToString();
        }
    }
}

Automation Add-ins

Open Excel, Create a blank new workbook.
Select (File > Options) and select the Add-ins tab.
Change the Manage drop-down to "Excel Add-ins" and press Go.
Press the Automation button.
This dialog box displays a list of all the COM registered servers on that computer.
Find "ExcelAutomationAddin" in the list.
Select this entry and press OK.

This item will be added to the "Add-ins available" list.
na



Excel Function

Once the Automation add-in has been loaded the new worksheet function is available.
Press the Insert Function button next to the formula bar.
Select the category drop-down and choose "ExcelAutomationAddin.BetterFunctions".
na


Press OK to insert this function
na




Select (Project > Properties)
Display the Build tab and tick "Register for COM Interop"
Select (Build > Build Solution)
Launch Excel
Display the Add-ins dialog box
Click on the Automation button to display a list of COM servers.
Browse for "ExcelAutomation.BetterFunctions" and press OK
The item will be added to the Add-ins available list
Press OK


Select Insert Function
Click on the Category drop-down and select "ExcelAutomation.BetterFunctions".
Select "AddTwoNumbers" and press OK
Enter 2 numbers and press OK
If you clean the solution then the functions will no longer be available





© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext