Udostępnij za pośrednictwem

How to: Expose Code to VBA in a Visual Basic Project

  • Artykuł

You can expose code in a Visual Basic project to Visual Basic for Applications (VBA) code if you want the two types of code to interact with each other.

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.

The Visual Basic process is different from the Visual C# process. For more information, see Calling Code in Document-Level Customizations from VBA and How to: Expose Code to VBA in a Visual C# Project.

The process is different for code in a host item class than it is for code in other classes:

  • Exposing code in a host item class

  • Exposing code that is not in a host item class

link to video For a related video demonstration, see How Do I: Call VSTO Code from VBA?.

Exposing Code in a Host Item Class

To enable VBA code to call Visual Basic code in a host item class, set the EnableVbaCallers property of the host item to True.

For a walkthrough that demonstrates how to expose a method of a host item class and then call it from VBA, see Walkthrough: Calling Code from VBA in a Visual Basic Project. For more information about host items, see Host Items and Host Controls Overview.

To expose code in a host item to VBA

  1. Open or create a document-level Visual Basic project that is based on a Word document, Excel workbook, or Excel template that supports macros, and that already contains VBA code. 

    For more information about the document file formats that support macros, see Calling Code in Document-Level Customizations from VBA.

    Note

    This feature cannot be used in Word template projects.

  2. Ensure that VBA code in the document is allowed to run without prompting the user to enable macros. You can trust VBA code to run by adding the location of the Office project to the list of trusted locations in the Trust Center settings for Word or Excel.

  3. Add the property, method, or event that you want to expose to VBA to one of the host item classes in your project, and declare the new member as Public. The name of the class depends on the application:

    • In a Word project, the host item class is named ThisDocument by default.

    • In an Excel project, the host item classes are named ThisWorkbook, Sheet1, Sheet2, and Sheet3 by default.

  4. Set the EnableVbaCallers property for the host item to True. This property is available in the Properties window when the host item is open in the designer.

    After you set this property, Visual Studio automatically sets the ReferenceAssemblyFromVbaProject property to True. For more information about this property, see Calling Code in Document-Level Customizations from VBA.

    Note

    If the workbook or document does not already contain VBA code, or if VBA code in the document is not trusted to run, you will receive an error message when you set the EnableVbaCallers property to True. This is because Visual Studio cannot modify the VBA project in the document in this situation.

  5. Click OK in the message that is displayed. This message reminds you that if you add VBA code to the workbook or document while you are running the project from Visual Studio, the VBA code will be lost the next time you build the project. This is because the document in the build output folder is overwritten every time you build the project.

    At this point, Visual Studio configures the project so that the VBA project can call into the assembly. Visual Studio also adds a property named CallVSTOAssembly to the ThisDocument, ThisWorkbook, Sheet1, Sheet2, or Sheet3 module in the VBA project. You can use this property to access public members of the class that you exposed to VBA. For more information, see Calling Code in Document-Level Customizations from VBA.

  6. Build the project.

Exposing Code That Is Not in a Host Item Class

To enable VBA code to call Visual Basic code that is not in a host item class, modify the code so it is visible to VBA.

To expose code that is not in a host item class to VBA

  1. Open or create a document-level Visual Basic project that is based on a Word document, Excel workbook, or Excel template that supports macros, and that already contains VBA code.

    For more information about the document file formats that support macros, see Calling Code in Document-Level Customizations from VBA.

    Note

    This feature cannot be used in Word template projects.

  2. Ensure that VBA code in the document is allowed to run without prompting the user to enable macros. You can trust VBA code to run by adding the location of the Office project to the list of trusted locations in the Trust Center settings for Word or Excel.

  3. Add the member that you want to expose to VBA to a public class in your project, and declare the new member as public.

  4. Apply the following ComVisibleAttribute and ComClassAttribute attributes to the class that you are exposing to VBA. These attributes make the class visible to VBA.

    <Microsoft.VisualBasic.ComClass()> _
<System.Runtime.InteropServices.ComVisibleAttribute(True)> _

  5. Override the GetAutomationObject method of a host item class in your project to return an instance of the class that you are exposing to VBA. The following code example assumes that you are exposing a class named DocumentUtilities to VBA.

    Protected Overrides Function GetAutomationObject() As Object
    Return New DocumentUtilities()
End Function

  6. Open the document (for Word) or worksheet (for Excel) designer in Visual Studio.

  7. In the Properties window, select the ReferenceAssemblyFromVbaProject property, and change the value to True.

    Note

    If the workbook or document does not already contain VBA code, or if VBA code in the document is not trusted to run, you will receive an error message when you set the ReferenceAssemblyFromVbaProject property to True. This is because Visual Studio cannot modify the VBA project in the document in this situation.

  8. Click OK in the message that is displayed. This message reminds you that if you add VBA code to the workbook or document while you are running the project from Visual Studio, the VBA code will be lost the next time you build the project. This is because the document in the build output folder is overwritten every time you build the project.

    At this point, Visual Studio configures the project so that the VBA project can call into the assembly. Visual Studio also adds a method named GetManagedClass to the VBA project. You can call this method from anywhere in the VBA project to access the class that you exposed to VBA. For more information, see Calling Code in Document-Level Customizations from VBA.

  9. Build the project.

See Also

Tasks

How to: Create Office Projects in Visual Studio

Walkthrough: Calling Code from VBA in a Visual Basic Project

How to: Expose Code to VBA in a Visual C# Project

Concepts

Calling Code in Document-Level Customizations from VBA

Other Resources

Designing and Creating Office Solutions

Combining VBA and Document-Level Customizations