Executing a VBA Macro from C#

The following example demonstrates how to run a user-defined VBA (Visual Basic for Applications) macro (in the RC-WinTrans 9 VBA environment) from a COM automation client developed in C# (.NET).

The C# client has an edit field where a message text can be entered. The C# application calls (on command) a VBA macro passing the message text as a parameter. The VBA macro writes the message text to RC-WinTrans' Output window.

The sample program has a reference to RC-WinTrans' type library. The RC-WinTrans application ("RCWinTrans.Application") will be created with the new operator.

The program's source code file ("Form1.cs") contains the implementation of the sample code. The programming code contains several comments/descriptions.

The VBA macro is called via an RC-WinTrans COM object called "VBATool." This object has two methods ("RunFunction" and "RunMacro") to call any VBA function and/or any VBA procedure.

Preparing the parameters and a parameters array passed to the VBA macro, and calling the VBA using the RC-WinTrans COM object "VBATool" (file: "Form1.cs"). Two parameters (a string and a boolean value) are passed to the VBA macro that's called.

// Create an array to hold the parameters for the VBA macro to be called.

Array arrParams = Array.CreateInstance( Type.GetType("System.Object"), 2 );

// Two parameters to be passed to the VBA macro.

// The parameters must be object types.

object objMsg = msg; // a message text

object objBool = true; // just a boolean value

// Add the parameters to the array. The array will be passed to the "RunFunction" method

// of the RC-WinTrans "VBATool" object.

// 1. Message text

// 2. A boolean value

arrParams.SetValue(objMsg, arrParams.GetLowerBound(0));

arrParams.SetValue(objBool, arrParams.GetLowerBound(0) + 1);

// Encapsulate array in object variable (needed for running VBA function).

object objParamAry = arrParams;

try

{

// The "RunFunction" method of the RC-WinTrans "VBATool"

// HRESULT RunFunction([in] BSTR VBAProjectName, [in] BSTR ModuleName,
[in] BSTR FunctionName, [in] VARIANT* ParametersArray,

[out,retval] VARIANT* pReturnValue);

// The VBA macro

// Function WriteToOutputWindow(ByVal msg As String, ByVal bVal As boolean) As Long

// Run the VBA function.

// Macro project: "CSharpClient"

// Macro module name: "Module1"

// Macro name: "WriteToOutputWindow"

object retVal = rwtApp.Tools.VBATool.RunFunction("CSharpClient", "Module1",

"WriteToOutputWindow", ref objParamAry);

// The function of the RC-WinTrans "VBATool" is to call the VBA macro.

// Therefore, "VBATool" gets the information about the name of the macro, the name of

// the VBA project, and the name of the VBA module.

// The parameters array is passed to the "RunFunction" method.

// The parameters are those used to call the VBA macro.

}

catch

{

MessageBox.Show("Running VBA function failed.", "WriteToOutputWindow");

}

The example includes the VBA macros file containing a macro named "WriteToOutputWindow," that is called by the sample application.

The macros file was initially created with RC-WinTrans and the macro was coded using the Microsoft VBA editor in RC-WinTrans.

To demonstrate the passing of parameters to a macro and retrieving a return value, "WriteToOutputWindow" is a VBA function which has two parameters and a return value.

The macros file must be added to (i.e., enabled for use in) RC-WinTrans. You can do this in one of two ways:

NOTE: RC-WinTrans will ask you to copy the macros file to the default folder for macros files in the RC-WinTrans installation directory. Close the message box with "No" to not make a copy of the macros file and use the file in the sample directory.

You can use the VBA debugger to set a breakpoint into the VBA function to see when the macro is being called and executed.