Call Method in Addin from Visual Basic Applications
VSTO Addins we write might contain good number of reusable components. The following article shows how to use them from external applications. This consists of the following steps:
1. Creating a VSTO Addin with a public method
2. Expose the VSTO Method for external use
3. Consume the VSTO Method from VBA
Creation of VSTO Excel Addin
Create a new project from Visual Studio - - > Office Projects - - > Excel 2007 Addin and call the project CallVSTOExample
This will automatically create necessary references and add required directives
VSTO Addin
using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
Create a new C# Class module and add the following code to it
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace CallVSTOExample
{
[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface IAddinVSTO
{
void SayHello();
}
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class VSTOExample : IAddinVSTO
{
#region IAddinVSTO Members
public void SayHello()
{
Excel.Worksheet wks = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
Excel.Range myRange = wks.get_Range("A1", System.Type.Missing);
myRange.Value2 = "Hello";
}
#endregion
}
}
Here we have created one interface and have also created a class that implements the interface. The class has a simple method SayHello, which inserts “Hello” in cell A1 of active worksheet
The class must be public and should expose the IDispatch interface for it to be visible to COM
InterfaceIsIDispatch indicates an interface is exposed to COM as a dispinterface, which enables late binding only.
Exposing the method for external use
Add the following code to ThisAddin class
private VSTOExample utilities;
protected override object RequestComAddInAutomationService()
{
if (utilities == null)
utilities = new VSTOExample();
return utilities;
}
RequestComAddInAutomationService method returns an object in your add-in that can be used by other Microsoft Office solutions.
Override this method to expose an object in your add-in to other Microsoft Office solutions. This includes other add-ins and document-level customizations running in the same application process, VBA code, and external automation code.
The object that you return must be public, it must be visible to COM, and it must expose the IDispatch interface. If the object you return does not meet these requirements, the Visual Studio Tools for Office runtime will throw an InvalidCastException after it calls your implementation.
Compile the code and open the application (Excel) with the addin installed.
Excel VBA Code to call VSTO Method
The VSTOAddin that was created will be loaded as a COMAddin in Excel. The following code will retrieve the addin information
Sub Execute_VSTO_Addin_Macro()
Dim oAddin As COMAddIn
Dim oCOMFuncs As Object
Set oAddin = Application.COMAddIns("CallVSTOExample")
Set oCOMFuncs = oAddin.Object
oCOMFuncs.SayHello
End Sub
The Object property returns the object represented by the specified COMAddIn object. The VSTO function SayHello is called using the COMAddin’s object as shown above
See Also
How to use .Net Array.Sort Function in VBA
output parameter in .NET
VSTO Addin
Posts
