Best Practices for Using Excel Functions

This topic provides some best practices when automating processes using the MS-Excel built-in functions.

Condition Workflows on Function Success

Workflows that automate operations in Excel should automatically verify that each function was performed as expected before continuing.

For example, the workflow step below runs three macros in an Excel file. The first two macros enter random numbers in cells A1 and A2. The third macro calculates their total and writes it to cell A3.

During run-time, each one of those macros may not run as expected, but the workflow will continue regardless. For example, while testing, it was found that the first macro often was not run completely so the first random number was not entered.

Best practice is to verify that each macro completes before continuing to the next workflow step. In the case of these simple macros, the workflow should check whether the cell that was meant to be populated is in fact populated.

For a macro that doesn't populate a previously empty cell, the recommended practice would be to modify the macro so that once it completes its main functions, it will populate some previously unpopulated cell. For example, it could write a code "Macro XY complete" to a certain cell on a dedicated sheet of the Excel file.

In the modified workflow below, each macro is run from a separate workflow step.

  • The first step runs the first macro.

  • The second step reads the cell that the first macro populates, A1, and stores the value in the variable macro_1_out.

  • The decision step checks whether the variable macro_1_out is empty or not. If it is not empty, the workflow progresses to the next step. If it is empty, then the workflow returns to the first step to attempt to run the first macro again.