How to call a VSTO Addin Function from VBA

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