Tuesday, November 25, 2008

Call a Method in VSTO Addin from Visual Basic Applications

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


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:

Insert Array to Excel Range using VSTO

Create Custom Task Panes for Word using VSTO

Office host applications and their support for add-ins

4 comments:

  1. 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!

    ReplyDelete
  2. Hi
    Thanks 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.

    ReplyDelete
  3. Thanks for that, would have been lost without it. It works in XL 2010.

    And 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

    ReplyDelete
  4. Anonymous3:13 PM

    Thanks for posting nice info on calling vsto udf from vba macro.
    I'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!!

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.