EXCEL for MAC: Circular Reference not identified

[KerryFSA]

Get a message about a circular ref.when opening WM. Can't find the problem cell(s). Error Checking does not identify it; status bar indicates "Circular Reference" on each sheet but doesn't i.d. the cell. Can't find the error by inspection. Ideas? Please note: Excel for Mac (up to date). Thanks very much.

 

[Yongle]

Re: EXCEL for MAC: Circular Refertence not identified

Windows
this lists 1st cell in each sheet where it (or one of its precedents) contains a circular reference
Test to see if this works for Mac

Message box returns a list of sheets and first-found circular reference (if any):

Sheet1 B1
Sheet2 none
Sheet3 none
Sheet4 C4
Sheet5 none

Mac
I have no way to check if this works for Mac
- before relying on it, force a circular reference to see if it gets picked it up

Sub Circular()
    Dim Circ As Range, Addr As String, Msg As String, Ws As Worksheet
    
    For Each Ws In ThisWorkbook.Worksheets
        Addr = "none"
        On Error Resume Next
        Addr = Ws.CircularReference.Address(0, 0)
        Msg = Msg & vbCr & Ws.Name & vbTab & Addr
    Next
    MsgBox Msg
End Sub

 

[KerryFSA]

Re: EXCEL for MAC: Circular Refertence not identified

Thanks, Yongle. A test cell was identified properly. The Sub also identified 4 other references: all formulas with the cell = NOW().
Does that make sense? Could another cell be using the NOW() cell in some way that creates the circle?

 

[Yongle]

Re: EXCEL for MAC: Circular Refertence not identified

the mystery deepens
=NOW() is not circular

Are you saying that ...
4 cells were identified in 4 different sheets ,and all 4 contain the simple formula ....
=NOW()

 

[Yongle]

Re: EXCEL for MAC: Circular Refertence not identified

Thanks to google, I may have found something highly relevant
- the link specifically mentions NOW() causing Circular reference problems
https://www.ablebits.com/office-addins-blog/2015/12/15/circular-reference-excel/

Hopefully, it explains what you are experiencing

 

[KerryFSA]

Re: EXCEL for MAC: Circular Refertence not identified

Thanks for the link. Sorry, but it doesn't address my issue. In my WB, other cells are not referring to the NOW function; nor am I combining the NOW() with any other terms or references in the offending cells.

 

[Yongle]

Re: EXCEL for MAC: Circular Refertence not identified

Perhaps this will help you get further...

It lists all cells in all sheets with formula containing Now() and any dependents in the same sheet

Sub LookingForNow()
[I][COLOR=#006400]'variables[/COLOR][/I]
    Dim Sh As Worksheet, ws As Worksheet, Loc As Range, Addr1 As String, Dep As String, Addr As String, Formul As String
[COLOR=#006400][I]'results sheet[/I][/COLOR]
    Set ws = Sheets.Add(before:=Sheets(1))
    ws.Name = "Results " & Round(Timer, 0)
    With ws.Range("A1:D1")
        .ColumnWidth = 25
        .Value = Split("Sheet,Ref,Formula,Dependents", ",")
        .Font.Bold = True
    End With
[I][COLOR=#006400]'loop sheets looking for "Now()"[/COLOR][/I]
    For Each Sh In ThisWorkbook.Worksheets
        On Error Resume Next
        With Sh.UsedRange.SpecialCells(xlCellTypeFormulas)
            Set Loc = .Cells.Find(What:="NOW()", LookAt:=xlPart)
            On Error GoTo 0
            On Error Resume Next
    
            If Not Loc Is Nothing Then
                Addr1 = Loc.Address
                Do
                    Dep = Loc.Dependents.Address
                    Addr = Loc.Address(0, 0)
                    Formul = Replace(Loc.Formula, "=", "")
                  [I][COLOR=#006400]  'write results[/COLOR][/I]
                    ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 4) = Array(Sh.Name, Addr, Formul, Dep)
                    Dep = ""
                    Set Loc = .FindNext(Loc)
                Loop While Not Loc Is Nothing And Loc.Address <> Addr1
            End If
            
        End With
        Set Loc = Nothing
    Next Sh

End Sub

 

[KerryFSA]

Re: EXCEL for MAC: Circular Refertence not identified

Thanks very much for your latest post. The Sub was very useful in identifying the dependents. Although I couldn't see the circular dependency, I disabled the dependent cells but still got the circular message on opening the WB. Then I disabled all the NOW() formulas, one at a time. It wasn't until I had disabled all NOW(()s that the message disappeared on opening the WB. Then I entered a simple NOW() without any custom formatting and the WB behaves properly. Do you think customizing the NOW() cells would create a problem? It certainly shouldn't.

I really appreciate all the time you have put into this thread. Thank you, Kerry

 

[Yongle]

Re: EXCEL for MAC: Circular Refertence not identified

Do you think customizing the NOW() cells would create a problem? It certainly shouldn't

- I cannot replicate your problem so it is probably a Mac "feature"
Glad you got it sorted


For future benefit of others can you quickly test the following volatile functions with custom format and see if the same thing happens
- please report back
thanks

I would not be surprised if these do the same thing:
RAND()
TODAY()

Who can guess how these might behave?
OFFSET()
CELL()
INDIRECT()
INFO()

 

[KerryFSA]

Re: EXCEL for MAC: Circular Refertence not identified

RAND and TODAY work fine, as does the simple NOW I mentioned. Am restoring NOW related cells one at a time, opening and closing each time. No problem so far.