Letzte Versuch würde ich sagen!
Ohne Beispielmappe ist es immer ein Schuß ins Blaue!
Registrire dich bei Open Ai (ChatGpt) ,der Textmodus ist kostenfrei
Kopiere deine Excel Datei und füge sie im Chatfenster ein zur Analyse!
Dann schreibe
Strenge dich besonders an,Prüfe die Excel Mappe auf Zirkelbezüge ,nur bei echten Formeln keine anderen Zellen
Der letzte Versuch
Hi Revanchist — Challenge accepted.
Dein Wunsch: bitte nur echte Zirkelbezüge in Formelzellen melden (keine leeren Zellen, keine Konstanten, keine “Verdachtsfälle”). Unten bekommst du genau das — plus kurze Erklärungen, warum Excel dir die Adresse manchmal nicht zeigt.
Was hier anders ist (kurz)
-
Kein .DirectPrecedents-Hack mehr (der liefert schnell Fehlalarme, v. a. bei bereichs-/blattübergreifenden Bezügen). Microsoft sagt selbst, dass .DirectPrecedents nur auf dem aktiven Blatt funktioniert und keine Remote-Bezüge verfolgen kann. (Microsoft Learn)
-
Wir fragen Excel selbst: pro Durchlauf holen wir pro Blatt die vom Excel-Rechenkern gemeldete erste Zirkelleiche (Worksheet.CircularReference). Das ist garantiert ein echter Zirkel, sonst gäbe es keinen Treffer. (Microsoft Learn)
-
Excel listet im Menü “Zirkelbezüge” nur Treffer des aktiven Blatts; liegen Zirkeln auf anderen Blättern, steht unten links nur “Zirkelbezüge” ohne Adresse – genau dein Symptom. (Microsoft Support)
Makro: Nur echte Zirkel in Formeln finden (mit Hyperlinks)
-
meldet ausschließlich Formelzellen, die Excel als zirkulär erkennt
-
keine leeren Zellen, keine Konstanten
-
zeigt jedes Vorkommen einmal, mit Link zur Zelle
-
arbeitet blattübergreifend, auch wenn Zyklen mehrere Blätter betreffen
Bitte vor dem ersten Lauf eine Sicherungskopie machen. Das Makro neutralisiert gefundene Formeln kurzzeitig (als Text), um alle Zyklen zu enumerieren, und stellt sie am Ende zuverlässig wieder her.
Option Explicit
Public Sub ZirkelNurEchte_Protokoll()
Dim rep As Worksheet, ws As Worksheet, circ As Range
Dim row As Long, visState As XlSheetVisibility
Dim savedCalc As XlCalculation, savedScreen As Boolean
Dim savedAlerts As Boolean, savedEvents As Boolean
' Sammler für temporär neutralisierte Formeln (für spätere Wiederherstellung)
Dim keys As Collection, forms As Collection
Dim found As Boolean, k As String
Dim i As Long, sh As Worksheet, addr As String, p As Long
On Error GoTo CleanFail
Set keys = New Collection
Set forms = New Collection
' Environment sichern & beschleunigen
savedCalc = Application.Calculation
savedScreen = Application.ScreenUpdating
savedAlerts = Application.DisplayAlerts
savedEvents = Application.EnableEvents
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.StatusBar = "Zirkelanalyse läuft …"
' Altes Protokoll löschen, neues anlegen
On Error Resume Next
ThisWorkbook.Worksheets("Zirkelanalyse").Delete
On Error GoTo 0
Set rep = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
rep.Name = "Zirkelanalyse"
rep.Range("A1:E1").Value = Array("Blatt", "Zelle", "Formel", "Hinweis", "Link")
rep.Rows(1).Font.Bold = True
row = 2
' So lange wiederholen, bis es in keiner Tabelle mehr eine gemeldete CircularReference gibt
Do
found = False
For Each ws In ThisWorkbook.Worksheets
visState = ws.Visible
ws.Visible = xlSheetVisible ' sicherstellen, dass wir rankommen
Set circ = Nothing
Set circ = ws.CircularReference ' Excel-eigene, echte Zirkelmeldung
If Not circ Is Nothing Then
If circ.HasFormula Then
' Protokollzeile
rep.Cells(row, 1).Value = ws.Name
rep.Cells(row, 2).Value = circ.Address(False, False)
rep.Cells(row, 3).Value = "'" & circ.Formula
rep.Cells(row, 4).Value = "Echter Zirkel (vom Excel-Rechenkern gemeldet)"
rep.Hyperlinks.Add Anchor:=rep.Cells(row, 5), _
Address:="", SubAddress:="'" & ws.Name & "'!" & circ.Address, _
TextToDisplay:="Zu Zelle"
row = row + 1
' Für Enumeration diesen Zirkelpunkt kurz neutralisieren
keys.Add ws.Name & "!" & circ.Address(0, 0)
forms.Add circ.Formula
circ.Formula = "'" & circ.Formula ' Formel als Text ablegen
found = True
Else
' sehr selten: Zirkel gemeldet, aber keine Formel (z. B. Name)
rep.Cells(row, 1).Value = ws.Name
rep.Cells(row, 2).Value = circ.Address(False, False)
rep.Cells(row, 3).Value = ""
rep.Cells(row, 4).Value = "Zirkel gemeldet, aber keine Formel in der Zelle"
rep.Hyperlinks.Add Anchor:=rep.Cells(row, 5), Address:="", _
SubAddress:="'" & ws.Name & "'!" & circ.Address, TextToDisplay:="Zu Zelle"
row = row + 1
' Nichts neutralisieren
found = True
End If
End If
ws.Visible = visState
Next ws
' nächste Runde, solange im Durchlauf noch etwas gefunden wurde
Loop While found
' Alle neutralisierten Formeln exakt wiederherstellen
For i = 1 To keys.Count
p = InStr(1, keys(i), "!")
Set sh = ThisWorkbook.Worksheets(Left$(keys(i), p - 1))
addr = Mid$(keys(i), p + 1)
sh.Range(addr).Formula = forms(i)
Next i
rep.Columns.AutoFit
Application.StatusBar = False
MsgBox "Fertig. Echte Zirkel stehen im Blatt 'Zirkelanalyse'.", vbInformation
CleanExit:
Application.Calculation = savedCalc
Application.ScreenUpdating = savedScreen
Application.DisplayAlerts = savedAlerts
Application.EnableEvents = savedEvents
Exit Sub
CleanFail:
MsgBox "Analyse abgebrochen: " & Err.Description, vbExclamation
Resume CleanExit
End Sub
Warum das die Fehlalarme eliminiert
-
Quelle ist Excel selbst: Worksheet.CircularReference kommt direkt vom Rechenkern – kein Heuristik-Rauschen. (Microsoft Learn)
-
Nur Formelzellen: wir prüfen HasFormula, leere Zellen/Konstanten landen nicht im Report.
-
Blattübergreifend: Wir iterieren über alle Blätter und wiederholen, bis Excel nirgendwo mehr einen Zirkel meldet (damit erwischt man auch Zyklen über mehrere Blätter, die sonst die Statusleiste ohne Adresse triggern). Dass Excel Adressen nur fürs aktive Blatt zeigt, ist leider by design. (Microsoft Support)
-
Die alte Methode mit .DirectPrecedents scheitert bei Remote-Bezügen und hat dich in die Irre geführt. (Microsoft Learn)
Optional: Popup-Bremse ohne Makro-Flut
Wenn dich die Zirkel-Warnung beim Öffnen nervt, kannst du (gerade bei bewusst tolerierten Zirkeln) iterative Berechnung aktivieren – Excel rechnet dann, ohne ständig zu meckern.
Datei → Optionen → Formeln → „Iterative Berechnung aktivieren“ (z. B. Max. Iterationen = 1, Max. Änderung = 1E-7). (Microsoft Support, Microsoft Learn)
Hinweis: Das unterdrückt die Warnung, bricht aber keine Zyklen. Für die Analyse nutze weiter das Makro oben.
Wenn du magst, baue ich dir zusätzlich noch einen kleinen Helfer, der für die markierte Zelle die komplette Zirkel-Schleife (alle beteiligten Zellen) verfolgt und farbig markiert. Sag Bescheid – aber probier erst dieses “nur echte Zirkel”-Protokoll.
