How to deal with Circular References in VBA?

Question

I would like to build an Excel function in VBA that checks the presence of a circular reference in a certain cell. I want the function to return a Boolean "TRUE" if there is a circular reference and "FALSE" otherwise. Here is my failed attempt:

Function CIRC_REF(BadCell As Range) As Boolean
    If BadCell.CircularReference = True Then
        CIRC_REF = True
    Else
        CIRC_REF = False
    End If
End Function

Unfortunately, this only returns a #VALUE! error in the evaluating cell. I tested it on a Worksheet which A1 = 1, A2 = 2, A3 = A1 + A2 + A3. Then I set B3=CIRC_REF(A3).

Please don't reference something/post a picture of it when you didn't bother reading it. — findwindow, May 13 '16 at 20:21
Voting to close as "too broad" because it's essentially asking the SO community to do all the work. See [ask] help center page for help about asking a good on-topic SO question. Also, downvoted for flagrant lack of effort and overall unclear question. — Mathieu Guindon, May 13 '16 at 20:39

score 0 · Answer 1 · answered May 29 '16 at 06:28

The best solution to this question may not be calling a function, but rather calling a macro with a sub procedure. If you make a selection of cells with formulas in it, then if any cell has a circular reference, the procedure returns a Boolean True to the cell one column-cell to the right of the culprit cell. You would have to change the code to change this behavior. For example, a small change would allow you to record the address locations of cells that contain a circular reference on a new sheet. Here's the code:

Sub mcrCircularReference()
    Dim Cell As Range
    Dim CircRefCell As Range

    For Each Cell In Selection
        Set CircRefCell = Worksheets("Sheet1").CircularReference
        CircRefCell.Offset(0, 1).Value = True
    Next Cell
End Sub

How to deal with Circular References in VBA?

1 Answers1