Run external macro Version 4 (Net)

Action group: Excel 

Description

The action starts macro in an Excel file that has its VBA-code in a separate text file. 

Action icon

 

Settings of parameters

PropertyDescriptionTypeFilling exampleMandatory field
Parameters
ContextAction context for working with an Excel document.Robin.ExcelExcel contextYes
PathToFilePath to the macro text file containing the VBA code.Robin.FilePath
Yes
Procedure nameName of the called function or subroutine that is specified in the VBA code of the macro.Robin.String
Yes
ParametersValues of the input parameters of the called function or subroutine in the macro code. If it is necessary to specify several parameters, the user must specify in this field a collection containing these parameters. Parameter values must be specified in the same order in which these parameters are specified in the called function.Robin.Object
No
Results
ResultValue returned by the running macro. If the specified macro returns nothing, the result of the action will be empty.Robin.Object

Special conditions of use

To be able to run the external macro, it is important to take the following actions: 

  1. Open Excel options.
  2. Choose "Trust Center".
  3. Click the "Trust Center Settings" button.
  4. In the new window choose "Macro Settings".
  5. Check the "Trust access to the VBA project object model" setting (If it is not checked).

If it is necessary to specify several parameters, the user must specify in the "Parameters" field the collection containing these parameters. Parameter values must be specified in the same order in which these parameters are specified in the called function.

The Russian language is not supported in the macro name (name of the function). 

Example of use

Task

Given: an Excel document

Check the relevance of dates in the date column for the current month. In case the date is irrelevant, mark it in red color. Also display the number of irrelevant dates in the dialog box.

Solution

Use the following actions - "Open", "Run external macro", “Show message”, “Save”, “Close”.

Implementation

  1. Assemble a robot scheme consisting of actions:

2. Set the parameters in the "Open 1" action.

3. Set the parameters in the "Run external macro" action. 

Learn more about creating a macro here: Create or run a macro.

Example of a macro

Function TestMonth() As Long
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentMonthYear As String
    Dim cell As Range
    Dim counter As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    currentMonthYear = Format(Now, "MM.YY")
    counter = 0
    For Each cell In ws.Range("A1:A" & lastRow)
        Dim cellMonthYear As String
        cellMonthYear = Format(cell.Value, "MM.YY")
        If cellMonthYear <> currentMonthYear Then
            counter = counter + 1
            cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next cell
    TestMonth = counter
End Function

4. Set the parameters of the "Show message" action. In order to display the number of irrelevant dates it is necessary to pass the result of the macro to the "Show message" action parameter in the "Message Text" field:

5. Set the parameters in the "Save" action.

6. Set the parameters in the "Close" action.

7. Click the "Start" button and see the result. 

Results

Macro ran successfully 

  • Нет меток