Beitrag aus SmartTools Excel Weekly
Alle Namen einer Arbeitsmappe finden
Excel 365 2021 2019 2016 2013 2010
Es gibt immer wieder Situationen, in denen Sie in Formeln auf Namen stoßen, denen Formeln zugewiesen sind. Die Namen der Bereiche können Sie allerdings nirgendwo in der Arbeitsmappe finden. Eine Lösung könnte der Namens-Manager sein, den Sie mit Formeln-Definierte Namen-Namens-Manager aufrufen. Ein Makro kann alternativ alle Namen in der aktuellen Arbeitsmappe in einem neuen Tabellenblatt ausgeben - egal, ob die Namen auf Blattebene oder auf Arbeitsmappenebene definiert worden sind. Das verschafft Ihnen schnell einen Überblick über die vergebenen Namen. Gehen Sie folgendermaßen vor.
Und so richten Sie die Lösung ein:
- Öffnen Sie eine neue, leere Arbeitsmappe.
- Drücken Sie Alt + F8, um das Dialogfenster Makro zu öffnen.
- Geben Sie "ListAllNames" in das Feld Makroname ein, wählen Sie im Dropdownfeld Makros in den Eintrag "Diese Arbeitsmappe" aus und klicken Sie dann auf Erstellen.
- Sie gelangen automatisch in die Programmierumgebung von Excel, in der bereits ein Prozedurgerüst für das Makro "ListAllNames" vorbereitet worden ist. Ergänzen Sie den Makrocode wie folgt:
Sub ListAllNames()
Dim objWbNames As Names
Dim objOutputSheet As Worksheet
Dim lngNamesCount As Long
Dim i As Long
Set objWbNames = ActiveWorkbook.Names
lngNamesCount = objWbNames.Count
If lngNamesCount > 0 Then
Set objOutputSheet = Worksheets.Add()
With objOutputSheet
.Cells(1, 1) = "Name"
.Cells(1, 2) = "Bezug"
For i = 1 To lngNamesCount
.Cells(i + 1, 1).Value = _
objWbNames(i).Name
.Cells(i + 1, 2).Value = _
"'" & objWbNames(i).RefersToLocal
Next i
End With
Else
MsgBox Prompt:="Keine Namen gefunden.", _
Buttons:=vbInformation, _
Title:="Namenslister"
End If
Set objOutputSheet = Nothing
Set objWbNames = Nothing
End Sub
- Verlassen Sie die Programmierumgebung per Datei-Schließen und zurück zu Microsoft Excel.
- Speichern Sie die Arbeitsmappe unter dem Dateinamen "Namenslister" in einem Ordner Ihrer Wahl. Denken Sie daran, dass Sie als Dateityp "Excel-Arbeitsmappe mit Makros (*.xlsm)" einstellen.
Wenn Sie jetzt alle Namen einer beliebigen Arbeitsmappe auflisten wollen, müssen Sie nur die Datei "Namenslister.xls(m)" öffnen. Dann öffnen Sie parallel dazu die Arbeitsmappe, in der Sie die vergebenen Namen ermitteln wollen. In dieser Arbeitsmappe drücken Sie Alt+F8, um das Dialogfenster Makro zu öffnen.
Darin wählen Sie im Dropdownfeld Makros in den Eintrag "Alle offenen Arbeitsmappen" aus. Nun markieren Sie den Makronamen "Namenslister.xls(m)!ListAllNames" in der angezeigten Liste und klicken auf die Schaltfläche Ausführen.
Das Ergebnis ist ein neues Tabellenblatt mit einer Liste aller Namen sowie der zugehörigen Bezüge. Wenn einem Namen eine Formel zugeordnet ist, wird anstelle eines Zellbezugs die entsprechende Formel angezeigt.