Prune

Simplifies formulas in cells by removing branches of conditional or lookup functions that are inactive given current inputs.

Applies to

IOAKAddIn, IOAKAPI

SYNTAX

result1 = expression1.Prune

expression2.Prune(Range1, mode, sumExpansionLimit, preserveNames)

expression1   Required.  An expression that returns an IOAKAddIn object.

expression2   Required.  An expression that returns an IOAKAPI object.

result1   An OAKResult enumeration indicating the success or otherwise of the action.

Range1           Required Range.  The range in which to search for cells that are not mentioned by any formula.

mode   Required PruningMode. Indicates which functions are to be pruned by the method.

sumExpansionLimit Required Integer.   Any SUM() function which acts on this number or fewer cells will be expanded, that is, turned from SUM(A1:A3) to A1+A2+A3.

preserveNames   Required Boolean. True if the pruned formula is to reuse any names that were present in the original. False if names are to be removed from the formula.

Remarks

When applied to an IOAKAddIn object, the Prune method performs the same action as the OAK | Formula | Prune command, including presenting the same dialog boxes. It acts on the current selection.

When applied to an IOAKAPI object, the Prune method acts on the cells specified by Range1.

Wrinkles

When applied to an IOAKAddIn object, the Prune method does not follow the One-cell rule.  This departure from the practice of Excel and OAK in other functions is intentional.  Pruning is a potentially destructive action, delivering a spreadsheet that gives unchanged answers with current inputs, but which might give quite wrong  answers with other inputs.  It is only permitted to act on cells that are selected explicitly.

When applied to an IOAKAddIn object, the method will display a dialog box before performing the pruning, to ask which functions are to be pruned, just as the equivalent OAK user-interface command does.

It is for any code to ensure that worksheets are thoroughly recalculated before using these methods.  It is for the user to keep a copy of the spreadsheet before letting OAK alter it through pruning.