If Workbook Contains a Circular Reference then

[bajlewis]

Hello,

I'm trying to get something like this to work (simplified for example, I know excel already warns me):

Sub CircTEST()

If ActiveWorkbook.CircularReferences.Count > 0 Then
    MsgBox ("Circ Ref")
 Else
    MsgBox ("NO Circ Ref")
  End If
End Sub

Didn't think it would be too difficult however I can't find the answer. The part that doesn't work is this.. "ActiveWorkbook.CircularReferences.Count > 0" it doesn't evaluate to boolean.

Thanks
-Bart

 

[Jonmo1]

The circularReference object is a member of the workSHEET group, not workBOOK..
And it reterns a RANGE object of the first (top left) cell with a circular.
It returns NOTHING if there are no circulars on the sheet

Try this

Sub test()
If ActiveSheet.CircularReference Is Nothing Then
    MsgBox "no circulars"
Else
    MsgBox "there is a circular in " & ActiveSheet.CircularReference.Address
End If
End Sub

 

[bajlewis]

Thanks for the quick reply! I suppose I can set a loop through the sheets in the book to check them all.



To further the challenge...

When on another sheet in the book (one that doesn't contain a circular reference) the Status Bar (lower left) displays a warning that there are circular references... I was wondering if there is a way to utilize that indicator to invoke a warning despite what sheet you are on. What part of the application generates this and how to I get at it?

 

[Jonmo1]

I am only aware of the worksheets("Sheetname").CircularReference Object..

To loop through the sheets, you could do something like

<font face=Error: Key or Value Not Found.><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ws.CircularReference <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "there is a circular in " & ws.Name & "!" & ws.CircularReference.Address<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Jonmo1]

I do have a correction to which cell is referred to by the circularreference object..
I originally said it would be the first (top left) cell with a circular.
But that's not correct

The verbage of the help files is

Returns a Range object that represents the range containing the first circular reference on the sheet

After testing, First actually means the first(chronologically) circular that was created.
Not necessarily first in Top/left order.

 

[bajlewis]

Very cool... thanks again.