Forum Discussion

Immortalis's avatar
Immortalis
Brass Contributor
Sep 10, 2022

Circular Reference when opening.

 

When opening my excel file, I get a circular reference Warning.

 

I click "OK" and it finishes opening. I go to "Formulas/Error Checking" and circular references is greyed out indicating there are none.

 

I only get the warning when opening because it detected the circular reference before it finished loading all the data but when all the data is loaded, it solves the issue. 

 

This doesn't affect anything it's just a major annoyance. Is there any way to fix this without turning of error checking?

 

Thank you

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor

    Immortalis  wrote:  ``I only get the warning when opening because it detected the circular reference before it finished loading all the data but when all the data is loaded, it solves the issue. ``

     

    That is conjecture, and it is not a logical one.

     

    There are many other possibilities.  But you provide insufficient detail for a reasonable attempt to triage the problem.

     

    First, after observing that circ ref error checking is greyed out, what is the state of the Iterative Calculation option?

     

    Second, what happens if you open the file in "safe mode"?

     

    To accomplish that, press and hold Ctrl, and continue pressing Ctrl while you click on an Excel program icon (not a file icon).  Continue pressing Ctrl until you see the desktop pop-up to confirm "safe mode".  Confirm "safe mode".  When Excel opens, you should see "safe mode" in the window title.

     

    In that "safe mode" instance of Excel, open the file.

     

    Questions:

    (a) Do you get the circular reference error?

    (b) Is the circ ref error checking option still greyed out?

    (c) If not, are you able to locate the circ ref?

     

    Finally, if none of that is productive, can you attach an example Excel file that demonstrates the problem?

     

    Click the "browse" link at the bottom of the reply applet.  If the forum does not permit that (yet), upload the file to a file-sharing website, and post the download URL in a response here.  I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.  If the form does not permit you to paste the URL (yet), manually edit it so it does not look like a URL.  For example, the "URL" for this thred is techcommunity dot microsoft dot com /t5/excel/circular-reference-when-opening/m-p/3622888 .

     

    Of course, you should redact the file (i.e. remove revealing private information like names and addresses).  And ideally, reduce the file to a minimum, as best as reasonably possible.

     

    If none of that pans out, unfortunately I have only scratched the surface of arm's-length questions that we could ask.

     

    • Immortalis's avatar
      Immortalis
      Brass Contributor

      JoeUser2004 

       

      Thank you for responding.

       

      Iterative Calculation is not enabled.

       

      In Safe mode, the warning still appears and Circular References option is still greyed out.

       

      I will attach the file.

       

      Thank you very much.

      • JoeUser2004's avatar
        JoeUser2004
        Bronze Contributor

        Immortalis 

         

        Thanks for the file.

         

        I'm afraid that this might get very complicated. I hope nothing is lost in the confusion.

         

        Caveat: I use Excel 2010. So some of the behavior that I see might differ due to opening a file with a different version. Also, the navigation to some features might differ.

         

        -----

         

        I __am__ able to duplicate the unexpected behavior that you describe.

         

        That is, when I open your file and enable macros, I get a circ ref warning, but circ ref error checking is greyed out, suggesting that there is no circ ref.

         

        However, when I open your file and disable macros, circ ref error checking identifies Aug!K36 and Aug!AQ36.

         

         

        Also note the circ ref cell identified in the lower left of the status bar: AM156 in the Sep worksheet.

         

         

        I don't know why that does not appear in the circ ref error checking list. Perhaps it is part of the chain of calculations that "starts with" either Aug!K36 or Aug!AQ36.

         

        In fact, if you look at each worksheet, you will see additional "local" circ refs in the lower left of the status bar.

         

        But beware of bogus local "circ refs". For example, BaseInfo!B3 contains the formula =TODAY(). Of course, that cannot be a circ ref.

         

        I suspect that B3 is simply the last cell that Excel calculated on that worksheet when it decided that there might be a circular chain of calculations.

         

        My guess is.... That is simply based on an arbitrary number of calculated cells in the chain.

         

        -----

         

        To disable macros, I set an option in Trust Center > Trust Center Settings > Macro Settings. I always choose "disable...with notification".

         

        Then, after loading, I click "x" on the right of the notification instead of clicking "Enable" on the left.

         

         

        -----

         

        Explanation (speculation)....

         

        I note that the file was saved with Automatic Calculation enable, and there are several "volatile" VBA functions.

         

        My guess is.... Initially, the file has circ refs, and that is what Excel reports.

         

        But after recalculation due to the volatile functions when the file is opened, conditions change such that the circ refs are "masked".

         

        To explain.... Consider the following formula in A1 in a new workbook:
        =IF(B1, A1+1, 0).

         

        Initially, there is no circ ref error and A1 displays 0 because B1 (empty) is interpreted as FALSE (0), so the value-if-true expression (A1+1) is ignored.

         

        But when we enter TRUE into B1, then we get a circ ref because the circ ref A1+1 is "unmasked" by the change in the logic.

         

        You have many complicated IF formulas, and the volatile VBA functions might alter their behavior.

         

        -----

         

        That is as far as I can take this, because of the unduly complex nature of the IF formulas.

         

        Hopefully, your intimate knowledge of what you are trying to do will help you isolate the circ refs.

         

        Unfortunately, I don't believe that Excel provides much assistance.

         

        You might try using Evaluate Formula (not available in Excel for Mac). But beware that sometimes EF does not exactly mimic normal formula evaluation.

         

        Good luck!

         

        -----

         

        BTW, I believe you have a defect in one VBA function -- and perhaps that is causing the unexpected change in circ ref conditions.

         

        Function MyDatePS(RCell As Range)
        MyDate = Date
        End Function

         

        should be

         

        Function MyDatePS(RCell As Range)
        MyDatePS = Date
        End Function

         

        You should use Option Explicit at the top of each VBA module. That might expose such typos.

         

Resources