Erstellen eines Namensberichts mit VBA-Code

Erstellen eines Namensberichts mit VBA-Code - Dummies

Wenn Sie in Ihrer Arbeitsmappe viele benannte Zellen und Bereiche verwenden, können Sie überrascht sein, dass Excel keine Möglichkeit bietet, die Details für jeden Namen aufzulisten. Nützliche Informationen werden im Dialogfeld "Name Manager" angezeigt, aber es gibt keine Möglichkeit, diese Informationen auf eine Weise anzuzeigen, die gedruckt werden kann.

Der hier angebotene VBA-Code generiert einen nützlichen Bericht, der die in einer beliebigen Arbeitsmappe definierten Namen beschreibt.

Beispiel Name Report

Hier sehen Sie ein Beispiel eines Namensreports.

Der Bericht, der in einem neuen Arbeitsblatt erstellt wird, enthält für jeden Namen die folgenden Informationen:

  • Name: Der Name.

  • RefersTo: Die Definition für den Namen. Normalerweise ist dies eine Zelle oder ein Bereich, aber ein Name kann auch eine Formel definieren.

  • Zellen: Die Anzahl der Zellen im angegebenen Bereich. Für benannte Formeln wird in diesem Feld # N / A angezeigt.

  • Bereich: Der Bereich des Namens - entweder Arbeitsmappe oder der Name des spezifischen Arbeitsblatts, auf dem der Name gültig ist.

  • Versteckt: Wahr, wenn der Name ausgeblendet ist. Versteckte Namen werden von einigen Add-Ins (z. B. Solver) erstellt und werden nicht im Dialogfeld Name Manager angezeigt.

  • Fehler: True, wenn der Name eine fehlerhafte Referenz enthält.

  • Link: Ein Hyperlink, der beim Anklicken den benannten Bereich aktiviert. Nur Namen, die sich auf Zellen oder Bereiche beziehen, enthalten einen Link.

  • Kommentar: Der Kommentar zum Namen, falls vorhanden.

Der VBA-Code

Um diesen Code zu verwenden, drücken Sie Alt + F11, um den Visual Basic-Editor zu aktivieren. Wählen Sie dann Einfügen → Modul, um ein neues VBA-Modul einzufügen. Kopieren Sie den Code und fügen Sie ihn in das neue Modul ein.

 Sub GenerateNameReport () 'Erzeugt einen Bericht für alle Namen in der Arbeitsmappe' (Enthält keine Tabellennamen) Dim n Wie Name Dim Row As Long Dim CellCount Als Variant 'Beenden wenn keine Namen If ActiveWorkbook. Namen. Count = 0 Then MsgBox "Die aktive Arbeitsmappe hat keine definierten Namen. "Exit Sub End If 'Beenden, wenn die Arbeitsmappe geschützt ist Wenn ActiveWorkbook. ProtectStructure Then MsgBox "Ein neues Blatt kann nicht hinzugefügt werden, da die Arbeitsmappe geschützt ist. "Exit Sub End If 'Fügt ein neues Blatt für den Bericht ActiveWorkbook ein. Arbeitsblätter. Fügen Sie ActiveSheet hinzu. Verschieben nach: = Blätter (ActiveWorkbook, Blätter, Anzahl) ActiveWindow. DisplayGridlines = False 'Fügt die erste Zeile des Titelbereichs hinzu ("A1: H1"). Zusammenführen mit Bereich ("A1"). Wert = "Namensbericht für:" & ActiveWorkbook. Name. Schriftart. Größe = 14. Schriftart. Fett = Wahr. HorizontalAlignment = xlCenter End Mit 'Zweite Zeile des Titelbereichs hinzufügen ("A2: H2"). Zusammenführen mit Bereich ("A2"). Wert = "Generiert" & Jetzt. HorizontalAlignment = xlCenter Ende Mit 'Kopfzeilen hinzufügen' ("A4: H4") = Array ("Name", "RefersTo", "Zellen", "Scope", "Versteckt", "Fehler", "Link", "" Kommentar ") 'Schleife durch die Namen Row = 4 On Error Fortsetzen Nächste für jede n In ActiveWorkbook.Namen Zeile = Zeile + 1 'Spalte A: Name Wenn n. Name wie "*! * "Then Cells (Row, 1) = Teilen (n. Name,"! ") (1) 'Entfernen Sie den Blattnamen Else Cells (Row, 1) = n. Name Ende If 'Spalte B: RefersTo Zellen (Zeile, 2) = "'" & n. RefersTo 'Spalte C: Anzahl der Zellen CellCount = CVErr (xlErrNA)' Rückgabewert für die benannte Formel CellCount = n. RefersToRange. CountLarge Cells (Row, 3) = CellCount 'Spalte D: Bereich Wenn n. Name wie "*! * "Dann Zellen (Zeile, 4) = Teilen (n. Name,"! ") (0) 'Blattname extrahieren Zellen (Zeile, 4) = Ersetzen (Zellen (Zeile, 4),"' "," ") 'Apostrophe entfernen Else Cells (Zeile, 4) = "Arbeitsmappe" End If' Spalte E: Versteckte Statuszellen (Zeile, 5) = Nicht n. Sichtbar 'Spalte F: Fehlerhafte Namenszellen (Zeile, 6) = n. RefersTo wie "* [#] REF! * "'Spalte G: Hyperlink, wenn keine Anwendung. IsNA (Zellen (Zeile, 3)) Dann ActiveSheet. Hyperlinks. Add _ Anchor: = Zellen (Zeile, 7), _ Adresse: = "", _ SubAdresse: = n. Name, _ TextToDisplay: = n. Name Ende If 'Spalte H: Kommentarzellen (Zeile, 8) = n. Comment Next n 'Konvertiere es in eine Tabelle ActiveSheet. Listenobjekte. Add Quelltyp: = xlSrcRange, _ Quelle: = Bereich ("A4"). CurrentRegion 'Anpassen der Spaltenbreiten Spalten ("A: H"). EntireColumn. AutoFit End Sub 

Generieren eines Berichts

Führen Sie die Prozedur GenerateNameReport aus, und der Bericht wird in einem neuen Arbeitsblatt in der aktiven Arbeitsmappe erstellt. Der Code muss nicht in der Arbeitsmappe enthalten sein, die die Namen für den Bericht enthält.

Wenn Sie diesen Code nützlich finden, können Sie ihn in Ihrer Personal Macro Workbook speichern oder ein Add-In erstellen.