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