PDA

View Full Version : [SOLVED] Circular reference in a formula



krishnak
04-05-2010, 01:22 PM
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
04-05-2010, 02:24 PM
From VBA Help

CircularReference Property


Returns a Range (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office\1033\vbaxl9.chm ::/html/xlobjRange.htm) 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.
Worksheets("Sheet1").CircularReference.Select

krishnak
04-06-2010, 08:34 AM
Thank you, mdmackillop. That worked well in ensuring that there are no circular references in the formulas for the worksheet.

- Krishna

mdmackillop
04-06-2010, 03:12 PM
Happy to help. Remember to mark your threads Solved

LarryG
09-14-2019, 08:14 PM
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)