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
- It breaks the clipboard, I think I ended up turning it off during build and only using it during review
- ShowPrecedents/Dependents will show all (in the current workbook – doesn’t really work for external links (but youre not using those anyway right?)
- The VBA range.Dependents or directDependents does not include off sheet references, making it somewhere between useless and dangerous.
- 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
