Manual Fallback: The Invisible Worker Rule

There are many ways to write macros. From experience, on a business environment, there are some general rules that will make them easier to write and use at the long term.

There is however one rule to “rule them all":

• MANUAL FALLBACK “the invisible worker”: the best macros are “invisible”. They produce Excel sheets exactly like the ones being created manually. They do not have macros themselves or macro functions, they contain numbers, text and links. Once done, it is impossible to distinguish between the macro output or a manual created one;

This is very important as it guarantees a fallback – if for any reason the macro fails (and they do fail) and it is not possible to overcome the issue (because the person who did the macro is not available, or it will take too much time), it can still be done by hand.

As an example: on one company, every month, hundreds of similar Excel files must be linked into a “consolidated” view. All input files where pretty similar but with slight differences, so linking could not solve the problem because the exact cells to link would change every month. They had to be found by criteria, not hard links to specific cells. This was done manually by one person for a whole week, and would usually have errors and hundreds of links would be created from the consolidated file to each input file.

Instead of creating a macro on the consolidated file “pulling” the values, the macro was created on a separated file. It would scan all input files and create the links on the consolidated file. Once done, it was similar to the file that was manually created before. But it would only took 2 hours to run and had no errors.

So, the macro was an “invisible worker”, doing in 2 hours the exact same work as before in 5 days, freeing one person to do values added tasks instead on Excel links. But the end result can still be checked, validated or performed manually, in case the macro fails.

Work smarter, outsource your boring work to your “invisible worker”!