How can I automatically test a large, complex spreadsheet?

Question

So, I've inherited one of those "Engineer's Special" spreadsheets -- the kind developed by Mort the Engineer, with lots of hairy VBA and WTF, complicated calculations, and no documentation.

Fortunately, most of the primary inputs and outputs are on a single page. Maybe not enough for full test coverage, but a start.

I'd like to create a quick, poor-man's regression test framework for Excel, but I haven't quite figured out the best method.

My gut tells me to create some code that:

1. Has a macro that "saves" a the "main" I/O spreadsheet's values to another workbook, basically creating a user-named, value-only copy of the main input sheet. This "scenario" workbook would have, as its first tab, a list of all of the saved sheets, with metadata.

2. Has a separate macro that "loads" a given set of inputs from said sheets, but does not overwrite the cells used for output, headers, instructions, etc.

3. Has a third macro which is run from the scenario summary table. It would, in turn, load each scenario's inputs into the main workbook, compare each saved output to the current output, and record whether everything matches or not. It would run unattended until it reached a failure to match, then stop so the user can go look at the differences (which would be highlighted).

The two caveats: (1) this requires manual synching of the master sheet and scenario sheets when the design of the master changes enough to reposition/add/remove inputs or outputs, and (2) this doesn't scale well if each scenario includes inputs/outputs to test on a multitude of workbooks. Saving/loading named ranges for each I/O cell rather than entire sheets might help with both situations, but has its own drawbacks.

Has anyone come up with some other novel way to effectively regression test arbitrary spreadsheets?

Answer 1

To (1): the layout of your scenario sheet should be independent from the layout of the master sheet. The save and load routines should do the mapping between them. So when the input areas in the master sheet changes, you don't have to change all of your scenarios sheets, you only have to change your save and load routines.

Same can be achieved for the output areas by letting the comparer map between master sheet and "expected value" sheets.

It may be also a good idea to add some safety checks especially to your "load" routine which make sure nothing in your master sheet gets accidentally overwritten when copying the scenario data into the master, and that all data is placed in the correct cells.

(2): I admit, I do not understand exactly what issue you see here, but I guess running such tests automatically will always scale much better than doing the tests manually. So better first test your performance, and optimize when you see real performance problems, not the other way round.