Executing a VBA Macro from C#

 

Introduction

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.

 

If RC-WinTrans is not already running, the C# client will start it.

 

Sample type: software project

Sample name: "CSharpClient"

Directory (location): ...\Samples\Developing\Programming\CSharpClient\

 

 

Technology

 

Program type:  EXE, COM automation client

Software platform:  Microsoft .NET 1.1

Programming language:  C#

Development environment:  Microsoft Visual Studio .NET 2003

 

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.

 

 

C# Code

 

Starting RC-WinTrans (file: "Form1.cs"):

 

// Gets the running RC-WinTrans application or starts RC-WinTrans if it is not running yet.

 

RCWinTrans.Application rwtApp = new RCWinTrans.Application();

 

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");

}

 

^ Top ^

 

The VBA Macro

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

 

Macros file name: "CSharpClient.macro"

Location (in the sample project): ...\CSharpClient\CSharpClient

 

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.

 

Enabling the Macros File

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

a.

use the drag-and-drop mechanism to move the file "CSharpClient.macro" to the main window of RC-WinTrans; or

b.

select the Macros Files command from the Tools menu.

 

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.

 

Once the macros file is added to RC-WinTrans the sample program can be run.

 

The VBA function "WriteToOutputWindow" (file: "CSharpClient.macro"):

 

Function WriteToOutputWindow (ByVal msg As String, ByVal bVal As Boolean) As Long

 

RCWinTrans.Windows.MultiViewBar.Output.SetVisible

  RCWinTrans.Windows.MultiViewBar.Output.AddText (msg)

  WriteToOutputWindow = True

 

End Function

 

^ Top ^

 

Debugging the Macro

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

 

Procedure:

1.

Start RC-WinTrans.

2.

Open the VBA Editor by selecting the Visual Basic Editor command from the Tools menu.

3.

In the "Project Explorer" tree view of the integrated VBA development environment, select the project named "CSharpClient".

4.

Open "Module1" in the "CSharpClient" project.

5.

Use the F9 functionkey to set a breakpoint in the "WriteToOutputWindow" VBA macro.

6.

Start the sample program and use the command button to call the macro.

 

 

Running the Sample Macro

1.

Start RC-WinTrans and add the macros file "CSharpClient.macro".

2.

Compile and start the sample program "CSharpClient".

3.

In the CSharpClient application (a dialog box), use the appropriate command button to call the macro in RC-WinTrans.

4.

See the Output window in RC-WinTrans for the message text.

 

^ Top ^