In diesem Tutorial erfahren Sie, wie Sie Zellenformeln mit VBA erstellen.
Formeln in VBA
Mit VBA können Sie Formeln direkt in Bereiche oder Zellen in Excel schreiben. Es sieht aus wie das:
| 123456789 | Unterformel_Beispiel()'Einer einzelnen Zelle eine hartcodierte Formel zuweisenRange("b3").Formel = "=b1+b2"'Einem Zellbereich eine flexible Formel zuweisenRange("d1:d100").FormulaR1C1 = "=RC2+RC3"End Sub |
Es gibt zwei Range-Eigenschaften, die Sie kennen müssen:
- .Formel - Erstellt eine exakte Formel (hartcodierte Zellbezüge). Gut zum Hinzufügen einer Formel zu einer einzelnen Zelle.
- .FormelR1C1 - Erstellt eine flexible Formel. Gut zum Hinzufügen von Formeln zu einem Zellbereich, in dem sich die Zellbezüge ändern sollen.
Für einfache Formeln ist es in Ordnung, die .Formula-Eigenschaft zu verwenden. Für alles andere empfehlen wir jedoch die Verwendung der Makro-Recorder…
Makrorekorder und Zellformeln
Der Macro Recorder ist unser Tool zum Schreiben von Zellformeln mit VBA. Sie können einfach:
- Starte die Aufnahme
- Geben Sie die Formel (mit relativen / absoluten Bezügen nach Bedarf) in die Zelle ein und drücken Sie die Eingabetaste
- Höre auf, aufzunehmen
- Öffnen Sie VBA und überprüfen Sie die Formel, passen Sie sie nach Bedarf an und kopieren und fügen Sie den Code bei Bedarf ein.
Ich finde es viel einfacher eine Formel in eine Zelle einzugeben, als die entsprechende Formel in VBA einzugeben.
Beachten Sie ein paar Dinge:
- Der Makrorekorder verwendet immer die Eigenschaft .FormulaR1C1
- Der Makrorekorder erkennt absolute vs. relative Zellreferenzen
VBA FormulaR1C1 Eigenschaft
Die FormulaR1C1-Eigenschaft verwendet die Zellreferenzierung im R1C1-Stil (im Gegensatz zum standardmäßigen A1-Stil, den Sie in Excel gewohnt sind).
Hier sind einige Beispiele:
| 12345678910111213141516171819 | UnterformelR1C1_Examples()'Referenz D5 (absolut)'=$D$5Range("a1").FormulaR1C1 = "=R5C4"'Referenz D5 (Relativ) aus Zelle A1'=D5Range("a1").FormulaR1C1 = "=R[4]C[3]"'Referenz D5 (Absolute Zeile, Relative Spalte) aus Zelle A1'=D$5Range("a1").FormulaR1C1 = "=R5C[3]"'Referenz D5 (Relative Zeile, absolute Spalte) aus Zelle A1'=$D5Range("a1").FormulaR1C1 = "=R[4]C4"End Sub |
Beachten Sie, dass Sie mit der Zellreferenzierung im R1C1-Stil absolute oder relative Referenzen festlegen können.
Absolute Referenzen
In der Standardnotation A1 sieht eine absolute Referenz so aus: „=$C$2“. In R1C1-Notation sieht das so aus: „=R2C3“.
So erstellen Sie eine absolute Zellreferenz mit dem Typ R1C1-Stil:
- R + Zeilennummer
- C + Spaltennummer
Beispiel: R2C3 würde Zelle $C$2 darstellen (C ist die 3. Spalte).
| 123 | 'Referenz D5 (absolut)'=$D$5Range("a1").FormulaR1C1 = "=R5C4" |
Relative Referenzen
Relative Zellbezüge sind Zellbezüge, die sich beim Verschieben der Formel „bewegen“.
In der Standardnotation A1 sehen sie so aus: „=C2“. In der R1C1-Notation verwenden Sie Klammern [], um den Zellbezug von der aktuellen Zelle zu verschieben.
Beispiel: Die Eingabe der Formel "=R[1]C[1]" 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 auf Zellen oberhalb oder links von der aktuellen Zelle zu verweisen.
| 123 | 'Referenz D5 (Relativ) aus Zelle A1'=D5Range("a1").FormulaR1C1 = "=R[4]C[3]" |
Gemischte Referenzen
Zellbezüge können teilweise relativ und teilweise absolut sein. Beispiel:
| 123 | 'Referenz D5 (Relative Zeile, absolute Spalte) aus Zelle A1'=$D5Range("a1").FormulaR1C1 = "=R[4]C4" |
VBA-Formeleigenschaft
Beim Einstellen von Formeln mit der .Formeleigenschaft Sie werden immer die Notation im A1-Stil verwenden. Sie geben die Formel wie in eine Excel-Zelle ein, außer in Anführungszeichen:
| 12 | 'Einer einzelnen Zelle eine hartcodierte Formel zuweisenRange("b3").Formel = "=b1+b2" |
Tipps zur VBA-Formel
Formel mit Variable
Bei der Arbeit mit Formeln in VBA ist es sehr üblich, Variablen innerhalb der Zellformeln zu verwenden. Um Variablen zu verwenden, verwenden Sie &, um die Variablen mit dem Rest der Formelzeichenfolge zu kombinieren. Beispiel:
| 1234567 | Sub Formula_Variable()Dim colNum As LongSpaltennummer = 4Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub |
Formel-Zitate
Wenn Sie innerhalb einer Formel ein Anführungszeichen (“) einfügen müssen, geben Sie das Anführungszeichen zweimal ein („“):
| 123 | Untermakro2()Range("B3").FormulaR1C1 = "=TEXT(RC[-1],""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 Zitat innerhalb der Textzeichenfolge behandelt wird.
Verwenden Sie auf ähnliche Weise 3 Anführungszeichen („““), um eine Zeichenfolge mit einem Anführungszeichen („“) zu umgeben.
| 12 | MsgBox """Verwenden Sie 3, um eine Zeichenfolge in Anführungszeichen zu setzen"""' Dies wird das sofortige Fenster drucken |
Zellformel der String-Variablen zuweisen
Wir können die Formel in einer bestimmten Zelle oder einem bestimmten Bereich lesen und einer String-Variablen zuweisen:
| 123 | 'Zellformel einer Variablen zuweisenDim strFormel als StringstrFormula = Range("B1").Formula |
Verschiedene Möglichkeiten zum Hinzufügen von Formeln zu einer Zelle
Hier sind einige weitere Beispiele für das Zuweisen einer Formel zu einer Zelle:
- Formel direkt zuweisen
- Definieren Sie eine String-Variable, die die Formel enthält
- Verwenden Sie Variablen, um Formeln zu erstellen
| 12345678910111213141516171819202122232425 | Sub MehrFormelBeispiele ()' Alternative Möglichkeiten zum Hinzufügen von SUM-Formel' zu Zelle B1'Dim strFormel als StringZelle als Bereich dimmendim fromRow as Range, toRow as RangeZelle setzen = Range("B1")' Direkte Zuweisung eines Stringscell.Formula = "=SUMME(A1:A10)"' String in eine Variable speichern' und der Eigenschaft "Formel" zuweisenstrFormel = "=SUMME(A1:A10)"cell.Formula = strFormula' Verwenden von Variablen, um einen String zu erstellen' und der Eigenschaft "Formel" zuordnenfromRow = 1toRow = 10strFormula = "=SUMME(A" & fromValue & ":A" & toValue &")cell.Formula = strFormulaEnd Sub |
Formeln aktualisieren
Zur Erinnerung: Um Formeln zu aktualisieren, können Sie den Befehl Berechnen verwenden:
| 1 | Berechnung |
Um eine einzelne Formel, einen Bereich oder ein gesamtes Arbeitsblatt zu aktualisieren, verwenden Sie stattdessen .Calculate:
| 1 | Sheets("Sheet1").Range("a1:a10").Berechnen |
