Testing, Testing, Testing

Excel workbooks are notoriously difficult to test thoroughly. There are many well documented losses caused by mistakes in spreadsheets. These range from errors in formulae, formulae overwritten with hard coded values, discrepancies in ranges and many other problems.

The problem with testing is that data has to be written into the relevant input cells, the workbook recalculated and the results eyeballed to see if they look correct. It is often a manual process and tends to be ‘forgotten’ until a problem occurs and then everyone wants to know why it wasn’t properly tested before.

The PersisTable Toolkit provides the ideal tools to capture input and output and to reload data and replay calculations, allowing the calculations to be compared with previous results and detect unexpected changes.

Having created a Template Workbook, the input and output data is captured off spreadsheet. The inputs can be arranged to be reloaded from the PersisTable store and the workbook recalculated This will create a set of results saved as PersisTables. By capturing the validated results in one Arena and saving the Test results in another, it is possible to compare the two using the TKTable_Compare() function. This will clearly highlight any differences. (Note if there are variables such as date/time values these will always be highlighted as differences).

UAT Arena set to Fallback to Production allowing original data to be extracted from Production but updates only applied to UAT Arena

This approach to testing is very flexible, new tests are created by saving the Production scenario to the UAT Arena. Relatively simple VBA macros can be created to automate the selection and execution of tests.