How to call a VSTO Addin Function from VBA
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
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:
Insert Array to Excel Range using VSTO
Create Custom Task Panes for Word using VSTO
Office host applications and their support for add-ins
Hi can I ask? How do you fix a Cancel command button on VBA to not add the selected item into an Excel cell? I have a drop down list and when I click on Cancel, before it unloads/hides, it still inputs the item on the list into the cell. Appreciate it if you could help. Thanks!
ReplyDeleteHi
ReplyDeleteThanks for your article.
I would like to call the VSTO code but at Document level instead of Application level addin. I dont have RequestComAddInAutomationService to override in my Document level project.
Thanks for that, would have been lost without it. It works in XL 2010.
ReplyDeleteAnd an exception thrown in .net produces a (cryptic) error in VBA, not just gobbled like with WPF or Ribbon.
How did you figure out these incantations?
Thanks,
Anthony
Thanks for posting nice info on calling vsto udf from vba macro.
ReplyDeleteI'm able to call for normal thing but my UDF inserts a winform user control into range and it is telling could not insert
object.
Any help is welcome!!