with VSTO Add-ins

Introduced in Excel 2007.
VSTO Add-ins must implement the IStartup interface.
More information can be found here VSTO Add-ins > Application Level > VSTO Add-in.
link - learn.microsoft.com/en-us/visualstudio/vsto/walkthrough-calling-code-in-a-vsto-add-in-from-vba?view=vs-2019

Creating

Open Visual Studio 2022 as administrator.
Search for "Excel VSTO Add-in" and press Next.
Change the Name to "ExcelVSTOAddin".
Change the Location to somewhere on your C drive.
Check the .NET Framework version is correct and press Create.
All the necessary References should be added automatically.
Add the following code to the ThisAddIn.cs file.

namespace ExcelVSTOAddin 
{ 
    public partial class ThisAddIn 
    { 
        private void ThisAddIn_Startup(object sender, System.EventArgs e) 
        { 
            System.Windows.Forms.MessageBox.Show("Startup"); 
        } 

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e) 
        { 
           System.Windows.Forms.MessageBox.Show("Shutdown"); 
        } 

        private void InternalStartup() 
        { 
            this.Startup += new System.EventHandler(ThisAddIn_Startup); 
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); 
        } 
    } 
} 

Build Solution

Select (Build > Build Solution).
At this point the necessary registry entries are automatically added to the registry.
Select (Debug > Start Debugging).
Excel will open and you will see the "Startup" message box appear.
If you display the COM Add-Ins dialog box you will see an entry for "ExcelVSTOAddin".
Close Excel and you will see the "Shutdown" message box appear.
If you select (Build > Clean Solution) then the registry entries will be automatically removed from the registry.

MyClass.cs

Add a new item to the project (Project > Add New Item).
Choose Class and rename the file to "MyClass.cs". Press Add.
Add the following code.

namespace ExcelVSTOAddin 
{ 
    [System.Runtime.InteropServices.ComVisible(true)] 
    public interface IMyClass 
    { 
        string GetMyString(); 
    } 

    [System.Runtime.InteropServices.ClassInterface(System.Runtime.InteropServices.ClassInterfaceType.None)] 
    [System.Runtime.InteropServices.ComVisible(true)] 
    public class MyClass : IMyClass 
    { 
        public string GetMyString() 
        { 
             return "some text"; 
        } 
    } 
} 

RequestComAddinAutomationService

To expose this class to VBA you need to override the RequestComAddInAutomationService method in the ThisAddIn class.
Overwrite the RequestComAddinAutomationService method to return an instance of this class.
Add a private field called "_exposed".
Add a method called "RequestComAddinAutomationService".

namespace ExcelVSTOAddin 
{ 
    public partial class ThisAddIn 
    { 
         private MyClass _exposed; 

         protected override object RequestComAddInAutomationService() 
         { 
              if (_exposed == null) 
              { 
                  _exposed = new MyClass(); 
              } 
              return _exposed; 
        } 
    } 
} 

This method returns an object from your add-in that can be accessed from VBA.
The COMAddin interface defines a small number of methods/properties such as ProgId and a Connect State.
It also defines an Object property.
By default this value is null but an add-in can expose an object which can be used by external callers (ie VBA).
Select (Build > Build Solution).
When the solution is built the RegAsm tool runs automatically to add the COM Interop keys.
When the solution is built the registry keys for adding the add-in to the COM Add-ins dialog box are also added automatically.
When the solution is cleaned the registry keys are automatically removed.

Registry Keys - VSTO Add-in

You can view the registry by going to the Windows start menu, choosing Run, typing regedit and pressing OK.
When you build the solution all the registry entries are added automatically.
For more information refer to the VSTO Add-ins > Application Level > Registry Entries

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\Excel\ 
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\Excel\ExcelVSTOAddin 
(String)(Default) - (value not set) 
(String) Description - ExcelVSTOAddin 
(String) FriendlyName - ExcelVSTOAddin 
(DWord) LoadBehavior - 3 
(String) Manifest - file:///C:/Personal/ExcelVSTOAddIn/ExcelVSTOAddin/bin/ 
                                             Debug/ExcelVSTOAddin.vsto|vstolocal 

Using Early Binding

We cannot use early binding to access the code because we cannot add a Reference to the project.

Using Late Binding

We can use Late Binding to access the VSTO Add-in.
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("ExcelVSTOAddin") 
   Set objManagedObject = objAddin.Object 
   Debug.Print objManagedObject.GetMyString() 
End Sub