With the release of EasyMorph version 5 comes an amazing new action called Excel Command which allows for running precreated Excel macros – subroutines or functions – from with a workflow, using the “Run VBA macro” setting.
This option opens up new potentials for processes outside the scope of EasyMorph like applying complex formatting, inserting or formatting objects (charts, pivottables, shapes, etc.), or triggering an Excel-based reporting process.
The sky’s the limit when you can have a complicated ETL workflow trigger an equally complex VBA macro, all with a single click. Add iterations to the mix, and….just wow.
The settings are pretty straightforward:
|File||The name of the .xlsm or .xlsb workbook holding the macro(s). This can have a static or relative path and can be defined as a static value, via a parameter, or via a column value.|
|Macro name||Enter the name of the sub or function within the selected .xlsm workbook to run. (31-character limit on the name.)|
|Capture return value||Check this if the macro is a function to capture the return value.|
|Macro arguments||If the macro being run is designed to receive arguments, list them here in the order defined in the macro.|
There are some general guidelines about where macros are stored, and how to access them.
To retrieve a value from a VBA function, the function must either:
- Reside in a Module (not behind a sheet or ThisWorkbook), or,
- It may reside behind a sheet or ThisWorkbook, but be called from a Module function, and qualified by the sheet name or ThisWorkbook:
ModuleFunction = ThisWorkbook.WorkbookFunction(arguments)
VBA subroutines can reside in Modules or behind sheets or ThisWorkbook. However, sheet or workbook subs must be qualified by the sheet name or “ThisWorkbook” to run properly.
Note that the total length of the VBA macro name (including any sheet or “ThisWorkbook” qualifiers) must be 31 characters or less.
As mentioned above, the arguments in the Excel Command | Run VBA macro action should align to the order of the arguments in the macro definition.
So, if the function definition starts with…
Function MultiplyMe( intNumber as Integer, strName as String ) as String
…the argument list in the action should look something like this:
- 20.5 (intNumber)
- Bob (strName)
Note, you don’t have to wrap text arguments in quotes here.
If the macro you’re running is a formula, you will have a return value that you can capture by checking on the Capture return value option in the action’s settings.
When captured, the current dataset will be the return value. If the return value is a single value, this will be a single-cell dataset (1 row x 1 column).
If, however, the return “value” is an array, the dataset will be a one-dimensional data set (1 column, multiple rows) with the values split down the rows.
If the return “value” (array) from the macro function is:
The current dataset will display as:
Alternatively, you can construct a delimited return value within the function to send back multiple values per row and then parse them into their own fields using the Split delimited text into columns action, for example.
Returning: “Bob | 20.5 | TRUE” (pipe delimited)
Would parse into:
|Col 1||Col 2||Col 3|
Please refer to the Macro accessibility section above regarding where to store VBA functions and how to access them to retrieve a return value.
If the macro being triggered runs into an issue, Excel’s debug window will appear, as usual, except it will be hidden under the EasyMorph application window. You may notice an extra Excel window open in the background at runtime, or the macro may seem to be taking longer than expected to run. These are signs you may have a hidden Excel dialog seeking your attention.
Click on the Excel icon in the Taskbar to force any dialogs to the front to handle them.
A nice feature of the Excel Command | Run VBA macro action is that it saves the macro workbook when finished with it. This means that if you had to debug the macro “on-the-fly”, you can resume the macro and the changes will be saved when EasyMorph closes the workbook.
Note, however, if you close the VBE without resolving the error, or just stop the macro from completing, EasyMoprh will return a warning for the action.
If you are running a macro that alters the contents or structure of its own workbook, be sure not to end the macro with a “Close” statement.
As EasyMorph holds the file open until the macro completes (reaching the “End Sub” or “End Function” statement), the “Close” method will fail and EasyMorph will return an error. EasyMorph saves and closes the workbook when it’s finished, so this statement isn’t necessary.
Following are some notes when running tasks on Server that include the new Excel Command action.
- Tasks that include the Excel Command action should be done in a Space with a Worker configured to run under a user’s account.
- Running a task on Server that includes the Excel Command action in “Run VBA macro” mode must be done on a system with a 64-bit version of Excel installed. Running the task with a 32-bit version will result in an error.
- While the List sheets/ranges mode will run without issues on Server, the other modes may result in the following error, depending on the configuration of the Server’s host system:
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
The following article contains several possible ways to resolve the error above:
Below is a .zip containing the files for a “Run VBA macro” process to show a couple of features. Extract all files into the same folder, launch the EasyMorph workflow (.morph file), and Run All Actions.
FormatMe.csv – acts as a data source file and is simply a small table of names and values.
Macros.xlsm – is the file containing a couple of macros (one Sub, one Function) that are triggered by the workflow.
Excel Command – VBA Macros examples.morph – is the workflow.
In short, the workflow reads in the data from FormatMe.csv and writes it out to an Excel sheet in a newly created file (“Data.xlsx”).
The first triggered macro accepts two arguments: an integer (multiplier) and a string (a formula). The number values in the Excel sheet are multiplied by the first argument and the formula is inserted at the bottom of the table (along with the heading “Total”).
The second macro is a function that accepts a single integer argument. The number values in the Excel sheet are multiplied by this value and the whole table is returned as an array.
Run VBA Macros example files.zip (16.3 KB)