Walkthrough: Calling Code from VBA in a Visual C# Project

Artykuł
02/04/2013

This walkthrough demonstrates how to call a method in a document-level customization for Microsoft Office Excel from Visual Basic for Applications (VBA) code in the workbook. The procedure involves three basic steps: add a method to the Sheet1 host item class, expose the method to VBA code in the workbook, and then call the method from VBA code in the workbook.

Applies to: The information in this topic applies to document-level projects for the following applications: Excel 2007 and Excel 2010; Word 2007 and Word 2010. 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 also applicable to document-level projects for Word.

This walkthrough illustrates the following tasks:

Creating a workbook that contains VBA code.
Trusting the location of the workbook by using the Trust Center in Excel.
Adding a method to the Sheet1 host item class.
Extracting an interface for the Sheet1 host item class.
Exposing the method to VBA code.
Calling the method 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 Working with Settings.

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.

Creating a Workbook That Contains VBA Code

The first step is to create a macro-enabled workbook that contains a simple VBA macro. Before you can expose code in a customization to VBA, the workbook must already contain VBA code. Otherwise, Visual Studio cannot modify the VBA project to enable VBA code to call into the customization assembly.

If you already have a workbook that contains VBA code that you want to use, you can skip this step.

To create a workbook that contains VBA code

Start Excel.
Save the active document as an Excel Macro-Enabled Workbook (*.xlsm) with the name WorkbookWithVBA. Save it to 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.
Add the following VBA code to the code file. This code defines a simple function that does nothing. The only purpose of this function is to ensure that a VBA project exists in the workbook. This is required for later steps in this walkthrough.
```
Sub EmptySub()
End Sub
```
Save the document and exit Excel.

Creating the Project

Now you can create a document-level project for Excel that uses the macro-enabled workbook you created earlier.

To create a new project

Start Visual Studio.
On the File menu, point to New, and then click Project.
In the templates pane, expand Visual C#, and then expand Office.
Select the 2010 or 2007 node.
In the list of project templates, select the Excel Workbook project.
In the Name box, type CallingCodeFromVBA.
Click OK.

The Visual Studio Tools for Office Project Wizard opens.
Select Copy an existing document, and, in the Full path of the existing document box, specify the location of the WorkbookWithVBA workbook that you created earlier. If you are using your own macro-enabled workbook, specify the location of that workbook instead.
Click Finish.

Visual Studio opens the WorkbookWithVBA workbook in the designer and adds the CallingCodeFromVBA project to Solution Explorer.

Trusting the Location of the Workbook

Before you can expose code in your solution to VBA code in the workbook, you must trust VBA in the workbook to run. There are several ways to do this. In this walkthrough, you will accomplish this task by trusting the location of the workbook in the Trust Center in Excel.

To trust the location of the workbook

Start Excel.
Click the File tab (for Excel 2010) or the Microsoft Office Button (for Excel 2007).
Click the Excel Options button.
In the categories pane, click Trust Center.
In the details pane, click Trust Center Settings.
In the categories pane, click Trusted Locations.
In the details pane, click Add new location.
In the Microsoft Office Trusted Location dialog box, browse to the folder that contains the CallingCodeFromVBA project.
Select Subfolders of this location are also trusted.
In the Microsoft Office Trusted Location dialog box, click OK.
In the Trust Center dialog box, click OK.
In the Excel Options dialog box, click OK.
Exit Excel.

Adding a Method to the Sheet1 Class

Now that the VBA project is set up, add a public method to the Sheet1 host item class that you can call from VBA code.

To add a method to the Sheet1 class

In Solution Explorer, right-click Sheet1.cs, and then click View Code.

The Sheet1.cs file opens in the Code Editor.

Add the following code to the Sheet1 class. The CreateVstoNamedRange method creates a new NamedRange object at the specified range. This method also creates an event handler for the Selected event of the NamedRange. Later in this walkthrough, you will call the CreateVstoNamedRange method from VBA code in the document.

private Microsoft.Office.Tools.Excel.NamedRange namedRange1;

public void CreateVstoNamedRange(Excel.Range range, string name)
{
    if (!this.Controls.Contains(name))
    {
        namedRange1 = this.Controls.AddNamedRange(range, name);
        namedRange1.Selected += new Excel.DocEvents_SelectionChangeEventHandler(
                namedRange1_Selected);
    }
    else
    {
        MessageBox.Show("A named range with this specific name " +
            "already exists on the worksheet.");
    }
}

private void namedRange1_Selected(Microsoft.Office.Interop.Excel.Range Target)
{
    MessageBox.Show("This named range was created by Visual Studio " +
        "Tools for Office.");
}

Add the following method to the Sheet1 class. This method overrides the GetAutomationObject() method to return the current instance of the Sheet1 class.
```
protected override object GetAutomationObject()
{
    return this;
}
```
Apply the following attributes before the first line of the Sheet1 class declaration. These attributes make the class visible to COM, but without generating a class interface.
```
[System.Runtime.InteropServices.ComVisible(true)]
[System.Runtime.InteropServices.ClassInterface(
    System.Runtime.InteropServices.ClassInterfaceType.None)]
```

Extracting an Interface for the Sheet1 Class

Before you can expose the CreateVstoNamedRange method to VBA code, you must create a public interface that defines this method, and you must expose this interface to COM.

To extract an interface for the Sheet1 class

In the Sheet1.cs code file, click anywhere in the Sheet1 class.
On the Refactor menu, click Extract Interface.
In the Extract Interface dialog box, in the Select public members to form interface box, click the entry for the CreateVstoNamedRange method.
Click OK.

Visual Studio generates a new interface named ISheet1, and it modifies the definition of the Sheet1 class so that it implements the ISheet1 interface. Visual Studio also opens the ISheet1.cs file in the Code Editor.
In the ISheet1.cs file, replace the ISheet1 interface declaration with the following code. This code makes the ISheet1 interface public, and it applies the ComVisibleAttribute attribute to make the interface visible to COM.
```
[System.Runtime.InteropServices.ComVisible(true)]
public interface ISheet1
{
    void CreateVstoNamedRange(Microsoft.Office.Interop.Excel.Range range, string name);
}
```
Build the project.

Exposing the Method to VBA Code

To expose the CreateVstoNamedRange method to VBA code in the workbook, set the ReferenceAssemblyFromVbaProject property for the Sheet1 host item to True.

To expose the method to VBA code

In Solution Explorer, double-click Sheet1.cs.

The WorkbookWithVBA file opens in the designer, with Sheet1 visible.
In the Properties window, select the ReferenceAssemblyFromVbaProject property, and change the value to True.
Click OK in the message that is displayed.
Build the project.

Calling the Method from VBA Code

You can now call the CreateVstoNamedRange method from VBA code in the workbook.

Note

In this walkthrough, you will add VBA code to the workbook while debugging the project. The VBA code you add to this document will be overwritten the next time that you build the project, because Visual Studio replaces the document in the build output folder with a copy of the document from the main project folder. If you want to save the VBA code, you can copy it into the document in the project folder. For more information, see Calling Code in Document-Level Customizations from VBA.

To call the method from VBA code

Press F5 to run your project.
On the Developer tab, in the Code group, click Visual Basic.

The Visual Basic Editor opens.
On the Insert menu, click Module.
Add the following code to the new module.

This code calls the CreateTable method in the customization assembly. The macro accesses this method by using the global GetManagedClass method to access the Sheet1 host item class that you exposed to VBA code. The GetManagedClass method was automatically generated when you set the ReferenceAssemblyFromVbaProject property earlier in this walkthrough.
```
Sub CallVSTOMethod()
    Dim VSTOSheet1 As CallingCodeFromVBA.Sheet1
    Set VSTOSheet1 = GetManagedClass(Sheet1)
    Call VSTOSheet1.CreateVstoNamedRange(Sheet1.Range("A1"), "VstoNamedRange")
End Sub
```
Press F5.
In the open workbook, click cell A1 on Sheet1. Verify that the message box appears.
Exit Excel without saving your changes.

Next Steps

You can learn more about calling code in Office solutions from VBA in these topics:

Call code in a host item in a Visual Basic customization from VBA. This process is different from the Visual C# process. For more information, see Walkthrough: Calling Code from VBA in a Visual Basic Project.
Call code in an application-level add-in from VBA. For more information, see Walkthrough: Calling Code in an Application-Level Add-in from VBA.

Udostępnij za pośrednictwem