SearchForUnreferencedCells

Searches a specified area of a worksheet for cells aren't referenced by any formula.

This method is important because it can defend against some kinds of the most hard error to detect, errors of omission.  A cell that contains a number that does not participate in any formula may have been left out of a model unintentionally.

Applies to

IOAKAddIn, IOAKAPI

SYNTAX

result1 = expression1.SearchForUnreferencedCells

Set result2 = expression2.SearchForUnreferencedCells(Range1,formula, numeric, text, logical)

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.

result2   A Range containing of all the cells within Range1 that are not mentioned in at least one formula, or Nothing if no such cells are found.

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

formula, numeric, text, logical   Required Boolean. True if the method is to return cells of the various types listed.

Remarks

When applied to an IOAKAddIn object, the SearchForUnreferencedCells method performs the same action as the OAK | Search Selection | Unreferenced cells command.   It searches for cells within the current selection that are not mentioned by a formula in at least on other cell.

When applied to an IOAKAPI object, the SearchForUnreferencedCells method applies searches for cells within the the range indicated by the parameter submitted to the method that are not mentioned by a formula in at least on other cell.

It would be common to set the formula parameter to FALSE to prevent the method from selecting the formulas on the worksheet, some of which at least would be  final outputs which by definition are not referenced by other formulas.

It would be common to set the text parameter to FALSE to prevent the method from selecting the text labels on the worksheet, which one would not expect to be referenced.

Wrinkles

When applied to an IOAKAddIn object, the SearchForUnreferencedCells method follows the One-cell rule.  When applied to an IOAKAPI object, it does not: if supplied a Range parameter describing a single cell, it will look in that cell only.

When applied to an IOAKAddIn object, the method will display a dialog box before performing the search to ask what kinds of cells are to be selected, and another  after the search if no unreferenced cells are detected, just as the equivalent OAK user-interface command does.

Both methods will report as unreferenced cells which, while not mentioned by any formula on the same worksheet, are mentioned by a formula on another worksheet.