Circular reference warning without circular reference

Question

Hi,

I have a problem with circular reference warning. When I open a file, I receive a message about circular reference. However, when I look in Formulas > Error Checking > Circular References, none is shown (the option is disabled). Please, how can I solve this problem? I am using macros in the worksheet, however, I receive the message even if I open the excel file in secure mode.

Thanks,

Lukas

Answer 1

Lukas wrote:

I have a problem with circular reference warning. When I open a file, I receive a message about circular reference. However, when I look in Formulas > Error Checking > Circular References, none is shown (the option is disabled). Please, how can I solve this problem? I am using macros in the worksheet, however, I receive the message even if I open the excel file in secure mode.

Hmm, I wonder if this is due to what Bill Manville calls a defect in the a previous discussion (click here).  A couple things you might try, if only to isolate the problem.

1. Start Excel in "safe mode".  Press and hold the Ctrl key while you click (or double-click) on the Excel icon, not an Excel file icon.  Continue holding the Ctrl key down until Excel prompts you about whether or not you want to continue in safe mode (click Yes).  Then open your Excel file in Excel (click File, then Open).

     Caveat:  Your Excel file might not open correctly in this mode.  Be sure you do not save it, even if prompted that it has changed.  This experiment is just for diagnostic purposes.

     If the problem goes away, the circular reference might be created by something in the personal.xls(b) file.  You need to examine it.

     If the problem persists, exit this instance of Excel (without saving your file).  Continue with step #2.

2. Open your file normally.  Search for TODAY and NOW.  If you use those functions, this might be the defect that Bill theorizes.  If so, you might try replacing TODAY and NOW with myToday and myNow, which you implement in a VBA module as follows.

Function myToday()

Application.Volatile

myToday = Date

End Function

Function myNow()

Application.Volatile

myNow = Now

End Function

     "Save As" to a temporary file name.  Exit this instance of Excel, then re-open the temporary Excel file.

     If the problem goes away, that would seem to confirm Bill's theory.  You might want to replace your original file with this temporary file.

     If the problem persists, I'm afraid I am out of ideas.  You might try the radical step of reinstalling Office 2010.  But that's a "hail Mary" play.  That is, there is no reason to expect that to work.  It might not be worth the trouble.

Answer 2

Hi,

You may want to try Stephen Bullen's utility:

http://www.oaltd.co.uk/Excel/Default.htm

Look for FindCirc.zip