Spreadsheet auditing (2 of many)

When I am looking at other peoples spreadsheets (well and my own actually) I like to use the audit arrows to see the dependents and precedents. I have used a few tools that create a separate chart of a dependency tree, but personally I like to see it all in context.

I have a couple of rough tools in this area that I use myself but are way too rough to release (this goes back to a discussion on DDOE about proportion of effort to get the key things working, compared to the effort to polish a product to a standard that be won’t be an embarrassment on release).  For XLAnalyst I think the core code that was useful to me took about 1/20th of the effort involved in the final released version.

Going forward I’m thinking I’ll just release the most useful bits here as source code, with a bit of discussion, and if people express enough interest in a proper packaged supported version then that may follow. If other people have code or ideas or other skills or services (anyone *like* documentation?) they might like to contribute to an open source VBA spreadsheet quality/audit type tool then please leave a comment.

When I am clicking around a worksheet I often like the arrows to follow me. If you stick the following 2 lines in the Workbook_SheetSelectionChange event of ThisWorkbook, that will happen for that workbook.

sh.ClearArrows
Target.ShowDependents ' or ShowPrecedents (or both)

sh and target are parameters passed into that event for you.

If you want that to work universally, then you need to wrap it up in a class to catch the events at application level. I’ll post that later with a proper workbook you can download and use. If anyone wants it?

A couple of issues around this

1. It breaks the clipboard, I think I ended up turning it off during build and only using it during review
2. ShowPrecedents/Dependents will show all (in the current workbook – doesn’t really work for external links (but youre not using those anyway right?)
3. The VBA range.Dependents or directDependents does not include off sheet references, making it somewhere between useless and dangerous.
4. None of this stuff covers conditional formatting or validation type dependencies, just cell formulas.

What are you favourite tools/techniques for sense checking formulas?

cheers

Simon