Formula Explorer Pro

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.

ExplorerProForm

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!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

5 Responses to Formula Explorer Pro

  1. Yury says:

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

  2. Yury says:

    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

    • fastexcel says:

      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)

  3. SAM says:

    @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 ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s