Run external macro Version 4 (.Net)
Группа действий: Excel
The action starts macro in an Excel file that has its VBA-code in a separate text file.
Action icon
Action parameters
Input parameters
- "Context" field - a type of a Word document where the external macro will run.
- "File" field – path to the macro text file containing the VBA code
- "Procedure name" field - name of the called function or subroutine that is specified in the VBA code of the macro.
- "Parameters" field - Values 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.
Settings
Property | Description | Type | Filling example | Mandatory field |
---|---|---|---|---|
Parameters | ||||
Context | Action context for working with an Excel document. | Robin.Excel | Excel context | Yes |
PathToFile | Path to the macro text file containing the VBA code. | Robin.FilePath | Yes | |
Procedure name | Name of the called function or subroutine that is specified in the VBA code of the macro. | Robin.String | Yes | |
Parameters | Values 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 | ||||
Result | Value 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:
- Open Excel options.
- Choose "Trust Center".
- Press the "Trust Center Settings" button.
- In the new window choose "Macro Settings".
- 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
The implementation of the action requires the following actions - "Open", "Run external macro", “Message”, “Save”, “Close”.
Implementation
- Assemble a circuit diagram of a robot consisting of actions:
2. Specify the parameters in the "Open 1" action.
3. Specify the parameters in the "Run external macro" action.
4. Learn more about creating a macro here: Creating and running 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
5. Customize the parameters of the "Message" action. In order to display the number of irrelevant dates it is necessary to pass the result of the macro to the "Message" action parameter in the "Message text" field:
6. Specify the parameters in the "Save" action.
7. Specify the parameters in the "Close" action.
8. Press the "Start" button and see the result.
Results
Macro ran successfully