Alle Formeln aller Tabellenblätter in einem getrennten Blatt auflisten
Zur Dokumentation kann es manchmal ganz hilfreich sein, alle Formeln und Funktionen einer Arbeitsmappe zu dokumentieren. Ab Excel 2013 gibt es zwar die Möglichkeit, diese per Funktion anzeigen zu lassen und seit ewigen Zeiten auch schon eine Anzeige aller Zellen als Formel (siehe auch dieser Beitrag), aber für eine klare und einfach handhabbare Dokumentation ist das nicht immer der Königsweg.
Die Forderung sei, dass in einem getrennten, neu zu erstellenden Arbeitsblatt alle verwendeten Formeln aller Arbeitsblätter (natürlich außer dem mit der zu erstellenden Tabelle) aufgelistet werden. Dabei soll in Spalte A der Blattname stehen, in Spalte B die Zelladresse und in Spalte C die verwendete Formel.
Ein kleines Makro, welches Sie in das Modul DieseArbeitsmappe einfügen, erledigt das für Sie sehr rasch und komfortabel. Hinweis: Der Code wurde bewusst sehr einfach gehalten. Er ließe sich noch auf Schnelligkeit trimmen, falls sehr große Datenmengen zu verarbeiten sind. Hier steht jedoch das Prinzip und der Lerneffekt im Vordergrund. Zu Beginn eine Mikro-Version, welche sich nur auf 1 benanntes Arbeitsblatt beschränkt. Hier bitte den Blattnamen bei Bedarf anpassen:
Sub AlleFormelnSchreiben() Dim wksSrc As Worksheet, wksDst As Worksheet, c As Range Dim fRow As Integer Set wksSrc = Sheets("Tabelle1") '<- Anpassen! Sheets.Add after:=ThisWorkbook.Sheets(Sheets.Count) Set wksDst = ActiveSheet Cells(1, 1) = "Verwendete Formeln und Funktionen in Blatt " & Chr(34) & wksSrc.Name & Chr(34) For Each c In wksSrc.UsedRange If Left(c.Formula, 1) = "=" Then With wksDst fRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1 .Cells(fRow, 1) = "'" & c.FormulaLocal End With End If Next c End Sub
Das folgende kleine Programm tut ebenfalls das, was Sie erreichen wollen. Aber auch etwas mehr als das vorherige. Es werden alle Blätter der Mappe ausgewertet. Eine einzige kleine Einschränkung: Sie müssen ein leeres Arbeitsblatt mit dem Namen Formeln erstellen. Gibt es das schon und dort sind Daten enthalten oder es soll mit anderen Daten gefüllt werden, dann geben Sie dem neuen Blatt einen anderen Namen und ändern Sie im VBA-Code den Namen des Blattes entsprechend ab Zeile: Set wksFormeln = Sheets("Formeln")).
Option Explicit Sub AlleFormeln() Dim wksBlatt As Worksheet, wksFormeln As Worksheet Dim c As Range Dim BlName As String Dim fRow As Long Set wksFormeln = Sheets("Formeln") With wksFormeln .Cells(1, 1) = "Blatt-Name" .Cells(1, 2) = "Zell-Adresse" .Cells(1, 3) = "Formel" End With For Each wksBlatt In ActiveWorkbook.Worksheets BlName = wksBlatt.Name If BlName <> "Formeln" Then For Each c In wksBlatt.UsedRange If c.HasFormula Then fRow = wksFormeln.Cells(Rows.Count, 1).End(xlUp).Row + 1 With wksFormeln .Cells(fRow, 1) = BlName .Cells(fRow, 2) = c.Address(0, 0) .Cells(fRow, 3) = "'" & CStr(c.Formula) End With End If Next c End If Next wksBlatt End Sub
Da Sie sich hier in den „Tipps und Tricks“ aufhalten, ist auf Kommentare beim Code verzichtet worden. Die Hilfe in Excel ist recht gut und eine Mail an uns könnte mitunter auch helfen … 😉 .
Letzte Änderung (Ergänzung): 20.06.2015
Letzte Änderung (Korrektur des Codes dank eines freundlichen Users 😉 ): 09.01.2020