with VSTO Add-ins

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


Creating

Open Visual Studio 2019 as administrator.
New Project, Expand Office/SharePoint > Office Add-ins, Excel 2013 and 2016 VSTO add-in.
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);
        }
    }
}

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.
Rename the class to "MyClass.cs" and add the following code.

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

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

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.

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

         protected override object RequestComAddInAutomationService()
         {
              if (_exposed == null)
              {
                  _eExposed = 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 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

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.MyConnect")
Set objManagedObject = objAddin.Object
Debug.Print objManagedObject.GetMyString()
End Sub

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