Automation Toolkit Advanced

< Back to Help menu

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.


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.

Run Automation Recipe from button

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.

Assign CallAutomationShape to the button

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: – 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.

Run Automation from button based on cell

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 Sub

The 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

Run Excel Macro screenshot

Running the recipe executes myMacro from Workbook A.xlsm, and reveals the message.

Result of running a macro

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 Sub

The parameters to call this macro would be as follows.

File Name:Workbook A.xlsm
Macro Name:myTextMacro

Run Excel Macro Text arguments screenshot

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.

Result of running macro 2

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 ( 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 Sub

This macro can be executed with the following parameters

File Name:Workbook A.xlsm
Macro Name:myComplexMacro

Running a complex macro

The result of running this recipe is:

Result of passing a complex macro

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 Sub

To pass a text string, including a line break from the Automation Toolkit, the default settings must be changed using the Automation Settings Change action.

Text string with line break example

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:

Macro with line break

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 Sub

The 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 Sub

Discover other articles:

Getting StartedFeature OverviewActions ReferenceUseful Formulas

AdvancedFeature RequestReport Bug