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

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.

Applies to: The information in this topic applies to application-level projects for Microsoft Office 2010 and the 2007 Microsoft Office system. For more information, see Features Available by Office Application and Project Type.

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

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.

Prerequisites

You need the following components to complete this walkthrough:

-

An edition of Visual Studio 2010 that includes the Microsoft Office developer tools. For more information, see [Configuring a Computer to Develop Office Solutions](bb398242\(v=vs.100\).md).

• Excel 2007 or Excel 2010.

For a video version of this topic, 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 more information, see How to: Create Office Projects in Visual Studio.

  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 writes a string into cell A1 of the active worksheet.

To expose the AddInUtilities class to other Office solutions, you must make the class public and visible to COM. You must also 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

1. On the Project menu, click Add Class.

2. 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.

3. Add the following statements to the top of the file.

   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;
   

4. Replace the AddInUtilities class with the following code.

   <ComVisible(True)> _
   Public Interface IAddInUtilities
       Sub ImportData()
   End Interface
   
   <ComVisible(True)> _
   <ClassInterface(ClassInterfaceType.None)> _
   Public Class AddInUtilities
       Implements IAddInUtilities
   
       ' This method tries to write a string to cell A1 in the active worksheet.
       Public Sub ImportData() Implements IAddInUtilities.ImportData
   
           Dim activeWorksheet As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet
   
           If activeWorksheet IsNot Nothing Then
               Dim range1 As Excel.Range = activeWorksheet.Range("A1")
               range1.Value2 = "This is my data"
           End If
       End Sub
   End Class
   

   [ComVisible(true)]
   public interface IAddInUtilities
   {
       void ImportData();
   }
   
   [ComVisible(true)]
   [ClassInterface(ClassInterfaceType.None)]
   public class AddInUtilities : IAddInUtilities
   {
       // This method tries to write a string to cell A1 in the active worksheet.
       public void ImportData()
       {
           Excel.Worksheet activeWorksheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
   
           if (activeWorksheet != null)
           {
               Excel.Range range1 = activeWorksheet.get_Range("A1", System.Type.Missing);
               range1.Value2 = "This is my data";
           }
       }
   }
   

   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 has the ClassInterfaceAttribute attribute, and it implements an interface that is visible to COM.

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

1. In Solution Explorer, expand Excel.

2. Right-click ThisAddIn.cs or ThisAddIn.vb, and then click View Code.

3. Add the following code to the ThisAddIn class.

   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;
   }
   

4. 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

1. Press F5 to run your project.

2. In Excel, save the active workbook as an Excel Macro-Enabled Workbook (*.xlsm). Save it in a convenient location, such as the desktop.

3. 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.

4. In the Code group, click Visual Basic.

   The Visual Basic Editor opens.

5. In the Project window, double-click ThisWorkbook.

   The code file for the ThisWorkbook object opens.

6. 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 automationObject As Object
       Set addIn = Application.COMAddIns("ExcelImportData")
       Set automationObject = addIn.Object
       automationObject.ImportData
   End Sub
   

7. Press F5.

8. Verify that a new Imported Data sheet has been added to the workbook. Also verify that cell A1 contains the string This is my data.

9. 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.

• Create a custom task pane in an add-in. 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 more information, see Ribbon Overview and How to: Get Started Customizing the Ribbon.

See Also

Tasks

How to: Create Office Projects in Visual Studio

Concepts

Calling Code in Application-Level Add-ins from Other Office Solutions

Customizing UI Features By Using Extensibility Interfaces

Architecture of Application-Level Add-Ins

Other Resources

Programming Application-Level Add-Ins

Developing Office Solutions