Detecting Circular Reference

Introduction

Workbooks can contain circular references, and sometimes there is a need to detect whether circular references are present.

Concept behind detecting the circular reference

Circular references can only be detected when the formula is calculated because the references of one formula often depend on the calculated results of other cells or formulas. Therefore, we provide new APIs for this requirement (to gather cells with circular references) in the process of formula calculation:

CalculationCell: Represents the calculation of relevant data about one cell being calculated

AbstractCalculationMonitor.OnCircular(IEnumerator circularCellsData): will be invoked by the formula calculation engine when it encounters circular references; the elements in the enumerator are CalculationCell objects that represent all cells in a circular chain. The returned value indicates whether the formula engine should continue calculating those cells involved in the circular reference after this call.

Users may gather those circular references in the implementation of the AbstractCalculationMonitor.OnCircular() method.

The source sample file can be downloaded from the following link:

Circular Formulas.xls

The definition of the CircularMonitor class, which derives from the AbstractCalculationMonitor class, is as follows: