Walkthrough: Calling Code in an Application-Level Add-in from VBA

Walkthrough: Calling Code in an App...

Microsoft Visual Studio Tools for the Microsoft Office system (version 3.0)

Walkthrough: Calling Code in an Application-Level Add-in from VBA

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Application-level projects Microsoft Office version 2007 Microsoft Office system Microsoft Office 2003 For more information, see Features Available by Application and Project Type.

Applies to

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Project type

Application-level projects

Microsoft Office version

2007 Microsoft Office system
Microsoft Office 2003

For more information, see Features Available by Application and Project Type.

This walkthrough demonstrates how to expose an object in an application-level add-in to other Microsoft Office solutions, including Visual Basic for Applications (VBA) and COM add-ins.

Although this walkthrough uses Excel specifically, the concepts demonstrated by the walkthrough are applicable to any add-in project provided by Visual Studio Tools for Office.

This walkthrough illustrates the following tasks:

Defining a class that can be exposed to other Office solutions.
Exposing the class to other Office solutions.
Calling a method of the class from VBA code.

Note:
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Prerequisites

You need the following components to complete this walkthrough:

Visual Studio Tools for Office (an optional component of Visual Studio 2008 Professional and Visual Studio Team System).
Microsoft Office Excel 2007.

Note:

You can also perform this walkthrough using Microsoft Office Excel 2003. However, some of the instructions assume you are using the Ribbon in Excel 2007.

Note:
You can also perform this walkthrough using Microsoft Office Excel 2003. However, some of the instructions assume you are using the Ribbon in Excel 2007.

Visual Studio Tools for Office is installed by default with the listed versions of Visual Studio. To check whether it is installed, see Installing Visual Studio Tools for Office.

link to video For a video demonstration, see Video How to: Calling Code in an Application-Level Add-in from VBA.

Creating the Add-in Project

The first step is to create an add-in project for Excel.

To create a new project

Create an Excel Add-in project with the name ExcelImportData, using the Excel Add-in project template for the 2007 Microsoft Office system. For more information, see How to: Create Visual Studio Tools for Office Projects.

Visual Studio opens the ThisAddIn.cs or ThisAddIn.vb code file and adds the ExcelImportData project to Solution Explorer.

Defining a Class That You Can Expose to Other Office Solutions

The purpose of this walkthrough is to call into the ImportData method of a class named AddInUtilities in your add-in from VBA code. This method imports data into a new worksheet in the active workbook, and creates a simple DataSet to contain the data.

To expose the AddInUtilities class to other Office solutions, you must make the class public and visible to COM. You must also provide a way to expose the IDispatch interface in the class. The code in the following procedure demonstrates one way to meet these requirements. For more information, see Calling Code in Application-Level Add-ins from Other Office Solutions.

To define a class that you can expose to other Office solutions

On the Project menu, click Add Class.
In the Add New Item dialog box, change the name of the new class to AddInUtilities, and click Add.

The AddInUtilities.cs or AddInUtilities.vb file opens in the Code Editor.

Add the following statements to the top of the file.

Visual Basic

Imports System.Data
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

using System.Data;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

Replace the empty AddInUtilities class declaration with the following code.

This code makes the AddInUtilities class visible to COM, and it adds the ImportData method to the class. To expose the IDispatch interface, the AddInUtilities class also implements an interface that has the InterfaceIsIDispatch attribute.

Visual Basic

<System.Runtime.InteropServices.ComVisibleAttribute(True)> _
<System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsIDispatch)> _
Public Interface IAddInUtilities
    Sub ImportData()
End Interface

<System.Runtime.InteropServices.ComVisibleAttribute(True)> _
<System.Runtime.InteropServices.ClassInterface(System.Runtime.InteropServices.ClassInterfaceType.None)> _
Public Class AddInUtilities
    Implements IAddInUtilities

    Public Sub ImportData() Implements IAddInUtilities.ImportData

        ' Create a new DataTable.
        Dim ds As New DataSet()
        Dim dt As DataTable = ds.Tables.Add("Customers")
        dt.Columns.Add(New DataColumn("LastName"))
        dt.Columns.Add(New DataColumn("FirstName"))

        ' Add a new row to the DataTable.
        Dim dr As DataRow = dt.NewRow()
        dr("LastName") = "Chan"
        dr("FirstName") = "Gareth"
        dt.Rows.Add(dr)

        ' Add a new XML map to the collection.
        Dim activeWorkbook As Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook
        Dim xmlMap1 As Excel.XmlMap = activeWorkbook.XmlMaps.Add(ds.GetXmlSchema(), _
            "NewDataSet")

        ' Import the data.
        If Not (xmlMap1 Is Nothing) Then
            Dim lastSheet As Object = activeWorkbook.Sheets(activeWorkbook.Sheets.Count)
            Dim newSheet As Excel.Worksheet = CType(activeWorkbook.Sheets.Add( _
                After:=lastSheet), Excel.Worksheet)
            newSheet.Name = "Imported Data"
            activeWorkbook.XmlImportXml(ds.GetXml(), xmlMap1, True, _
                newSheet.Range("A1"))
        End If
    End Sub
End Class

[System.Runtime.InteropServices.ComVisibleAttribute(true)]
[System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface IAddInUtilities
{
    void ImportData();
}

[System.Runtime.InteropServices.ComVisibleAttribute(true)]
[System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.None)]
public class AddInUtilities : IAddInUtilities
{
    public void ImportData()
    {
        // Create a new DataTable.
        DataSet ds = new DataSet();
        DataTable dt = ds.Tables.Add("Customers");
        dt.Columns.Add(new DataColumn("LastName"));
        dt.Columns.Add(new DataColumn("FirstName"));

        // Add a new row to the DataTable.
        DataRow dr = dt.NewRow();
        dr["LastName"] = "Chan";
        dr["FirstName"] = "Gareth";
        dt.Rows.Add(dr);

        // Add a new XML map to the collection.
        Excel.Workbook activeWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
        Excel.XmlMap xmlMap1 = activeWorkbook.XmlMaps.Add(ds.GetXmlSchema(), 
            "NewDataSet");

        // Import the data.
        if (xmlMap1 != null)
        {
            object lastSheet = activeWorkbook.Sheets[activeWorkbook.Sheets.Count];
            Excel.Worksheet newSheet = (Excel.Worksheet)activeWorkbook.Sheets.Add(
                System.Type.Missing, lastSheet, 1, System.Type.Missing);
            newSheet.Name = "Imported Data";

            activeWorkbook.XmlImportXml(ds.GetXml(), out xmlMap1, true,
                newSheet.get_Range("A1", System.Type.Missing));
        }
    }
}

Exposing the Class to Other Office Solutions

To expose the AddInUtilities class to other Office solutions, override the RequestComAddInAutomationService method in the ThisAddIn class. In your override, return an instance of the AddInUtilities class.

To expose the AddInUtilities class to other Office Solutions

In Solution Explorer, expand Excel.
Right-click ThisAddIn.cs or ThisAddIn.vb, and then click View Code.

Add the following code to the ThisAddIn class.

Visual Basic

Private utilities As AddInUtilities

Protected Overrides Function RequestComAddInAutomationService() As Object
    If utilities Is Nothing Then
        utilities = New AddInUtilities()
    End If
    Return utilities
End Function

private AddInUtilities utilities;

protected override object RequestComAddInAutomationService()
{
    if (utilities == null)
        utilities = new AddInUtilities();

    return utilities;
}

On the Build menu, click Build Solution.

Verify that the solution builds without errors.

Testing the Add-In

You can call into the AddInUtilities class from several different types of Office solutions. In this walkthrough, you will use VBA code in an Excel workbook. For more information about the other types of Office solutions you can also use, see Calling Code in Application-Level Add-ins from Other Office Solutions.

To test your add-in

Press F5 to run your project.
In Excel, save the active workbook as an Excel Macro-Enabled Workbook (*.xlsm). Save it in a convenient location, such as the desktop.
On the Ribbon, click the Developer tab.

Note:

If the Developer tab is not visible, you must first show it. For more information, see How to: Show the Developer Tab on the Ribbon.
In the Code group, click Visual Basic.

The Visual Basic Editor opens.
In the Project window, double-click ThisWorkbook.

The code file for the ThisWorkbook object opens.

Note:
If the Developer tab is not visible, you must first show it. For more information, see How to: Show the Developer Tab on the Ribbon.

Add the following VBA code to the code file. This code first gets a COMAddIn object that represents the ExcelImportData add-in. Then, the code uses the Object property of the COMAddIn object to call the ImportData method.

Sub CallVSTOMethod()
    Dim addIn As COMAddIn
    Dim automationbject As Object
    Set addIn = Application.COMAddIns("ExcelImportData")
    Set automationObject = addIn.Object
    automationObject.ImportData
End Sub

Press F5.
Verify that a new Imported Data sheet has been added to the workbook. Also verify that cell A1 contains the string Chan, and cell B1 contains the string Gareth.
Exit Excel.

Next Steps

You can learn more about programming add-ins from these topics:

Use the ThisAddIn class to automate the host application and perform other tasks in add-in projects. For more information, see Programming Application-Level Add-Ins and AddIn Host Item.
Create a custom task pane in an add-in for the 2007 Microsoft Office system. For more information, see Custom Task Panes Overview and How to: Add a Custom Task Pane to an Application.
Customize the Ribbon in an add-in for the 2007 Microsoft Office system. For more information, see Ribbon Overview and How to: Get Started Customizing the Ribbon.