Combining VBA and Document-Level Customizations

Article
02/04/2013

You can use Visual Basic for Applications (VBA) code in a document that is part of a document-level customization for Microsoft Office Word or Microsoft Office Excel. You can call VBA code in the document from the customization assembly, or you can configure your project to enable VBA code in the document to call code in the customization assembly. For more information about the latter task, see Calling Code in Document-Level Customizations from VBA.

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.

Behavior of VBA Code in a Document-Level Customization

When you open your project in Visual Studio, the document is opened in design mode. VBA code does not run when the document is in design mode, so you can work on the document and code without running the VBA code.

When you run the solution, event handlers in both VBA and the customization assembly pick up events that are raised in the document, and both sets of code run. You cannot determine beforehand which code will run before the other; you must determine this through testing in each individual case. You can get unexpected results if the two sets of code are not carefully coordinated and tested.

Calling VBA Code from the Customization Assembly

You can call macros in Word documents, and you can call macros and functions in Excel workbooks. To do this, use one of the following methods:

For Word, call the Run method of the Microsoft.Office.Interop.Word.Application class.
For Excel, call the Run method of the Microsoft.Office.Interop.Excel.Application class.

For each method, the first parameter identifies the name of the macro or function you want to call, and the remaining optional parameters specify the parameters to pass to the macro or function. The first parameter can have different formats for Word and Excel:

For Word, the first parameter is a string that can be any combination of template, module, and macro name. If you specify the document name, your code can only run macros in documents related to the current context — not just any macro in any document.
For Excel, the first parameter can be a string that specifies the macro name, a Range that indicates where the function is, or a register ID for a registered DLL (XLL) function. If you pass a string, the string will be evaluated in the context of the active sheet.

The following code example shows how to call a macro named MyMacro from a document-level project for Excel. This example assumes that MyMacro is defined in Sheet1.

Globals.Sheet1.Application.Run("MyMacro")

Globals.Sheet1.Application.Run("MyMacro", missing, missing, missing,
    missing, missing, missing, missing, missing, missing, missing,
    missing, missing, missing, missing, missing, missing, missing,
    missing, missing, missing, missing, missing, missing, missing, 
    missing, missing, missing, missing, missing, missing);

Note

For information about using the global missing variable in place of optional parameters in Visual C#, see Programming with Visual Basic vs. Visual C# in Office Solutions.

Combining VBA and Document-Level Customizations

Behavior of VBA Code in a Document-Level Customization

Calling VBA Code from the Customization Assembly

See Also

Tasks

Concepts

Other Resources

Additional resources