Benannten Bereich auf Gültigkeit prüfen

Forumthread: Benannten Bereich auf Gültigkeit prüfen

06.12.2006 10:24:06

Arthur

Hallo zusammen.
Immer mal wieder bekomme ich eine Excel-Tabelle, um daran weiterzuarbeiten. Oftmals das Problem, dass benannte Bereiche existieren, die sich nicht nur innerhalb der Tabelle, sondern auch auf externe Quellen referenzieren. Darunter befinden sich aber jede Menge schwarze Schafe. Ein #REF oder #BEZUG im Text zu finden ist noch einfach. Wenn es sich dann um externe Bezüge handelt, ist es schon schwieriger. Dann kommen noch die Konstanten dazu ...
Ich möchte nun ein kleines Makro schreiben, das mir die benannten Bereiche auf ihre Gültigkeit hin prüft und bei Bedarf dann löscht.
Gibt es eine Möglichkeit wie z.B. BenannterBereich.BistDuGültig oder BenannterBereich.GibtEsDeinZiel? Oder 'Zelle, du referenzierst auf den bennanten Bereich, sag mir, ob dein Inhalt kein Fehler ist'?
Besten Dank im Voraus
-Arthur

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Find Link

06.12.2006 10:42:26

Beate
Frage ist noch offen

06.12.2006 10:51:25

Arthur
AW: Benannten Bereich auf Gültigkeit prüfen

06.12.2006 10:46:24

IngGi
Könnte klappen

06.12.2006 11:00:35

Arthur
AW: Könnte klappen

06.12.2006 12:46:48

IngGi
AW: Könnte klappen

06.12.2006 14:49:51

Arthur

Find Link

06.12.2006 10:42:26

Beate

Hallo Arthur,
http://www.bmsltd.co.uk/MVP/Default.htm
Schau dir dort FindLink.zip an.
Gruß,
Beate

Frage ist noch offen

06.12.2006 10:51:25

Arthur

Hallo Beate.
Danke für die Antwort. Leider hilft sie mir nicht weiter. Die letzte Tabelle enthielt über 1.000 benannte Bereiche mit Bezügen irgendwohin. Einzeln abzufragen ist da keine Lösung. Da hilft nur herauszufinden, welche davon in Ordnung sind und welche nicht; kurze Anzeige und weg damit.
Gruß
-Arthur

AW: Benannten Bereich auf Gültigkeit prüfen

06.12.2006 10:46:24

IngGi

Hallo Arthur,
das geht mit einer Fehlerbehandlungsroutine. Du versuchst, auf die benannten Bereiche zuzugreifen und löschst sie, wenn der Zugriffsversuch einen Laufzeitfehler erzeugt:

Sub Benannte_Bereiche_Testen()
Dim Bereich As Name
Dim BerVorhanden As Boolean
For Each Bereich In ActiveWorkbook.Names
   BerVorhanden = True
   On Error GoTo Bereich_pruefen
   Debug.Print Range(Bereich).Address
   On Error GoTo 0
   If BerVorhanden = False Then Bereich.Delete
Next 'Bereich
Set Bereich = Nothing
Exit Sub
Bereich_pruefen:
If Err.Number = 1004 Then
   BerVorhanden = False
   Resume Next
Else
   MsgBox "Fehlernummer: " & Err.Number & vbLf & vbLf & Err.Description
   Set Bereich = Nothing
End If
End Sub

Gruß Ingolf

Könnte klappen

06.12.2006 11:00:35

Arthur

Hallo Ingolf.
Das könnte klappen. Wird zwar eine Weile dauern, bis alle Bezüge durchgeprüft sind, aber dann sind sie zumindest identifiziert.
Habe das gerade auch noch probiert und nur eine Zusatzherausforderung: Die Bereiche sind auch im asiatischen Raum entstanden und so ist der Name für die westliche Excel-Welt eher kryptisch. Das "Bereich.Delete" läuft damit auf einen Fehler (Der eingegebene Name ist ungültig.)
Falls Du dazu auch eine Idee hättest ... ;o)
Gruß
Arthur

AW: Könnte klappen

06.12.2006 12:46:48

IngGi

Hallo Arthur,
du könntest dafür noch eine zweite Fehlerbehandlungsroutine einbauen, die dir sämtliche benannten Bereiche, die wegen ungültiger Namen per Makro nicht gelöscht werden können in Spalte A des aktuellen Tabellenblattes anlistet, damit du diese dann manuell löschen kannst. Ich weiß allerdings nicht die Fehlernummer für diesen Fehler, daher musst du in folgendem Makro noch die richtige Fehlernummer eintragen. Laß' mein erstes Makro nochmal laufen und merke dir die Fehlernummer. Die trägst du dann unter der Zeile "nicht_geloescht:" an Stelle der 1004 ein.

Sub Benannte_Bereiche_Testen()
Dim Bereich As Name
Dim BerVorhanden As Boolean
Dim NichtGeloescht As Boolean
For Each Bereich In ActiveWorkbook.Names
   BerVorhanden = True
   On Error GoTo Bereich_pruefen
   Debug.Print Range(Bereich).Address
   On Error GoTo 0
   If BerVorhanden = False Then
      On Error GoTo nicht_geloescht
      Bereich.Delete
      On Error GoTo 0
      If NichtGeloescht = True Then
         Range("A65536").End(xlUp).Offset(1, 0) = Bereich.Name
      End If
   End If
Next 'Bereich
Set Bereich = Nothing
Exit Sub
nicht_geloescht:
If Err.Number = 1004 Then
   NichtGeloescht = True
   Resume Next
Else
   MsgBox "Fehlernummer: " & Err.Number & vbLf & vbLf & Err.Description
   Set Bereich = Nothing
End If
Set Bereich = Nothing
Exit Sub
Bereich_pruefen:
If Err.Number = 1004 Then
   BerVorhanden = False
   Resume Next
Else
   MsgBox "Fehlernummer: " & Err.Number & vbLf & vbLf & Err.Description
   Set Bereich = Nothing
End If
End Sub

Gruß Ingolf

AW: Könnte klappen

06.12.2006 14:49:51

Arthur

Hallo Ingolf.
Danke für die Mühe. Leider sollte das automatisch funktionieren. Als Beispiel haben sich in einer der Dateien mehr als 1.000 solcher Names angesammelt. Die Datei geht leider durch viele Hände und nicht jeder räumt seinen Müll auf. Denke ich werde die Frage nochmals separat ins Forum stellen.
Gruß
Arthur

Beliebteste Forumthreads (12 Monate)

Benannten Bereich auf Gültigkeit prüfen

Forumthread: Benannten Bereich auf Gültigkeit prüfen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread