Alle Formeln auflisten

Publiziert am 3. Februar 2015 von Günther (Admin)

Alle Formeln aller Tabellenblätter in einem getrennten Blatt auflisten

Zur Doku­men­ta­tion kann es manch­mal ganz hil­fre­ich sein, alle Formeln und Funk­tio­nen ein­er Arbeitsmappe zu doku­men­tieren. Ab Excel 2013 gibt es zwar die Möglichkeit, diese per Funk­tion anzeigen zu lassen und seit ewigen Zeit­en auch schon eine Anzeige aller Zellen als Formel (siehe auch dieser Beitrag), aber für eine klare und ein­fach hand­hab­bare Doku­men­ta­tion ist das nicht immer der Königsweg.

Die Forderung sei, dass in einem getren­nten, neu zu erstel­len­den Arbeits­blatt alle ver­wen­de­ten Formeln aller Arbeits­blät­ter (natür­lich außer dem mit der zu erstel­len­den Tabelle) aufge­lis­tet wer­den. Dabei soll in Spalte A der Blat­tname ste­hen, in Spalte B die Zel­ladresse und in Spalte C die ver­wen­dete Formel.

Ein kleines Makro, welch­es Sie in das Mod­ul DieseAr­beitsmappe ein­fü­gen, erledigt das für Sie sehr rasch und kom­fort­a­bel. Hin­weis: Der Code wurde bewusst sehr ein­fach gehal­ten. Er ließe sich noch auf Schnel­ligkeit trim­men, falls sehr große Daten­men­gen zu ver­ar­beit­en sind. Hier ste­ht jedoch das Prinzip und der Lern­ef­fekt im Vorder­grund. Zu Beginn eine Mikro-Ver­sion, welche sich nur auf 1 benan­ntes Arbeits­blatt beschränkt. Hier bitte den Blat­tna­men 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 fol­gende kleine Pro­gramm tut eben­falls das, was Sie erre­ichen wollen. Aber auch etwas mehr als das vorherige. Es wer­den alle Blät­ter der Mappe aus­gew­ertet. Eine einzige kleine Ein­schränkung: Sie müssen ein leeres Arbeits­blatt mit dem Namen Formeln erstellen. Gibt es das schon und dort sind Dat­en enthal­ten oder es soll mit anderen Dat­en gefüllt wer­den, 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” aufhal­ten, ist auf Kom­mentare beim Code verzichtet wor­den. Die Hil­fe in Excel ist recht gut und eine Mail an uns kön­nte mitunter auch helfen …  😉 .

Let­zte Änderung (Ergänzung): 20.06.2015
Let­zte Änderung (Kor­rek­tur des Codes dank eines fre­undlichen Users 😉 ): 09.01.2020

▲ nach oben …

Post Views: 1.697
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.