with VSTO Add-ins

Introduced in Excel 2007.
VSTO Add-ins must implement the IStartup interface.


Creating

Open Visual Studio 2017.
New Project, Expand Office/SharePoint > Office Add-ins, Excel 2013 and 2016 VSTO add-in.
Change the Name to "ExcelVSTOAddin".
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);
        }
    }
}

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.
Add a new item to the project (Project > New Item).
Choose Class and press Add.
Add the following code to the Class1.cs file.

class Class 1 
{
    [System.Runtime.InteropServices.GUID()]
    [System.Runtime.InteropServices.InterfaceType(
          COMInterfaceType.InterfaceIsDual)]
    [System.Runtime.InteropServices.ComVisible(true)]
    public interface IMyClass
    {
          string GetMyString();
     }
    [System.Runtime.InteropServices.GUID()]
    [System.Runtime.InteropServices.ClassInterface(
          ClassInterfaceType.None)]
    [System.Runtime.InteropServices.ComVisible(true)]
    public class MyClass : IMyClass
    {
        public string GetMyString()
        {
             return "some text";
        }
    }
}

Overwrite the RequestComAddinAutomationService method to return an instance of this class
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)

namespace ExcelVSTOAddIn 
{
    public partial class ThisAddIn
    {
         private AddInExposed myExposed;

         protected override object RequestComAddInAutomationService()
         {
              if (myExposed == null)
              {
                  myExposed = new AddInExposed();
              }
              return myExposed;
        }
    }
}

Registry Keys - VSTO Add-in

For more information refer to the [Office Developer Tools > 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




Using Late Binding




Registry Entries - Interface GUID



Registry Entries - Class GUID



Registry Entries - Assembly GUID



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