VBA Excel: Formel in Zelle einfügen

In Excel und anderen Microsoft Office-Anwendungen lassen sich prima wiederholende Aufgaben mittels VBA automatisieren, um so schneller und produktiver zu arbeiten. Manchmal kann es dabei notwendig sein, dass über ein Makro eine Formel in eine Excel-Zelle einfügen muss. Klar, die triviale Lösung wäre einfach die notwendigen Berechnungen innerhalb das Makros auszuführen und dann das fertige Ergebnis in an die Zelle zu übergeben. Aber die Anwendungen sind vielfältig und dieser Ansatz hilft nicht, wenn nachträglich Eingangswerte in der Excel-Tabelle geändert werden und über die Formel gleich das neue Ergebnis dargestellt werden soll.

Ein Beispiel könnte sein, wenn man zum Beispiel eine Rechnung in Excel erstellt und für die Berechnung der Umsatzsteuer jeweils eine andere Formel benötigt in Abhägingkeit, ob es sich um einen ermäßigten Umsatzsteuersatz oder den normalen Umsatzsteuersatz handelt.

Inhaltsverzeichnis

Formel per Makro einfügen (FormulaLocal)

Hier soll das Einfügen einer Formel in eine Excel-Zelle via eines Makros mal an einem einfachen Beispiel gezeigt werden. Es soll einfach eine Formel eingefügt werden, welche die drei Wert addiert; also einfach:

“=B3+B4+B5” oder
“=SUMME(B3:B5)”

Screenshot Excel mit 3 Zahlenwerten zum Einfügen einer Summenformel — Formel zum Summieren dreier Werte soll hier eingefügt werden

Der VBA-Code für das Makro ist dann sehr einfach. Man nimmt die betreffende Zelle, wo die Formel reingeschrieben werden soll [Cells(6,2)] und ruft die Eigenschaft FormulaLocal auf. Diese weist man einfach per String die Formel zu, genau in derselben Schreibweise als ob man die Formel direkt in die Zelle eintragen würde.

Sub InsertFormula()
'ggf. vorher noch richtige Tabelle auswählen
Worksheets("Tabelle1").Activate

Cells(6, 2).FormulaLocal = "=SUMME(B3:B5)"
End Sub

Und schon hat man das fertige Ergebnis:

Screenshot Excel: Formel wurde über Makro eingefügt — Nach Ausführung des VBA-Makros

Komplexere Formeln mit Anführungszeichen

Nicht alle Formeln sind in Excel so einfach, wie diese Beipiel. Schnell können diese unüberschaubare Dimensionen annehmen, insbesondere, wenn man mehrere Formeln ineinander, ggf. noch über WENN-Bedingungen miteinander verschachteln muss.

Bei manchen Formeln ist es notwendig, dass man String-Werte übergeben muss, die selbst in Anführungszeichen gesetzt werden. Im VBA geht das aber nicht direkt, weil die Formel selbst ein Zeichenkette ist. Dafür bedarf es dann einer besonderen Schreibweise im VBA-Code für Anführungszeichen.

Dazu nehmen wir ein neues Beispiel. In diesem Fall soll die Formel so arbeiten, dass die Summe bzw. der Gesamtpreis nur von den Artikeln berechnet wird, die in der Spalte Kaufen ein “Ja” enthalten. Ist kein Artikel ausgewählt, soll ein Text “Kein Artikel ausgewählt” angezeigt werden.

Excel Screenshot Beispiel für kompliziertere WENN-Formel — Beispiel für etwas kompliziertere Formel

Direkt in Excel würde man die Formel wie folgt eintragen:

=WENN((WENN(B3=”Ja”; C3; 0) + WENN(B4=”Ja”;C4;0)+WENN(B5=”Ja”;C5;0)) > 0; (WENN(B3=”Ja”; C3; 0) + WENN(B4=”Ja”;C4;0)+WENN(B5=”Ja”;C5;0)); “Kein Artikel ausgewählt”)

Im VBA sieht dann der Code so aus. Man achte auf die Schreibweise der Anführungszeichen innerhalb der Formel. Diese müssen doppelt geschrieben werden, damit der VBA-Compiler zwischen dem eigentlichen String unterscheiden kann. Alternativ kann man eine Schreibweise der Anführungszeichen mit der Funktion Chr(34) verwenden.

Sub InsertFormula()
'ggf. vorher noch richtige Tabelle auswählen
Worksheets("Tabelle1").Activate

Cells(6, 3).FormulaLocal = "=WENN((WENN(B3=""Ja"";C3;0)+WENN(B4=""Ja"";C4;0)+WENN(B5=""Ja"";C5;0))>0;(WENN(B3=""Ja"";C3;0)+WENN(B4=""Ja"";C4;0)+WENN(B5=""Ja"";C5;0));""Kein Artikel ausgewählt"")"
End Sub

Und im Excel selbst liefert dann die Formel diese beispielhaften Ergebnisse:

Screenshot Excel mit verschiedenen beispielhaften Ergebnissen — eingefügte Formel gibt die verschiedenen Ergebisse aus

Formel mit variabler Zeilennummer

Es kann auch Formeln geben, bei denen sich der Zellenbezug dynamisch ändert, zum Beispiel, wenn man dieselbe Formel eine Zeile tiefer einfügt und man die Werte dieser Zeile zum Beispiel addieren möchte. Dann kann man die Zeilennummer im VBA-Code auch variabel gestalten. Die Zeilennummer verwendet man dann als Integer-Variable.

Screenshot Excel Formel — Zeilennummer in Formel soll sich anpassen abhängig von Zellauswahl

Normalweise würde der Code im VBA einfach so aussehen.

Sub InsertFormula()
    Cells(3, 3).FormulaLocal = "=A3+B3"
End Sub

Das macht aber keinen Sinn, wenn wir die Summenformel zum Beispiel in die Zeile 4 eintragen wollen.

In dem Beispiel ermitteln wir die Zeilennummer der markierten Zelle und passen die Formel dynamisch an:

Sub InsertFormula()
Dim activeRow As Integer
activeRow = ActiveCell.Row
Cells(activeRow, 3).FormulaLocal = "=A" & CStr(activeRow) & "+B" & CStr(activeRow)
End Sub

Man bastelt sich also die Formel entsprechend zusammen. So kann man auch ganz viele Zellen mit einer For-Schleife iterieren. Wir verwenden hier die CStr-Funktion, um zuvor den Integer-Wert in String umzuwandeln. Doch es hat sich gezeigt, dass dies gar nicht zwingend notwendig ist, die Anwweisung ist so tolerabel, dass es auch ohne CStr() funktioniert.

“Error 400” oder “#NAME?”

Wenn man beim Einfügen der Formel in eine Excel-Zelle die Fehlermeldung “Error 400” erhält oder in der Excel-Zelle “#NAME?” angezeigt wird, dann hat man wahrscheinlich den Fehler gemacht, dass man anstatt FormulaLocal die Value-Eigenschaft verwendet hat.

Sub InsertFormula()
'ggf. vorher noch richtige Tabelle auswählen
Worksheets("Tabelle1").Activate
'das funktioniert nicht
Cells(6, 2).Value = "=SUMME(B3:B5)"
End Sub

Bestseller Nr. 1