Alle Formeln auflisten

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

▲ nach oben …

Dieser Beitrag wurde unter Code-Schnipsel, Excel-Funktionen, Mit VBA/Makro, Tabelle und Zelle, Text, Tipps und Tricks, Verschiedenes abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.