Run Visual Basic Macros in Word and Excel

You can use the Word Run Macro VI and the Excel Run Macro VI to run Visual Basic for Applications (VBA) macros in Microsoft Word and Microsoft Excel. These VIs allow you to can run existing VBA macros and macros that you program to implement functionality that the LabVIEW Report Generation Toolkit for Microsoft Office does not include.

Before running a macro, make sure you include the module that defines the macro in the VBA project associated with the report or in the template on which you based the report. The Word Import Module VI and the Excel Import Module VI can import modules dynamically from a VBA function file (.bas) or from a LabVIEW string that contains the module source code.

Macros with Input Arguments

If the macro you want to run requires input arguments, enter them into the parameters input of the VI. The data type for this input is an array of variants. You must convert all the input parameters sent to the macro to variants and bundle the parameters into an array. Use the To Variant function to convert the parameters to variants. Use the Build Array function to build the parameters into an array.

Retrieve an Output Value

If you want to retrieve an output value from a macro that LabVIEW runs, you can find this value in the return value parameter of the Word Run Macro VI or the Excel Run Macro VI. These VIs return the output value as a variant. Use the Variant To Data function to convert the value to LabVIEW-compatible data.

Manipulate an Object from Another Application

To run a macro that manipulates objects from another application, set a reference to the application type library. Creating a reference allows easy access to objects that use either the Word Add Reference to VBproj VI or the Excel Add Reference to VBproj VI. Refer to the Microsoft Visual Basic Help for more information about type libraries.

Example

The following example uses Report Generation VIs to run a Word macro. The Create Report VI creates a new Word document. The Append Table to Report VI inserts a table into the report. The Word Import Module VI adds a VBA module to the report. The Word Run Macro VI runs the ProcessTable macro that the module defines. The Word Remove Module VI deletes the module from the report. The Save Report to File VI saves the report as a Word document. The Dispose Report VI closes the Word document.
Figure 1. Block Diagram Using Report Generation VIs to Run a Word Macro


The VI in the previous figure is similar to the Run Macro on Word Table example VI, located in the labview\examples\office\Word Reports.llb.