none
Checking for Circular References using Excel Interop in .Net RRS feed

  • Question

  • We have a .Net application, and are using Microsoft.Office.Interop.Excel to programatically open an XLSX file and check for Circular References in it.

    However, we have found that it yields the same results as what is shown on the bottom-left in Excel's Status bar; it shows ALL cells affected by and causing the circular reference.

    I have found that the list Excel displays in the 'Error Checking' menu is a more accurate report of the circular referewnce; is there any way to retrieve the results that are displayed in there instead?

    Thursday, March 23, 2017 1:02 AM

All replies

  • Hello,

    After checking object models, Excel object models only support using Worksheet.CircularReference Property (Excel) to return the range containing the first circular reference on the sheet. So I think there is no way to retrieve your expected result.

    In fact, I failed to reproduce your issue. Even there are several circular references in the workbook, there is only one showing in the status bar and the Error Checking list. And the value on status bar and list are the same.

    To fix your issue, I suggest you post on Excel IT Pro Discussions or https://answers.microsoft.com/en-us/msoffice to find the cause of this situation.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 23, 2017 6:37 AM
    Moderator
  • Hi Celeste

    It is very easy to replicate it:

    1. Create a workbook with 3 sheets (Sheet1. Sheet2, Sheet3)
    2. On Sheet1 cell A1, create a formula =Sheet2!A1
    3. On Sheet2 cell A1, create a formula =Sheet1!A1.........circular reference between those 2 cells!
    4. On Sheet2 cell A1, create a formula =Sheet2!A1.........not part of the circ reference loop, but referencing a cell that is
    5. When you then click through each tab, it will indicate in the Status that there is a Circ Reference in cell A1 of every sheet.
    6. If you however look at the Error Checking mene (Ribbon > Formulas > Formula Auditing > Error Checking > Circular References), you can see it is only listed the cells on Sheet1 and Sheet2 as being in the circ reference loop, which is more accurate, and the information i am after.
    Friday, March 24, 2017 2:51 AM
  • Hello Estate,

    Thanks for the sharing and I think we could find solution for your issue.

    We could use  Worksheet.CircularReference Property (Excel)  to loop all sheets, get the range object and return the cell formula.

    You could test the code below. You could get the list from the new array aryN(). 

    Dim ary() As String
    Dim aryN() As String
    For Each ws In ActiveWorkbook.Sheets
    ReDim Preserve ary(i)
    ary(i) = ws.CircularReference.Formula
    i = i + 1
    Next
    ReDim aryN(0)
    aryN(0) = ary(0)
    For Each ele In ary()
        If UBound(Filter(aryN(), ele)) = -1 Then
            ReDim Preserve aryN(x)
            aryN(x) = ele
            x = x + 1
        End If
    Next

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, March 24, 2017 9:53 AM
    Moderator
  • Hi Celeste

    Thanks for that solution. It seems to work for my basic example, so I am going to implement it in some more complex scenarios and see if it still works.

    Anthony

    Monday, March 27, 2017 10:57 PM
  • Hi Celeste

    I have tested your sample code in other scenarios, and i don't think it works. I found that i gave you the wrong steps to follow (on step 4, i had incorrectly referred to setting a formula on Sheet 2 instead of Sheet3). I have actually revised the steps below 

    Try this:

    1. Create a workbook with 3 sheets (Sheet1. Sheet2, Sheet3)
    2. On Sheet1 cell A1, create a formula =Sheet2!A1+10+B1
    3. On Sheet2 cell A1, create a formula =Sheet1!A1/2+C1 ........circular reference between those 2 cells!
    4. On Sheet3 cell A1, create a formula =Sheet2!A1+A7 ........not part of the circ reference loop, but referencing a cell that is

    When i use your sample code, it returns two elements in the aryN array

    • aryN(0) "=Sheet1!A1/2+C1"  This is the formula entered on Sheet2, and is correctly part of the circular reference loop
    • aryN(1) "=Sheet2!A1+A7" This is the formula entered on Sheet3, as is NOT part of the circular reference loop, so i dont want this returned. It should have been the formula in Sheet1

    Can you think of any other alternative solution?

    Tuesday, April 4, 2017 11:35 PM
  • Hi Estate,

    >> in the circ reference loop, which is more accurate, and the information i am after

    Could you share us what do you want to do with this information in Circular References?

    In many cases, if you create additional formulas that contain circular references, Excel won’t display the warning message again. So, we are not sure which circular reference will display in “Circular Reference”.

    When there are multiple circular references, which circular reference did you want, and what do you want to do with this circular reference?

    If you insist on getting the values in "Circular References", I would suggest you share us your workbook, we will provide suggestion according the provided workbook. But, we would not guarantee it will work for other scenarios. 

    In addition, what do you mean by .Net application? Is it asp.net web application?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 6, 2017 8:51 AM
  • Hi,

    It can be done as my RefTreeAnalyser utility proves (free demo): www.jkp-ads.com/reftreeanalyser.asp


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Thursday, April 6, 2017 11:54 AM
  • Hi Edward

    • Our application is a .Net Winforms application.
    • Using Excel Interop, i want to my application to load the XLSX file and get a list of all circular references in the file.
    • Here is a sample of the code we currently use for this: https://www.screencast.com/t/wzmdxlxyoym
    • Using that process, it returns the same results as what is shown on the bottom-left in Excel's Status bar when the file is open in Excel; it shows ALL cells affected by AND causing the circular reference. See my screenshot in the original post.
    • I have also discovered that if there are any volatile functions in the workbook, even if they are no way linked to the circular reference, Excel still displays that cell reference in the status bar as a Circular Reference.
    • I have found that the list Excel displays in the 'Error Checking' menu is a more accurate report of the circular references; it only shows causing the circular reference. I want to know is there is a way for my application to retrieve the results that are displayed in that list.

    Here is a link to download a sample workbook: https://1drv.ms/x/s!ArreXfLnjdAquCorlwUZm4g3NiXT

    • The circular reference I created is only between 2 cells on Sheet1 and Sheet2
    • When you click on each worksheet tab, you will see that the status bar shows Circular Reference: <Cell Address> on every sheet.
    • On Sheet3 i just have a reference to one of these circ ref cells
    • On Sheet4 i just have an unrelated OFFSET formula
    • If you go to the ribbon menu, FORMULAS > Formula Auditing > Error Checking > Circular References, the list it shows in that drop down is the  2 cells on Sheet1 and Sheet2!!! This is the data i want my application to retrieve.

    Hopefully that helps.

    Friday, April 7, 2017 12:07 AM
  • Hi Jan

    Yes, i trialled your add-in, and it does exactly what i need.

    But the question is, how do i get my windows application to do the same?

    Friday, April 7, 2017 1:14 AM
  • Hi Estate,
     
    I'm afraid I can't share my code publicly (after all I am selling this tool and
    the method to find all circular references is my trade secret), but I am
    willing to work with you to find a solution. Please contact me if you'd like to
    discuss this via email. You can find my address at the bottom of each page of
    my website jkp-ads.com.
     
     
    Friday, April 7, 2017 10:04 AM