I have spent most of the last 18 months or so working on a new tool to improve on Excel’s ancient Trace Precedents and Evaluate formulas tools. It is called **Formula Explorer Pro**, and it is part of FastExcel Manager Pro.

Formula Explorer Pro supports Windows Excel 2007 through Excel 2016 and Office 365, and also Mac Excel 2016.

And now it’s time for a serious test of Beta version 2.5.

(Beta 2.6 Build 362.792 just uploaded)

For a quick overview of Formula Explorer Pro see https://vimeo.com/272986447

And to download the latest Beta version see http://www.decisionmodels.com/fxl_mgrpro_beta2.htm

Please let me have your feedback, bug reports, suggestions etc.

### Explorer Pro Technology

The Explorer Pro Userform is probably the most complex VBA userform I have ever built.

Because it has to work on both Mac and Windows Excel I cannot use any Windows API calls.

The userform:

- Is modeless
- Resizeable
- Has 3 splitter bars
- Has 2 synchronised Treeview controls and 2 textboxes
- Adaptive positioning of controls, dependent on both mode and expression selection.

I wish I could say I developed all the technology that underlies this, but it relies heavily on Jan Karel Pieterse and Peter Thornton’s VBA Treeview, and Andy Pope’s work on resizeable userforms and splitter bars …

Other major components of Formula Manager Pro are the formula parser, expression builder, formula indenter, expression evaluator and forward/backward formula debugger.

The formula parser splits a formula into token strings in the local language being used, and then translates the token strings into US English (thanks to Mourad Louha for his help with testing and extending this to 91 different languages).

The expression builder assembles the token strings into expressions that can be evaluated (this includes the strange criteria expressions that occur in functions like SUMIFS).

The formula indenter takes the expressions and builds indented formula strings using the indenting rules that the user has chosen.

The expression evaluator evaluates each of the expressions to produce a scalar or array result. Special handling is needed for things like the criteria expressions in SUMIFS and finding the source precedents that functions like MAX, LOOKUP, SUMIFS, SUMPRODUCT etc are pulling from. Designing the evaluator is hard because there are many different ways you can build sub-expressions from a formula like =A1+B2+C3+D4*E5+F6.

Another tricky evaluation problem is correctly handling implicit intersection, particularly for formulas like =VLOOKUP(A:A,B:F,G:G,H:H) because some of the arguments do implicit intersection and others (B:F) do not. This requires a lookup table for every argument of every native Excel function.

The step-by-step formula debugger actually drove some of the design choices in the expression builder because it requires an unambiguous set of expressions to can be condensed to results for replacing the expression string in the indented formula.

**Anyway this is why we need an extended Beta test period!**

It looks quite amazing. Like the formula indenter and tree view are really helpful. Will continue exploring, thanks for your effort!

One small comment – when I work on two screens and using Formula Explorer in one screen and Excel is in another monitor, and I expand one of the nods in the formula tree (left bottom box) – the user form tends to jump to the same screen where Excel is open. Don’t think that’s the kind of planned behavior

Thanks Yuri, I think I need to do a bit of redesign for the case where Formula Explorer is not within the Excel window.

Could you try switching off Smart Scrolling (Formula Explorer Settings) to see if that helps?

( I am out of the office for a few days)

Just uploaded Beta 2.6 build 362.792 which fixes the two screen problem – (and sevaeral other things!)

@Charles :In cell A1:A20 fill 4 values – North, South, East, West in a Random order

In Cell B1:B20 fill random numbers

In Cell G1 type this formula

=INDEX(B1:B20,N(IF(1,MODE.MULT(IF(A1:A20=”North”,ROW(A1:A20)*{1,1})))))

Does your expression evaluator – return the Array that F9 returns ?

@Charles

Did you play around with Dynamic Array formulas – Like SORT / FILTER / SEQUENCE etc