AnalyzeDiscrepancies method |
This page in 2003 Top Previous Next |
|
Generates a new worksheet which sets out an analysis of the precedents of two cells, inspection of which may be helpful in identifying the cause of any differences between the two cells' values. Applies to SYNTAX result = expression1.AnalyzeDiscrepancies Set wb = expression2.AnalyzeDiscrepancies(range1, range2, levels, linkByValue, decimals, suppressNonMatched, maxCells, PruningMode) expression1 Required. An expression that returns an IOAKAddIn object. expression2 Required. An expression that returns an IOAKAPI object. result An OAKResult enumeration indicating the success or otherwise of the action. wb A workbook generated by OAK on which the discrepancy analysis is laid out. Range1 Required Range. The first of the two cells whose derivations are to be compared. Can be a Range object or a string that contains a cell reference in R1C1-style notation. Range2 Required Range. The second of the two cells whose derivations are to be compared. Can be a Range object or a string that contains a cell reference in R1C1-style notation. levels Required Integer. The number of levels of precedent that the analysis is to include. linkByValue Required Boolean. False to cause precedent cells that are common to both starting cells to be highlighted. True to cause precedents that are common to both starting cells to be highlighted if they have the same value, but are not necessarily located in the same cell. decimals Required Integer. The number of decimal places to compare when linkByValue is TRUE. Ignored when linkByValue is FALSE. suppressNonMatched Required Boolean. False to display the trees of both starting cells' precedents in their entirety. True to suppress the display of subtrees that have no common elements between the two fan-outs. maxCells Required Integer. Controls the potentially explosive fan-out of a precedent hierarchy by limiting the number of cells that will be included in the analysis in any large range referenced by a function. If a cell encountered during the discrepancy analysis contains the subexpression =SUM(A1:A1000), and maxCells is set to 20, only the first 20 cells in the range A1:A1000 will be explored in the analysis, not all 1000 of them. PruningMode Optional PruningMode. Specifies what, if any, pruning is performed on functions encountered during the analysis of precedent formulas. Remarks When applied to an IOAKAddIn object, the AnalyzeDiscrepancies method activates the same dialog box as is presented when the Analyze discrepancies command is selected manually from the OAK user interface. The action that follows is controlled by the data gathered by the dialog box from the user. When applied to an IOAKAPI object, the AnalyzeDiscrepancies method generates a discrepancy analysis identical to the one that would be generated by OAK if its Analyze Discrepancies command were activated manually from the user interface, but the details of the analysis are specified by the parameters submitted to the method. Example To analyse the differences in derivation between two cells MyRange1 and MyRange2, back to 4 levels of precedence, pruning occurrences of the COUNTIF and SUMIF functions Set MyWorkbook = MyOakApi.AnalyzeDiscrepancies MyRange1, MyRange2, 4, False, 0, True, 500, PruningMode_COUNTIF + PruningMode_SUMIF See also |