The Automation Toolkit is built using VBA in Excel. Therefore, it can leverage the capabilities of VBA to provide additional functionality.
This article explains how to extend the Automation Toolkit beyond the default actions and enable integration into larger processes.
Contents
- Run an automation recipe from a shape or button
- Run automation recipe from a macro in another workbook
- Build your own action steps using macros
Run an automation recipe from a shape or button
An automation recipe can run directly from a shape or button on a worksheet. Let’s take a look at two ways to achieve this.
Call named automation recipe
Create a shape on the worksheet.
Right-click on the shape and change the name in the name box as follows:
Sheet:Sheet Name- Sheet: Tells the Automation Toolkit that the following text is the name of the sheet containing the automation recipe
- Sheet Name: Replace this with the name of the sheet containing the automation recipe
In the example below, the recipe sheet is called Automation Recipe.
Right-click on the shape and select Assign Macro… from the menu.
In the Assign Macro dialog box, select the CallAutomationShape macro, then click OK.
That’s it. Clicking the shape runs the named automation recipe.
Call automation recipe based on cell value
To add even more flexibility, the recipe sheet name may be based on a cell value.
Create the shape as per the previous section, but name the shape as follows:
Range:A1- Range: – tells the Automation Toolkit that the following text is the name of the range containing the automation recipe. The range must be on the same sheet as the button.
- A1 – replace this with the cell reference containing the name of the recipe sheet.
For example, look at the screenshot below. The shape is called Range:C2 and cell C2 contains the name of the recipe sheet.
Assign the CallAutomationShape macro to the shapes as before.
Clicking the shape executes the automation recipe named in the cell.
Run automation recipes from a macro in another workbook
Automation Recipes can be run from another workbook.
The following macro runs the recipe on the WorksheetName tab of the WorkbookName.xlsm workbook.
Run "'WorkbookName.xlsm'!CallAutomationMacro", "WorksheetName"'Replace the following items to fit your scenario.
- WorkbookName.xlsm: Name of the workbook containing the automation recipe
- WorksheetName: Name of the worksheet containing the automation recipe
In the code example, pay close attention to the placement of the single and double quotes characters.
All the elements in the VBA above are text strings and can be replaced with cell values if required.
NOTE: The workbook containing the automation recipe must be open for the macro to execute.
Build your own action steps using macros
Using the Excel Macro Run action, you can run your own macros with up to 15 arguments form an automation recipe.
Example with no arguments
Let’s assume Workbook A.xlsm contains the following macro called myMacro.
Sub myMacro()
MsgBox "Hello"
End SubThe macro above displays a message box containing the word Hello.
To call this macro from the Automation Toolkit we use the Excel Macro Run action.
FileName:Workbook A.xlsx
Macro Name:myMacro
Arguments:
Separator:,
Running the recipe executes myMacro from Workbook A.xlsm, and reveals the message.
NOTES: The workbook containing the macro must be open. If necessary, use the Excel Workbook Open action prior to the Excel Macro Run action.
Example with basic arguments
Now, let’s assume Workbook A.xlsm contains a macro called myTextMacro. It accepts two string arguments, text1 and text2.
Sub myTextMacro(text1 As String, text2 As String)
MsgBox text1 & " " & text2
End SubThe parameters to call this macro would be as follows.
File Name:Workbook A.xlsm
Macro Name:myTextMacro
Arguments:Hello,World
Separator:,
The Separator argument declares the string used to separate each argument. In this example, the separator is a comma ( , ); therefore, the first argument is Hello, and the second argument is World. The separator character(s) must not be used within the arguments.
Example with complex arguments
All arguments are passed from the Automation Toolkit to the macro as text strings.
Where numeric or boolean values are required, the macro must convert the text to the correct data types.
VBA contains type functions (https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions) to convert the text to other data types.
Common data conversion examples:
- CBool: Converts text to a boolean value
- CLng: Converts text to a long
- CDbl: Converts text to a decimal / floating point number
- CInt: Converts text to an integer
Now, let’s assume Workbook A.xlsm contains a macro called myComplexMacro (see below). It contains calculations using numbers. CInt converts the text to numbers prior to calculation.
'Note all arguments are passed as text strings
Sub myComplextMacro(name As String, age As String, years As String)
Dim futureAge As Integer
'Convert text values to integers
futureAge = CInt(age) + CInt(years)
'Output a message
MsgBox name & " will be " & futureAge & _
" in " & years & " years"
End SubThis macro can be executed with the following parameters
File Name:Workbook A.xlsm
Macro Name:myComplexMacro
Arguments:Mark,43,10
Separator:,
The result of running this recipe is:
Using line breaks within arguments
By default, the line break is a special character within the Automation Toolkit; it separates each parameter line. Where a line break is required within an argument, the automation default settings must be modified.
Let’s assume Workbook A.xlsm contains a macro called myLineBreakMacro with a single text argument.
Sub myLineBreakMacro(text As String)
MsgBox text
End SubTo pass a text string, including a line break from the Automation Toolkit, the default settings must be changed using the Automation Settings Change action.
In the screenshot above:
- Using the Automation Settings Change action, the Parameter Separator for the automation is changed to //
- The Excel Macro Run action is changed manually so that // separates each parameter. A line break character can be included between Hello and World, because it is no longer the parameter separator
- The Automation Settings Change action changes the Parameter Separator for the automation back to a line break
The result of this automation recipe is:
Passing objects into a macro
The Automation Toolkit is built upon the Unix Philosophy, therefore all arguments are passed to macros as text values. Therefore, It is not possible to pass objects as arguments. However, the name of the object can be passed as text.
The following macro requires an object as a parameter. This will not work with the Automation Toolkit.
Sub passWorkbook(wb As Workbook)
MsgBox wb.Sheets.Count
End SubThe following macro requires only text parameters and could be used as an alternative to passing objects:
Sub passWorkbookName(wbName As String)
MsgBox Workbooks(wbName).Sheets.Count
End SubDiscover other articles:
