Excel-VBA-Formeln: Der ultimative Leitfaden

In diesem Tutorial lernen Sie, wie Sie mit VBA Zellenformeln erstellen können.

Formeln in VBA

Mit VBA können Sie Formeln direkt in Bereiche oder Zellen in Excel schreiben. Das sieht folgendermaßen aus:

Sub Formel_Beispiel()

    'Einer einzelnen Zelle eine fest kodierte Formel zuweisen
    Range("b3").Formula = "=b1+b2"
    
    'Eine flexible Formel einem Bereich von Zellen zuweisen
    Range("d1:d100").FormulaR1C1 = "=RC2+RC3"

End Sub

Es gibt zwei Bereichseigenschaften, die Sie kennen müssen:

• .Formula – Erzeugt eine exakte Formel (fest kodierte Zellbezüge). Gut für das Hinzufügen einer Formel zu einer einzelnen Zelle.
• .FormulaR1C1 – Erzeugt eine flexible Formel. Geeignet für das Hinzufügen von Formeln zu einem Bereich von Zellen, in dem sich die Zellenbezüge ändern sollen.

Für einfache Formeln ist es in Ordnung, die Eigenschaft .Formula zu verwenden. Für alles andere empfehlen wir jedoch die Verwendung des Makro-Aufzeichners…

Makro-Aufzeichner und Zellenformeln

Der Makro-Aufzeichner ist unser bevorzugtes Werkzeug für das Schreiben von Zellenformeln mit VBA. Sie können einfach:

• Aufzeichnung starten
• Die Formel (mit relativen/absoluten Bezügen je nach Bedarf) in die Zelle eingeben und die Eingabetaste drücken
• Aufzeichnung stoppen
• VBA öffnen und die Formel überprüfen, indem Sie sie nach Bedarf anpassen und den Code kopieren und einfügen, wo es notwendig ist.

Ich finde, es ist viel einfacher, eine Formel in eine Zelle einzugeben, als die entsprechende Formel in VBA zu tippen.

Beachten Sie ein paar Sachen:

• Der Makro-Aufzeichner verwendet immer die Eigenschaft .FormulaR1C1
• Der Makro-Aufzeichner unterscheidet zwischen absoluten und relativen Zellenbezügen

VBA-Eigenschaft FormulaR1C1

Die Eigenschaft FormulaR1C1 verwendet eine Zellenreferenzierung im Stil von R1C1 (im Gegensatz zum Standardstil A1, den Sie von Excel gewohnt sind). Hier sind einige Beispiele:

Sub FormulaR1C1_Beispiele()

    'Referenz D5 (absolut)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"
    
    'Referenz D5 (Relativ) aus Zelle A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"
    
    'Referenz D5 (Absolute Zeile, Relative Spalte) aus Zelle A1
    '=D$5
    Range("a1").FormulaR1C1 = "=R5C[3]"
    
    'Referenz D5 (Relative Zeile, Absolute Spalte) aus Zelle A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

End Sub

Beachten Sie, dass die Zellenreferenzierung im Stil von R1C1 es Ihnen ermöglicht, absolute oder relative Referenzen festzulegen.

Absolute Referenzen

In der Standard-A1-Darstellung sieht ein absoluter Bezug folgendermaßen aus: „=$C$2“. In der R1C1-Darstellung sieht er folgendermaßen aus: „=R2C3“. Um einen absoluten Zellenverweis in R1C1-Schreibweise zu erstellen, geben Sie ein:

• R + Zeilennummer
• C + Spaltennummer

Beispiel: R2C3 würde die Zelle $C$2 darstellen (C ist die 3. Spalte).

   'Referenz D5 (absolut)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"

Relative Bezüge

Relative Zellenbezüge sind Zellenbezüge, die sich „bewegen“, wenn die Formel verschoben wird. In der Standard-Darstellung A1 sehen sie folgendermaßen aus: „=C2“. In der R1C1-Darstellung verwenden Sie die Klammern [], um den Zellenbezug von der aktuellen Zelle zu versetzen. Beispiel: Die Eingabe der Formel „=R[1]C[1]“ in Zelle B3 würde sich auf Zelle D4 beziehen (die Zelle, die sich eine Zeile unter und eine Spalte rechts von der Formelzelle befindet). Verwenden Sie negative Zahlen, um auf Zellen über oder links von der aktuellen Zelle zu verweisen.

   'Referenz D5 (Relativ) von Zelle A1
   '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"

Gemischte Referenzen

Zellenbezüge können teilweise relativ und teilweise absolut sein. Beispiel:

   'Referenz D5 (Relative Zeile, Absolute Spalte) aus Zelle A1
   '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

VBA-Formeleigenschaft

Beim Festlegen von Formeln mit der Formeleigenschaft verwenden Sie immer die Darstellung im Stil von A1. Sie geben die Formel genauso ein, wie Sie es in einer Excel-Zelle tun würden, außer der Tatsache, dass sie von Anführungszeichen umgeben ist:

   'Zuweisen einer fest kodierten Formel zu einer einzelnen Zelle
    Range("b3").Formula = "=b1+b2"

VBA-Formel-Tipps

Formel mit Variablen

Bei der Arbeit mit Formeln in VBA kommt es häufig vor, dass man innerhalb der Zellenformeln Variablen verwenden möchte. Um Variablen zu verwenden, benutzen Sie &, um die Variablen mit dem Rest der Formelzeichenfolge zu kombinieren. Beispiel:

Sub Formel_Variable()
    Dim SpalteNum As Long
    SpalteNum = 4

    Range("a1").FormulaR1C1 = "=R1C" & SpalteNum & "+R2C" & SpalteNum

End Sub

Formelanführungszeichen

Wenn Sie innerhalb einer Formel ein Anführungszeichen („) einfügen müssen, geben Sie das Anführungszeichen zweimal ein („“):

Sub Makro2()
    Range("B3").FormulaR1C1 = "=TEXT(RC[-1],""mm/dd/yyyy"")"
End Sub

Ein einfaches Anführungszeichen („) bedeutet für VBA das Ende einer Textzeichenfolge. Ein doppeltes Anführungszeichen („“) hingegen wird wie ein Anführungszeichen innerhalb der Textzeichenfolge behandelt. In ähnlicher Weise können Sie 3 Anführungszeichen („““) verwenden, um eine Zeichenfolge mit einem Anführungszeichen („) zu umgeben

MsgBox """Verwenden Sie 3, um eine Zeichenfolge mit Anführungszeichen zu umgeben"""
' Dadurch wird <"Verwenden Sie 3, um eine Zeichenfolge mit Anführungszeichen zu umgeben"> Direktfenster ausgegeben

Zellenformel einer String-Variablen zuweisen

Wir können die Formel in einer bestimmten Zelle oder einem Bereich lesen und sie einer String-Variablen zuweisen:

'Zellenformel einer Variablen zuweisen
Dim strFormel as String
strFormel = Range("B1").Formula

Verschiedene Möglichkeiten, einer Zelle Formeln hinzuzufügen

Im Folgenden Abschnitt finden Sie einige weitere Beispiele dafür, wie Sie einer Zelle eine Formel zuweisen können:

1. Direktes Zuweisen der Formel
2. Definieren Sie eine String-Variable, die die Formel enthält
3. Variablen zum Erstellen von Formeln verwenden

Sub MehrFormelBeispiele()
' Alternative Möglichkeiten zum Hinzufügen der Formel SUMME
' zu Zelle B1
'
  Dim strFormel as String
  Dim Zelle as Range
  Dim vonZeile As Long, zuZeile As Long

  Set Zelle = Range("B1")

  ' Direktes Zuweisen einer Zeichenfolge
  Zelle.Formula = "=SUM(A1:A10)"

  ' Zeichenfolge in eine Variable speichern
  ' und der Eigenschaft "Formel" zuweisen
  strFormel = "=SUM(A1:A10)"
  Zelle.Formula = strFormel

  ' Variablen verwenden, um eine Zeichenfolge zu erstellen 
  ' und Zuweisung an die Eigenschaft "Formel"
  vonZeile = 1
  zuZeile = 10
  strSummenBereich = "A" & vonZeile & ":A" & zuZeile
  strFormel = "=SUM(" & strSummenBereich & ")"
  Zelle.Formula = strFormel
End Sub

Formeln aktualisieren

Zur Erinnerung: Um Formeln zu aktualisieren, können Sie den Befehl Calculate verwenden:

Calculate

Um eine einzelne Formel, einen Bereich oder ein ganzes Arbeitsblatt zu aktualisieren, verwenden Sie stattdessen .Calculate:

Sheets("Sheet1").Range("a1:a10").Calculate