In diesem Tutorial erfahren Sie, wie Sie Zellformeln mit VBA erstellen.
Formeln in VBA
Mit VBA können Sie Formeln direkt in Bereiche oder Zellen in Excel schreiben. Es sieht so aus:
Sub Formula_Example() 'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2" 'Assign a flexible formula to a range of cells Range("d1:d100").FormulaR1C1 = "=RC2+RC3"End Sub
Es gibt zwei Bereichseigenschaften, die Sie kennen müssen:
- .Formel – Erstellt eine exakte Formel (hartcodierte Zellreferenzen). Gut zum Hinzufügen einer Formel zu einer einzelnen Zelle.
- .FormulaR1C1 – Erstellt eine flexible Formel. Gut zum Hinzufügen von Formeln zu einem Zellbereich, in dem sich Zellreferenzen ändern sollten.
Für einfache Formeln ist es in Ordnung, die zu verwenden .Formel-Eigenschaft. Für alles andere empfehlen wir jedoch den Makrorecorder…
Makrorecorder und Zellformeln
Der Makrorecorder ist unser Werkzeug zum Schreiben von Zellformeln mit VBA. Sie können einfach:
- Aufnahme starten
- Geben Sie die Formel (mit relativen / absoluten Referenzen nach Bedarf) in die Zelle ein & Drücken Sie die Eingabetaste
- Aufnahme beenden
- Öffnen Sie VBA und überprüfen Sie die Formel, passen Sie sie nach Bedarf an und kopieren Sie den Code bei Bedarf.
Ich finde es viel einfacher, eine Formel in eine Zelle einzugeben, als die entsprechende Formel in VBA einzugeben.
Beachten Sie ein paar Dinge:
- Der Makrorecorder verwendet immer die .FormulaR1C1-Eigenschaft
- Der Makrorecorder erkennt absolute vs. relative Zellreferenzen
VBA FormulaR1C1-Eigenschaft
Die FormulaR1C1-Eigenschaft verwendet die Zellreferenz im R1C1-Stil (im Gegensatz zum Standard-A1-Stil, den Sie in Excel gewohnt sind).
Hier sind einige Beispiele:
Sub FormulaR1C1_Examples() 'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4" 'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC" 'Reference D5 (Absolute Row, Relative Column) from cell A1 '=D$5 Range("a1").FormulaR1C1 = "=R5C" 'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"End Sub
Beachten Sie, dass Sie mit der Zellreferenzierung im R1C1-Stil absolute oder relative Referenzen festlegen können.
Absolute Referenzen
In der Standard-A1-Notation sieht eine absolute Referenz folgendermaßen aus: „=$C$2“. In der R1C1-Notation sieht es so aus: „= R2C3“.
So erstellen Sie eine absolute Zellreferenz mit dem Typ R1C1:
- R + Zeilennummer
- C + Spaltennummer
Beispiel: R2C3 würde Zelle $ C $2 darstellen (C ist die 3. Spalte).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
Relative Referenzen
Relative Zellreferenzen sind Zellreferenzen, die sich „bewegen“, wenn die Formel verschoben wird.
In der Standardnotation A1 sehen sie folgendermaßen aus: „=C2“. In der R1C1-Notation verwenden Sie Klammern, um die Zellreferenz von der aktuellen Zelle zu versetzen.
Beispiel: Die Eingabe der Formel „= RC“ in Zelle B3 würde auf Zelle D4 verweisen (die Zelle 1 Zeile darunter und 1 Spalte rechts von der Formelzelle).
Verwenden Sie negative Zahlen, um Zellen über oder links von der aktuellen Zelle zu referenzieren.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
Gemischte Referenzen
Zellreferenzen können teilweise relativ und teilweise absolut sein. Beispiel:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
VBA Formula Property
Beim Setzen von Formeln mit dem .Formeleigenschaft Sie verwenden immer die Notation im A1-Stil. Sie geben die Formel wie in einer Excel-Zelle ein, außer in Anführungszeichen:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA-Formeltipps
Formel mit Variablen
Wenn Sie mit Formeln in VBA arbeiten, möchten Sie häufig Variablen in den Zellenformeln verwenden. Um Variablen zu verwenden, verwenden Sie & , um die Variablen mit dem Rest der Formelzeichenfolge zu kombinieren. Beispiel:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
Formelzitate
Wenn Sie ein Zitat („) in eine Formel einfügen müssen, geben Sie das Zitat zweimal ein („“):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
Ein einfaches Anführungszeichen („) bedeutet für VBA das Ende einer Textzeichenfolge. Während ein doppeltes Anführungszeichen („“) wie ein Anführungszeichen innerhalb der Textzeichenfolge behandelt wird.
Verwenden Sie in ähnlicher Weise 3 Anführungszeichen („““), um eine Zeichenfolge mit einem Anführungszeichen („) zu umgeben
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Weisen Sie der Zeichenfolgenvariablen eine Zellformel zu
Wir können die Formel in einer bestimmten Zelle oder einem bestimmten Bereich lesen und einer Variablen:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
Verschiedene Möglichkeiten, Formeln zu einer Zelle hinzuzufügen
Hier sind einige weitere Beispiele, wie man einer Zelle eine Formel zuweist:
- Formel direkt zuweisen
- Definieren Sie eine Zeichenfolgenvariable, die die Formel enthält
- Verwenden Sie Variablen, um eine Formel zu erstellen
Sub MoreFormulaExamples ()' Alternate ways to add SUM formula' to cell B1' Dim strFormula as String Dim cell as Range dim fromRow as Range, toRow as Range Set cell = Range("B1") ' Directly assigning a String cell.Formula = "=SUM(A1:A10)" ' Storing string to a variable ' and assigning to "Formula" property strFormula = "=SUM(A1:A10)" cell.Formula = strFormula ' Using variables to build a string ' and assigning it to "Formula" property fromRow = 1 toRow = 10 strFormula = "=SUM(A" & fromValue & ":A" & toValue & ") cell.Formula = strFormulaEnd Sub
Formeln aktualisieren
Zur Erinnerung: Um Formeln zu aktualisieren, können Sie den Befehl Berechnen verwenden:
Calculate
Um einzelne Formeln, Bereiche oder das gesamte Arbeitsblatt zu aktualisieren .Stattdessen berechnen:
Sheets("Sheet1").Range("a1:a10").Calculate