How to compare 2 complex spreadsheets running in parallel for consistency with each other?

I am working on converting a large number of spreadsheets to use a new 3rd party data access library (converting from third party library #1 to third party library #2). fyi: a call to a UDF (user defined function) is placed in a cell, and when that is refreshed, it pulls the data into a pivot table below the formula. Both libraries behave the same and produce the same output, except, small irregularites can arise, such as an additional field being shown in the output pivot table using library #2, which can affect formulas on the sheet if data is being read from the pivot table without using GetPivotData.

So I have ~100 of these very complicated (20+ worksheets per workbook) spreadsheets that I have to convert, and run in parallel for a period of time, to see if the output using the new data access library matches the old library.

Is there some clever approach to do this, so I don't have to spend a large amount of time analyzing each sheet to determine the specific elements to compare?

Two rough ideas that come to开发者_高级运维 mind:

1. just create a Validator workbook that has the same # of worksheets, and simply do a Worbook1!Worksheet1!A1 - Worbook2!Worksheet3!A1 for every possible cell on each sheet

2. roughly the equivalent of #1, but just traverse the cells in the 2 books using VBA, and log any cells that do not match.

I don't particularly like either idea, can anyone think of something better than this, maybe some 3rd party utility I could buy?

Sounds like its time for a serious fundamental redesign rather than swapping data access libraries.
But, to address your question:
- I don't think a 3rd party utility to do this exists.
- a VBA approach to do this using variant arrays to get the used-range from each sheet would be reasonably easy & efficient as long as you don't try to traverse the sheets cell-by-cell.