InvokeVBA

This activity enables the user to execute custom VBA code directly within an Excel sheet. It must be used within the “Excel Scope” activity and supports execution in both “.xls” and “.xlsx” file formats.

Pre-requisites

This activity allows the execution of VB script only if access is enabled in the Excel sheet. Follow the steps below to enable it:
1. Open Excel and navigate to File → Options → Trust Center → Trust Center Settings.
2. Under Macro Settings, select the checkbox for Trust Access to the VBA project object model.
Ensure this setting is enabled before using the activity.

Properties

Input

VBScriptPath: *Indicates to provide the VB script path and it accepts values in “String” datatype. You can either hardcode the values or use a variable of the “String” datatype. 

MethodName: *Specify the “Method name” from the script to be called and execute the functionality. It accepts values in “String” datatype, either hardcode the values or use a variable of the “String” datatype. 

MethodParameters: This parameter specifies the input arguments to be provided for the specified method name.

MISC

DisplayName: Displays the name of the activity. The activity name can be customized, which aids in troubleshooting.

SkipOnError: Specify the “Boolean” value as “True” or “False.”
True: Continue executing the workflow regardless of any errors thrown.
False: Halt the workflow if it encounters any errors.
None: If the option is left blank, the activity will, by default, behave as if “False” were chosen.

Version: It indicates the version of the feature being used.

OUTPUT

Result: This parameter allows you to view the execution status of the activity and returns a value in “Boolean”.
True: Indicates that the activity has been executed successfully without any errors.
False: Indicates that the activity has been unsuccessful due to an unexpected error being thrown.

Output: This parameter allows you to view the output of the script execution and result from the script. 

* Represents mandatory fields to execute the workflow.

Example 

In the following example, the “Invoke VBA” activity is utilized to highlight the text based on the length in the provided sheet. Here we are highlighting the text value that exceeds more than 10 characters in red in the sheet. Refer the attached sample VBS code. 

HighlightText VB script

Note: Download the above VBS code and save it as “.vbs” extension type. 

1. Drag and drop the “Excel Scope” activity into the workflow and choose the downloaded excel sheet path to automate. 
2. Add the “InvokeVBA” activity within the body of the excel scope.
a. Now, choose the downloaded “VB script path”. 
b. Enter the method name as “HighlightLongTextWithParams”. Refer the attached code. 
3. Before moving to the “Method parameters”, create 2 variables as below. 
a. SheetName with “String” as datatype to provide the sheet name. 
b.  TextLength as “Integer” as dataype to provide the length as “10”.
c. Now, add these values to the “MethodParameters” property. 
4. Save and execute the workflow.