with Automation Add-ins

Introduced in Excel 2002.
It is possible to define and expose 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
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 2022 as Administrator.
New Project, Class Library (.NET Framework)
Change the Name to "ExcelAutomationAddin"
Change the Location to somewhere on your C drive.
Add one reference to this project, (Project > Add Reference)
(1) Assemblies, Extensions: "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(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.Guid("9819051F-E917-4841-8991-7FFBEC1187C9")] 
    [System.Runtime.InteropServices.ClassInterface(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(); 
            } 
        } 

        [System.Runtime.InteropServices.ComRegisterFunction] 
        public static void Method_RegisterFunctions(System.Type type) 
        { 
            // (64 bit Office) 
            Microsoft.Win32.Registry.ClassesRoot.CreateSubKey( 
                     "CLSID\\{" + type.GUID.ToString().ToUpper() + "}\\" + "Programmable"); 

            // (32 bit Office) 
            Microsoft.Win32.Registry.ClassesRoot.CreateSubKey( 
                     "\\Wow6432Node\\CLSID\\{" + type.GUID.ToString().ToUpper() + 
                      "}\\" + "Programmable"); 
        } 

        [System.Runtime.InteropServices.ComUnregisterFunction] 
        public static void Method_UnregisterFunctions(System.Type type) 
        { 
            // (64 bit Office) 
            Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey( 
                     "CLSID\\{" + type.GUID.ToString().ToUpper() + "}\\" + "Programmable"); 

            // (32 bit Office) 
            Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey( 
                     "\\Wow6432Node\\CLSID\\{" + type.GUID.ToString().ToUpper() + 
                     "}\\" + "Programmable"); 
        } 
    } 
} 

Make sure the 2 Guid Ids are replaced using (Tools > Create GUID).
Select (Build > Build Solution).
At this point no registry entries are added in the registry.
Select (Project > Properties).
Display the Build tab.
Tick the "Register for COM Interop" checkbox (requires admin rights).
Close the Properties window.
Select (Build > Build Solution).
When the solution is built a "TLB" file appears in the "\bin\Debug\" folder.
When the solution is built the RegAsm tool automatically runs.
The RegAsm tool adds the necessary COM Interop class and interface information to the registry.

C:\Windows\Microsoft.NET\Framework\v4.0.30319\ RegAsm /codebase ExcelCOMAddin.dll 

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

Links

link - codeproject.com/Articles/7753/Create-an-Automation-Add-In-for-Excel-using-NET