Circular reference in a formula

[krishnak]

I have a worksheet range which has many formulas. I want to run a macro to check whether the formula in the each cell has a circular reference.
When we check for formula errors from the menu, the Circular Reference will show up only if the cell formula has such an error. Instead of checking each cel manually, I want to run a macro.
I could not find any reference to Circular Reference errors in the properties and methods. Am I missing something?
Any help is appreciated.

- Krishna

[mdmackillop]

From VBA Help

CircularReference Property


Returns a Range object that represents the range containing the first circular reference on the sheet, or returns Nothing if there's no circular reference on the sheet. The circular reference must be removed before calculation can proceed. Read-only.

CircularReference Property Example
This example selects the first cell in the first circular reference on Sheet1.
[vba]Worksheets("Sheet1").CircularReference.Select[/vba]

[krishnak]

Thank you, mdmackillop. That worked well in ensuring that there are no circular references in the formulas for the worksheet.

- Krishna

[mdmackillop]

Happy to help. Remember to mark your threads Solved

[LarryG]

Seriously old thread, but, hey, if I landed here googling the topic in 2019, maybe others will and may benefit from my additions Anyway, I discovered a pair of peculiarities (dare I say bugs?) on this.
- Make a circular reference on sheet 1.
- Go to sheet 2 (clean unused sheet)
- Run (or best, step through) the following, an expansion of the code posted earlier. Note code comments indicating problems.

Option Explicit

Sub Macro1()
    Dim sht As Worksheet
    On Error GoTo foo
    
    Worksheets("Sheet1").CircularReference.Select 'error if you are on clean sheet2
    
    Set sht = Worksheets("Sheet1")
    Debug.Print Worksheets("Sheet1").CircularReference Is Nothing 'fine
    Debug.Print Worksheets(sht.Name).CircularReference Is Nothing 'fine
    If Not (sht.CircularReference) Is Nothing Then MsgBox "hi"  'ERROR! You need to do next line instead!!
    If Not (Worksheets(sht.Name).CircularReference) Is Nothing Then MsgBox "ho" 'fine        
Exit Sub
foo:
    MsgBox Err.Number & ":" & Err.Description
    Resume Next
End Sub

I mean no offense pointing this out, as I have seen that you put out completely reliable posts, and a lot of them - thanks for that. (Really, of hundreds, I haven't found a flaw.) And, gee, all you did was recite official documentation, but unfortunately the rocket scientist developers at Microsoft appear to be unaware of this. But, hey, the just-do-enough-to-get-by testers have video games and surfing to do, you know

One more quirk, .CircularReference.count seems to always be 1 or nothing. But to see that, you better go
Worksheets(sht.name).CircularReference
and not
sht.CircularReference
or else you should brace for impact.

If anyone concludes it IS a bug, have fun reporting it. I've given up trying to get MS to respond to or even acknowledge even far more blatant errors. In fact, in this case, brace for "we're discontinuing VBA." (That would mess up the world quite rather severely, and hopefully they are conscious and realize that)