with COM Add-ins

Introduced in Excel 2000.
COM Add-ins must implement the Extensibility.IDTExtensibility2 interface.
If you want an add-in to appear in the COM Add-ins dialogs then it must implement this interface.
Once the C# dll has been built it needs to be added to the registry using the RegAsm tool.
In addition to the normal COM Interop registration the dll must also be registered with Microsoft Office.
This requires adding another registry key manually under HKEY_CURRENT_USER.

Creating

Open Visual Studio 2022 as administrator.
New Project, Visual C#, Windows Desktop, Class Library (.NET Framework).
Change the Name to "ExcelCOMAddin".
Change the Location to somewhere on your C drive.
Check the .NET Framework version is correct and press OK.
Rename the Class1.cs file to MyConnect.cs.
Add three references to this project, (Project > Add Reference).
(1) Assemblies, Extensions: "Extensibility"
(2) Assemblies, Extensions: "Microsoft.Office.Interop.Excel" (version 15)
(3) COM, Type Libraries: "Microsoft Office 16.0 Object Library"
Remove the existing (default) class.
Add the following interface to the MyConnect.cs file.

namespace ExcelCOMAddin 
{ 
    [System.Runtime.InteropServices.Guid("5FA4884F-AA70-40C0-A4EC-F5390930B152")] 
    [System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsDual)] 
    [System.Runtime.InteropServices.ComVisible(true)] 
    public interface IMyConnect : Extensibility.IDTExtensibility2 
    { 
        string GetMyString(); 

        new void OnConnection( 
            object Application, 
            Extensibility.ext_ConnectMode ConnectMode, 
            object AddInInst, 
            ref System.Array custom); 
        new void OnDisconnection( 
            Extensibility.ext_DisconnectMode RemoveMode, 
            ref System.Array custom); 
        new void OnAddInsUpdate(ref System.Array custom); 
        new void OnStartupComplete(ref System.Array custom); 
        new void OnBeginShutdown(ref System.Array custom); 
    } 

Add the following class underneath the interface in the MyConnect.cs file.

    [System.Runtime.InteropServices.ProgId("ExcelCOMAddin.MyConnect")] 
    [System.Runtime.InteropServices.ComDefaultInterface(typeof(IMyConnect))] 
    [System.Runtime.InteropServices.Guid("DE3911A1-C82F-42F1-BEFB-F3802E1B6D34")] 
    [System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.None)] 
    [System.Runtime.InteropServices.ComVisible(true)] 
    public class MyConnect : IMyConnect 
    { 
        private Microsoft.Office.Interop.Excel.Application _ApplicationObject; 
        private Microsoft.Office.Core.COMAddIn _AddinInstance; 

        public string GetMyString() 
        { 
            return "some text"; 
        } 

        public void OnConnection( 
            object Application, 
            Extensibility.ext_ConnectMode ConnectMode, 
            object AddInInst, 
            ref System.Array custom) 
        { 
            this._ApplicationObject = (Microsoft.Office.Interop.Excel.Application)Application; 

            if (this._AddinInstance == null) 
            { 
                this._AddinInstance = (Microsoft.Office.Core.COMAddIn)AddInInst; 
                this._AddinInstance.Object = this; 
            } 
        } 
        public void OnDisconnection( 
            Extensibility.ext_DisconnectMode RemoveMode, 
            ref System.Array custom) 
        { 
            this._ApplicationObject = null; 
            this._AddinInstance = null; 
        } 
        public void OnAddInsUpdate(ref System.Array custom) 
        { } 
        public void OnStartupComplete(ref System.Array custom) 
        { } 
        public void OnBeginShutdown(ref System.Array custom) 
        { } 
    } 
} 

Make sure the 2 Guid Ids are replaced using (www.guidgen.com).
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 

Registry Keys - COM Add-in

You can view the registry by going to the Windows start menu and typing "regedit" and pressing OK.
When you build the solution only the COM Interop registry entries are added.
If you want the add-in to appear in the COM Add-ins dialog box you need to add the following registry key manually.
There must be a key representing the COM Add-in in the following location:
64 Bit Office - HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\
32 Bit Office - HKEY_CURRENT_USER\Software\Wow6432Node\Microsoft\Office\Excel\Addins\

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\ExcelCOMAddin.MyConnect 
(String)(Default) - (value not set) 
(String) Description - A Long Description 
(String) FriendlyName - ExcelCOMAddin 
(DWord) LoadBehavior - 3 

Description - A brief description of the add-in.
FriendlyName - A user friendly name that is displayed in the COM add-ins dialog box.
Load Behavior - The load behaviour of the add-in.

Load the COM Addin

Open Excel, Create a new blank workbook.
Select (File > Options) and select the Add-ins tab.
Change the Manage drop-down to "COM Add-ins" and press Go.
Find "ExcelCOMAddin" in the list.
Tick this entry and press OK.

Using Early Binding

We can use Early Binding if we want to see what properties and methods are available in the VBA Object Browser.
Open Excel, Create a blank workbook, Add a new Code Module (Module1).
Select (Tools > References) to display a list of all the object libraries that are registered.
Find "ExcelCOMAddin" in the list.
This dialog box displays a list of all the object libraries that are registered on that computer.
Tick this entry and select OK.
Add the following code to Module1.
When you step through the code, you will see "some text" appear in the Immediate window.

Sub Call_EarlyBinding() 
    Dim objAddin As ExcelCOMAddin.MyConnect 
    Set objAddin = New ExcelCOMAddin.MyConnect 
    Debug.Print objAddin.GetMyString() 
End Sub 

Using Late Binding

We can also use Late Binding if we do not want a reference added to the project.
Open Excel, Create a blank workbook, Add a new Code Module.
Add the following code to Module1.
When you step through the code, you will see "some text" appear in the Immediate window.

Sub Call_LateBinding 
   Dim objAddin As ComAddIn 
   Dim objManagedObject As Object 
   Set objAddin = Application.COMAddIns("ExcelCOMAddin.MyConnect") 
   Set objManagedObject = objAddin.Object 
   Debug.Print objManagedObject.GetMyString() 
End Sub 

Registry Keys - Interface GUID

The registry keys that are generated are exactly the same as the COM Interop ones.

Registry Keys - Class GUID

The registry keys that are generated are exactly the same as the COM Interop ones.

Registry Keys - Assembly GUID

The registry keys that are generated are exactly the same as the COM Interop ones.