Apr 30 2020 07:30 AM
Apr 30 2020 07:30 AM
I've been getting reports of some strange Circular Reference errors in a workbook. Unfortunately I'm not allowed to share the workbook or any images of it, as it contains sensitive company data. The workbook is complex, containing over 4000 lines of VBA code and thousands of lengthy formulas. A copy of the workbook is completed every shift, it takes the information entered, sorts it, and exports the data to another workbook. Most of the code and formulas are used to make the data entry easier, and provide a visualization of the data.
The first instance of the strange error appeared in a copy of the workbook completed by one shift, none of the workbooks completed by other shifts contained the error. When the workbook was opened, excel displayed a warning that the sheet contained circular references. It listed the cell with the first circular reference in the bottom left, and error checking reported that there were circular references, but could not specify what cells they were in. When selecting a cell listed in the bottom left, error checking did not find anything wrong with the specific cell. I tried tracing the dependencies of the cells with errors, but couldn't find any circular references.
It gets weirder. None of the other copies of the workbook had the error, until you opened one of the good copies at the same time as the copy with the error. That copy would then start showing the same problems, even though nothing had changed from when it was error free. We made sure that everyone was using a fresh copy of the workbook each shift, and the problem wasn't re-encountered for several weeks.
This brings us to the second instance. Same problem, but only on one machine. This time it doesn't propagate from one open workbook to another, and the error doesn't occur when the workbook is opened on most computers. Only one machine that I know of produces the error when opening the workbook. All copies of the workbook are saved on a company server.
Does anyone have any insight into what the cause of this problem could be?
Apr 30 2020 07:37 AM - edited Apr 30 2020 07:39 AM
Jul 18 2021 04:12 PM
In my case, Excel lets me load my workbook, but with the message "Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you." Some worksheets have a general "circular reference" message, others point to apparently random cells. I've tried Error Checking in the Formula Auditing group, but in my case the Circular References is greyed out. I suspect it may be linked to my recent use of dynamic arrays.