Is there a way to identify all circular reference in Excel via VBA?

Question

I am looking for a way to identify all circular reference cells and record their addresses into an array. For now, I can use the Formula tab's Error Checking > Circular Reference and ActiveSheet.CircularReference to get the error. However, both of these methods only find one circular reference and left out other circular references in the worksheet. Is there a way to identify all of the circular references? Thank you

Answer 1

You can use the Range.Precedents property. This returns a recursive set of cells that are referenced by a cell. If that set includes the cell in question, then it's part of a circular reference.

Demo:

Sub Demo()
    Dim ws As Worksheet
    Dim rFormula As Range
    Dim rPrecedents As Range
    Dim rCircular As Range
    Dim rCell As Range
    
    Set ws = ActiveSheet ' change to suit your needs
     
    ' only consider cells with formulas
    On Error Resume Next
    Set rFormula = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    
    If Not rFormula Is Nothing Then
        
        ' loop as Formula cells
        For Each rCell In rFormula
            ' Check if Formula references any cells
            Set rPrecedents = Nothing
            On Error Resume Next
            Set rPrecedents = rCell.Precedents
            On Error GoTo 0
            If Not rPrecedents Is Nothing Then
                
                ' if Precenents includes rcell, then its circular
                Set rCircular = Nothing
                Set rCircular = Application.Intersect(rPrecedents, rCell)
                If Not rCircular Is Nothing Then
                    Debug.Print rCircular.Address
                    ' Add rCircular.Address to your array here
                    '...
                End If
            End If
        Next
    End If
End Sub

_{Note that this will only work if all precedents are on the same sheet}