Automated benchmarks (Excel 2000)

[ChrisGreaves]

I am the world's second-biggest fan of benchmark testing.

The demo application (attached) has an input-Output sheet with an input area (pale yellow) and and output area (blue).
Select a city from the data validation list, and watch the results change.

Now switch to the corresponding benchmark sheet (if you chose Mississauga as the city, choose the sheet "BM003") and run the macro "TESTBenchmarkWorksheet".

If the frozen values match the recalculated values, nothing untoward is signaled.
If the values do not match, the macro stops after highlighting the offending cell.

If you would like to set up another benchmark sheet, run the macro "CopySheet" to create, say, BM004.
Key in new input values in the Input_output sheet, copy/pasteSpecial-Values the input and output cells to column C of the new sheet.
Run the macro "TESTBenchmarkWorksheet".

Of course, this application is almost too trivial to consider, but now think of that sevenseventeen-sheet wonder generated by your client; your job is to re-organize the calculations to provide a simpler version while maintaining what the client says is accurate.

Benchmarking is a fast and sure way of checking that your interim changes do not affect the final result.

Of course, the procedure "BenchmarkWorksheet" can be incorporated into a loop that uses every sheet whose name is of the form"BM0??".