Excel hat Fehler in Formelbezug gefunden zeigt ihn aber nicht an

[Daniel Albert]

Hallo,

beim öffnen meiner Excel Arbeitsmappe erhalte ich jetzt ständig folgende Fehlermeldung:

   

Verbindungen zu anderen Mappen habe ich keine und unter dem Namensmanager konnte ich auch keine Fehler finden. Wie kann ich mir denn den Fehler welchen Excel entdeckt hat anzeigen lassen?

Gruß Daniel

[WillWissen]

Hi Daniel,

benutze mal das Menü "Formeln"==> Spur zum...  Vllt. findest du so den Fehler.

[Daniel Albert]

Hallo, ja das kenne ich schon aber da muss man ja jede Zelle einzeln anklicken:

   

Verstehe nicht warum keine Info kommt wo Excel den Fehler erkannt hat. Jetzt muss ich in allen Arbeitsblättern einzeln nachschauen

[Gast 123]

Hallo Daniel Albert

ein klare Nein dank Hajo aus dem Herber Forum, da fand ich im Archiv ein Makro um Verknüpfungen auzuspüren die sich auf Formeln beziehen.  Es gibt noch andere Möglichkeiten, Vernüpfungen in Objekten zu anderen Mappen, wenn man von dort ein Objekt kopiert hat.  Sofern es eine Formel ist hilft dir dieser Code.  Sonst sage bitte Bescheid, für Verknüpfungen in Objekten und Workbook Namen habe ich auch noch einen Code parat.

mfg  Gast 123 

Code:

Option Explicit       'Verknüpfung Hajo


Sub Verknuepfte_Zellen()
'**************************************************
'* H. Ziplies                                     *
'* 24.08.08                                       *
'* erstellt von HajoZiplies@web.de                *
'* http://Hajo-Excel.de/                          *
'**************************************************
   On Error GoTo Fehler1                           ' Fehlerbehandlung ausschalten
   Dim RaZelle As Range                            ' Variable für aktuelle Zelle
   Dim ByMldg As Byte                              ' Variable Meldung
   Dim WsSh As Worksheet                           ' Variable Tabelle
   Dim ObZelle As Object                           ' Variable für Namen
'GoTo weiter:
   For Each WsSh In Worksheets                     ' Schleife über alle Tabellen der Datei
       ' Prüfen ob Tabelle schon vorhanden
       If InStr(WsSh.Name, "Verknüpfung") > 0 Then
           ByMldg = MsgBox("Eine Tabelle mit dem Namen " _
               & "Verknüfungen ist schon vorhanden, sollen die " _
               & "Daten gelöscht werden", vbYesNo + vbQuestion, _
               "Löschabfrage ?", "", 0)
           If ByMldg = 6 Then              ' Ja wurde gedrückt
               ' Zellen komplett löschen,
               ' da schon bestimmte Formate eingestellt
               WsSh.Cells.Delete
               ' Kennzeichnen dass Tabelle schon vorhanden
               ByMldg = 45
               ' Schleife verlasse, da Tabelle gefunden
               Exit For
           Else
               Exit Sub
           End If
       End If
   Next WsSh
weiter:
   Application.ScreenUpdating = False              ' Bildschirmaktualisierung ausschalten
   Application.EnableEvents = False                ' Reaktion Eingabe abschalten
   If ByMldg <> 45 Then                            ' Tabelle anlegen falls noch nicht vorhanden
       ' Anlegen hinter der letzten Tabelle ubnd Namen geben
       Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Verknüpfung"
       With ActiveWindow
           .SplitRow = 2
           .FreezePanes = True
       End With
   End If
   With Worksheets("Verknüpfung")
       ' Überschriftszeilen
       ' Formel mit Ergebnis Fehler
       .Cells(1, 1) = "Formel mit Ergebnis Fehler"
       .Cells(2, 1) = "Zelle"
       .Cells(2, 2) = "Tabelle"
       .Cells(2, 3) = "Formel"
       ' Formel zu anderen Arbeitsmappe
       .Cells(1, 5) = "Formel zu anderen Arbeitsmappe"
       .Cells(2, 5) = "Zelle"
       .Cells(2, 6) = "Tabelle"
       .Cells(2, 7) = "Formel"
       ' Formel zu anderen Tabellen in dieser Arbeitsmappe
       .Cells(1, 9) = "andere Tabelle"
       .Cells(2, 9) = "Zelle"
       .Cells(2, 10) = "Tabelle"
       .Cells(2, 11) = "Formel"
       ' restliche Formel
       .Cells(1, 13) = "Rest"
       .Cells(2, 13) = "Zelle"
       .Cells(2, 14) = "Tabelle"
       .Cells(2, 15) = "Formel"
       ' definierte Namen in dieser Arbeitsmappe
       .Cells(1, 17) = "definierte Namen"
       .Cells(2, 17) = "Name"
       .Cells(2, 18) = "Zelle"
       .Cells(2, 19) = "Tabelle"
       Rows("1:2").Font.Bold = True
       For Each WsSh In Worksheets                 ' Schleife über alle Tabellen
           If WsSh.Name <> "Verknüpfung" Then
               ' Schutz aufheben falls vorhanden
               ' WsSh.Unprotect "Passwort"
               On Error Resume Next
               Set RaZelle = WsSh.UsedRange.SpecialCells(xlCellTypeFormulas)
               Set RaZelle = Nothing
               If Err.Number = 0 Then
                   On Error GoTo 0
                   ' Schleife über den benuzten Bereich mit Formel
                   For Each RaZelle In WsSh.UsedRange.SpecialCells(xlCellTypeFormulas)
                       ' Formeln mit Fehler
                       If IsError(RaZelle.Value) Then
                           .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1) _
                               = RaZelle.Address(0, 0)
                           .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 2) _
                               = CStr(WsSh.Name)
                           .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 3) _
                               = "'" & RaZelle.FormulaLocal
                       ' Formel zu anderer Arbeitsmappe
                       ElseIf InStr(RaZelle.Formula, ":\") <> 0 Then
                           .Cells(.Cells(.Rows.Count, 5).End(xlUp).Row + 1, 5) _
                               = RaZelle.Address(0, 0)
                           .Cells(.Cells(.Rows.Count, 5).End(xlUp).Row, 6) _
                               = CStr(WsSh.Name)
                           .Cells(.Cells(.Rows.Count, 5).End(xlUp).Row, 7) _
                               = "'" & RaZelle.FormulaLocal
                       ' Formel zu andere Tabelle
                       ElseIf InStr(RaZelle.Formula, "!") > 1 Then
                           .Cells(.Cells(.Rows.Count, 9).End(xlUp).Row + 1, 9) _
                               = RaZelle.Address(0, 0)
                           .Cells(.Cells(.Rows.Count, 9).End(xlUp).Row, 10) _
                               = CStr(WsSh.Name)
                           .Cells(.Cells(.Rows.Count, 9).End(xlUp).Row, 11) _
                               = "'" & RaZelle.FormulaLocal
                        Else               ' restliche Formeln
                           .Cells(.Cells(.Rows.Count, 13).End(xlUp).Row + 1, 13) _
                               = RaZelle.Address(0, 0)
                           .Cells(.Cells(.Rows.Count, 13).End(xlUp).Row, 14) _
                               = CStr(WsSh.Name)
                           .Cells(.Cells(.Rows.Count, 13).End(xlUp).Row, 15) _
                               = "'" & RaZelle.FormulaLocal
                       End If
                   Next RaZelle
               End If
               On Error GoTo 0                     ' Fehlerbehandlung einschalten
           End If
           ' WsSh.Protect "Passwort"               ' Schutz wieder setzen
       Next WsSh
       ' Programmteil Namen auslesen
       ' Schleife über alle Namen der Datei
       For Each ObZelle In ActiveWorkbook.Names
           .Cells(.Cells(.Rows.Count, 17).End(xlUp).Row + 1, 17) _
               = ObZelle.Name
           With .Cells(.Cells(.Rows.Count, 17).End(xlUp).Row, 18)
               If InStr(ObZelle, "REF") <> 0 Then
                   .Value = Mid(ObZelle, InStr(ObZelle, "!") + 1)
                   .Font.Bold = True
                   .Font.ColorIndex = 3
               ElseIf InStr(ObZelle, "\") <> 0 Then
                   .Value = Mid(ObZelle, InStr(ObZelle, "!") + 1)
                   .Font.Bold = True
                   .Font.ColorIndex = 4
               Else
                   .Value = Mid(ObZelle, InStr(ObZelle, "!") + 1)
               End If
           End With
           If InStr(ObZelle.RefersTo, "!") > 0 Then
               .Cells(.Cells(.Rows.Count, 17).End(xlUp).Row, 19) _
                   = Application.WorksheetFunction.Substitute(Mid(ObZelle, _
                   2, InStr(ObZelle, "!") - 2), "'", "")
           Else
               .Cells(.Cells(.Rows.Count, 17).End(xlUp).Row, 19) _
                   = ObZelle.RefersTo
           End If
       Next
       .Range("B:C,F:G,J:K,N:O, R:S").EntireColumn.AutoFit
       ' Überschriftszeilen
       ' Formel mit Ergebnis Fehler
       .Cells(1, "A") = "Zellen mit Ergebnis Error"
       ' Formel zu anderen Arbeitsmappe
       .Cells(1, "E") = "Formeln zu anderen Arbeitsmappen"
       ' Formel zu anderen Tabellen in dieser Arbeitsmappe
       .Cells(1, "I") = "Formeln zu anderen Tabellen"
       ' restliche Formel
       .Cells(1, "M") = "restliche Formeln"
       ' definierte Namen in dieser Arbeitsmappe
       .Cells(1, "O") = "Namen in dieser Arbeitsmappe"
   End With
Fehler1:
   On Error GoTo 0                                 ' Fehlerbehandlung eimschalten
   If Err <> 0 Then MsgBox "Es ist ein Fehler aufgetreten!"
   Application.ScreenUpdating = True               ' Bildschirmaktualisierung einschalten
   Application.EnableEvents = True                 ' Reaktion Eingabe einschalten
End Sub

[Daniel Albert]

Servus, der Code hat jetzt einige Fehler gefunden aber ich erhalte beim Speichern trotzdem noch eine Fehlermeldung zu einen falschen Formelbezug.

[Gast 123]

Hallo Danie Albert

freut mich das der Code von Hajo einige Fehler aufspüren konnte, machen wir weiter mit meinem Code aus meinem Archiv.

Der listet Formeln und Formel Fehler als Anzahl mit Adresse auf, damit man sofort prüfen kann wo der Fehler steckt.  Das 2. Makro listet die Workbook Names auf, da können auch ungültige Referenz Adressen drin sein.  Die gemeine Tücke:  diese ungültigen Referenzen kannst du im Workbook Names Dialog von MS nicht sehen.  Da werden die NICHT angezeigt.  Mein Makro spürt sie aber auf!!
 
mfg  Gast 123

Code:

Sub Formelbezug_finden()
Dim UseRng As String, FmlAdr As String
Dim ErrAdr As String, FmZrk As String
Dim Formel As Long, FmlErr As Long

Worksheets("Test").Select
Range("A2:Z200").ClearContents
z = 2  '1.Zeile zum auflisten

On Error Resume Next
For k = 1 To Worksheets.Count
With Worksheets(k)
  UseRng = "":  FmlAdr = "":  ErrAdr = ""
  FmZrk = "Nein":  Formel = 0:  FmlErr = 0
 
  UseRng = .UsedRange.Address(False, False)
  Formel = .Cells.SpecialCells(xlFormulas).Count
  FmlErr = .Cells.SpecialCells(xlFormulas, 16).Count
 
  FmZrk = .CircularReference.Address(False, False)
  FmlAdr = .Cells.SpecialCells(xlFormulas).Address(0, 0)
  ErrAdr = .Cells.SpecialCells(xlFormulas, 16).Address(0, 0)
 
  Cells(z + 1, 2) = .Name
  Cells(z + 2, 2) = "UsedRange"
  Cells(z + 3, 2) = "Formeln"
  Cells(z + 4, 2) = "Formel Err"
  Cells(z + 5, 2) = "Zirkelbezug"
 
  Cells(z + 2, 3) = UseRng
  Cells(z + 3, 3) = Formel
  Cells(z + 4, 3) = FmlErr
  Cells(z + 5, 3) = FmZrk
 
  Cells(z + 3, 4) = FmlAdr
  Cells(z + 4, 4) = ErrAdr
  z = z + 6

End With
Next k
End Sub



Sub Namen_auflisten()
Dim Txt As String, j As Integer, r As Long
Worksheets("test").Select

  On Error Resume Next
  Range("A2:F500") = Empty
 
With ThisWorkbook
  If .Names.Count = 0 Then MsgBox " -keine- Wb Namen in Datei": Exit Sub
  For j = 1 To .Names.Count
      Cells(j + 1, 2) = j
      Cells(j + 1, 3) = .Names(j).Name
      Cells(j + 1, 4) = " ' " & .Names(j).RefersToLocal
      If InStr(Cells(j + 1, 4), "REF") Then r = r + 1
  Next j
  If r > 0 Then MsgBox r & "  REF Fehler in Names - (ungültige Referenz)"
End With
End Sub

[Daniel Albert]

Danke, hat scheinbar funktioniert. Teste es die Tage mal aus