Making sense of complex Formulas: an Indenting Viewer-Editer

Some time ago I was working with a client to speed up one of their workbooks.
I was using the FastExcel V3 formula profiler and it showed that one formula was taking a significant proprtion of the calculation time.

And the formula was too complicated to easily understand. So I decided that FastExcel V3 really needed a better way of understanding, creating and modifying formulas and started developing one. It currently looks like this:

Of course the trouble with creating a formula indenter is:

• Nobody agrees what the “correct” indentation style is!
• And anyway what works well for one formula does not neccessarily work well for another formula.

So I added the ability to dynamically switch indentation styles: for me splitting the OR(…) section by commas makes it easier to read –

The Viewer-Editer also helps you debug the formula by showing you the result of the selected portion of the formula and by making it easy and fast to jump to and select different parts of the formula.

The Select options work in conjunction with the navigate arrows (Next left, This, Next right, Expand selection, Contract selection).
So if you click the right arrow with Functions selected the selection jumps to the next function on the right and shows you the result in the evaluate box.

Modifying the Formula

You can modify the formula by directly editing the formula text and there are also many of the familiar Excel tools built-in:

• Function Wizard
• Insert a Reference or a Defined Name
• Change a reference from Relative to Absolute (F4)
• Build up a Mega-Formula by inserting a copy of another formula

Clicking the Function Wizard button when a function is selected brings up the function wizard for that function so that its easy to change:

but if nothing is selected then the Function Wizard is called allowing you to choose a function, enter its parameters, and have it inserted at the current insertion point.

Conclusion

I have added quite a lot of function to the viewer-editer since the original concept, but I am sure it can be improved further.

So please download FastExcel V3 Beta 3, try it out and let me have your comments.