Excel, even in the newest version, has poor support for managing and debugging formulas. Formula X-Ray fills the gap that "Evaluate Formulas" leaves.
What it does
- "Formula Inspector": Show complex Excel formulas in a graphical view, letting people see results of subformulas, replacing subformulas by results and back, show where errors came from, and much more
- "Find Similar Formulas": Finds cells with identical or similar formulas compared to a reference cell
- "Formula Makeup": Automatically adds blanks to formulas to increase readability, comparable to AAutoFormat in modern IDEs
- Support for non-English Excel installations by translating formulas to and from English, in order to benenfit from the many English Excel sources on the web.
How I built it
- The code is purely based on Office VSTO and C#/VS 2015. It contains no VBA
- The addin's setup is based on WIX technology
Challenges I ran into
- Calculating the subformula results is done by using the cell itself in the background, without the user noticing it. This approach was necessary, because Excel's Evaluate() method has lots of limitations. The chosen approach ensures 100% the same results as if the subformulas were calculated by Excel - well, they are!
- Running background tasks without interfering with the foreground actions the user does with Excel (e.g. deleting rows)
- (Known) limitations of the Excel API, e.g. the lack of an event that fires when an workbook has been closed
- I wasn't able to use the default Excel formula edit box
- Excel offers no way to catch unhandled exceptions centrally
- Creating modeless windows that stay on to of the Excel grid, yet that are not system-topmost and that go out of the way if the user opens the backstage view
Accomplishments that I'm proud of
- The control that shows the formula using colored brackets
- The many new possibilities users have when trying to understand what a complex formula does and why
- The (hopefully) good usability
- The code that finds not only identical, but also similar formulas and that shows the difference between formulas in a graphical way (similar to MS Word's Review markup)
- The modeless windows that overcome the challenges mentioned above
What I learned
- I gained a profound knowledge of the Excel and Office VSTO API
- I got my first insights about WIX setup technology
What's next for Formula X-Ray
- Automatically detecting common flaws in user's sheets, like SUMs that "forget" the last row of a table
- An LDAP interface that helps users in creating lists of persons, e.g. "all people reporting to the CFO", "all people in building 7"
- An extended commenting function that helps authors of sheets to comment their own formulas, and that's more powerful than plain Excel cell comments
- Dozens of more in the backlog...