Siehe auch: | Formeln auslesen - Englische Formelnamen und mehr |
Autor: Peter Haserodt --- Aus Excel VBA - Gruppe: Häufige Fragen
Formeln in Zellen schreiben
Autor: Peter Haserodt - Erstellt: -- - Letzte Revision: --
Wie schreibt man eine Formel in eine Zelle - Auf was muss man achten
(Für bestimmte Erklärungen gehe ich von einem deutschen Excel aus)
Und wie immer lernen wir es am Besten an einem Beispiel:
Aufgabe ist es klarerweise, die Formel in der Zelle E2 per Code zu erstellen.
Machen wir es doch einfach mal, bevor ich mit den Erklärungen beginne.
Erstellen Sie ein Modul in Ihrer Beispielmappe und dorthinein dann folgender Code:
(Für bestimmte Erklärungen gehe ich von einem deutschen Excel aus)
Und wie immer lernen wir es am Besten an einem Beispiel:
| Tabelle1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Machen wir es doch einfach mal, bevor ich mit den Erklärungen beginne.
Erstellen Sie ein Modul in Ihrer Beispielmappe und dorthinein dann folgender Code:
' **************************************************************
' Modul: mdlFormelnSchreiben Typ = Allgemeines Modul
' **************************************************************
Option Explicit
Public Sub FormelnSchreiben1()
Dim oBlatt As Worksheet
Set oBlatt = ThisWorkbook.Worksheets("Tabelle1") 'Tabellennamen gg. anpassen
With oBlatt
.Range("e2").FormulaLocal = "=Summe(B2:C5)"
End With
Set oBlatt = Nothing
End Sub
Die Technik des Formelschreibens per VBA ist vom Prinzip:
Ich muss einen String erstellen, der so aussieht, wie ich es in die Zelle als Formel schreiben würde.
(Achtung - ich sagte vom Prinzip - wir werden sehen, dass dies nicht ganz stimmt)
Wichtig ist aber die Eigenschaft der Zelle, die ich anspreche.
Um Werte in eine Zelle zu schreiben, benutze ich z.B. ihre Eigenschaft Value.
In unserem Fall müssen wir eine Eigenschaft benutzen, die mit Formula beginnt.
Wir werden sehen, dass es da aber mehrere Eigenschaften gibt.
In unserem Beispiel haben wir die Eigenschaft FormulaLocal benutzt.
Dies beutet nichts anderes, als dass wir Funktionsnamen in der Sprache unseres Excel schreiben.
(Und noch dazu, dass die Formel in der A1 schreibweise vorliegt, mehr dazu später)
Wir wollen nun die Zelle E3 mit der gleichen Formel bestücken, nur auf eine andere Art.
Ergänzen Sie den Code unter der FormulaLocal Zuweisung durch folgende Zeile:
(Vergessen Sie die Punkte nicht)
.Range("e3").Formula = "=Sum(B2:C5)"
Starten Sie dann wieder die Prozedur.
Ich muss einen String erstellen, der so aussieht, wie ich es in die Zelle als Formel schreiben würde.
(Achtung - ich sagte vom Prinzip - wir werden sehen, dass dies nicht ganz stimmt)
Wichtig ist aber die Eigenschaft der Zelle, die ich anspreche.
Um Werte in eine Zelle zu schreiben, benutze ich z.B. ihre Eigenschaft Value.
In unserem Fall müssen wir eine Eigenschaft benutzen, die mit Formula beginnt.
Wir werden sehen, dass es da aber mehrere Eigenschaften gibt.
In unserem Beispiel haben wir die Eigenschaft FormulaLocal benutzt.
Dies beutet nichts anderes, als dass wir Funktionsnamen in der Sprache unseres Excel schreiben.
(Und noch dazu, dass die Formel in der A1 schreibweise vorliegt, mehr dazu später)
Wir wollen nun die Zelle E3 mit der gleichen Formel bestücken, nur auf eine andere Art.
Ergänzen Sie den Code unter der FormulaLocal Zuweisung durch folgende Zeile:
(Vergessen Sie die Punkte nicht)
.Range("e3").Formula = "=Sum(B2:C5)"
Starten Sie dann wieder die Prozedur.
Formula versus FormulaLocal
Sie sehen, in Zelle E3 steht das Gleiche wie in E2.
Obwohl wir Sum geschrieben haben, wurde dort Summe eingetragen.
Man darf nie vergessen, dass im Hintergrund Excel sowieso mit den englischen Formeln arbeitet, es ist nur eine Frage der Darstellung in der Zelle.
Und welche Variante soll man nun bevorzugen?
Ich empfehle unbedingt, wenn es nur irgendwie möglich ist, die englische Schreibweise - also Formula - zu benutzen.
Sie können damit gewährleisten, dass Ihre Mappe zumindest in dieser Beziehung international stabil bleibt.
(Es gibt Situationen, bei denenen man dies nicht gewährleisten kann aber dies ist ein anderes Thema)
Zwar erfordert dies ein bisserl mehr Arbeit aber es lohnt sich.
Die englischen Formelnamen können Sie ganz leicht ermitteln, sehen Sie mal oben in Siehe auch.
Sie sehen, in Zelle E3 steht das Gleiche wie in E2.
Obwohl wir Sum geschrieben haben, wurde dort Summe eingetragen.
Man darf nie vergessen, dass im Hintergrund Excel sowieso mit den englischen Formeln arbeitet, es ist nur eine Frage der Darstellung in der Zelle.
Und welche Variante soll man nun bevorzugen?
Ich empfehle unbedingt, wenn es nur irgendwie möglich ist, die englische Schreibweise - also Formula - zu benutzen.
Sie können damit gewährleisten, dass Ihre Mappe zumindest in dieser Beziehung international stabil bleibt.
(Es gibt Situationen, bei denenen man dies nicht gewährleisten kann aber dies ist ein anderes Thema)
Zwar erfordert dies ein bisserl mehr Arbeit aber es lohnt sich.
Die englischen Formelnamen können Sie ganz leicht ermitteln, sehen Sie mal oben in Siehe auch.
Kurzer Ausflug R1C1
Sowohl Formula als auch FormulaLocal gibt es noch in einer anderen Version, nämlich
FormulaR1C1 bzw. FormulaR1C1Local
Diese Varianten erwarten ganz einfach Zellbezüge in der R1C1 Schreibweise (Z1S1)
Es gibt Situationen, in welchen ich diese Schreibweise benötige - wenn ich mich recht erinnere, kann dies z.B. bei Formeln für Bedingte Formatierungen notwendig werden aber dies herauszufinden überlasse ich Ihnen.
Sowohl Formula als auch FormulaLocal gibt es noch in einer anderen Version, nämlich
FormulaR1C1 bzw. FormulaR1C1Local
Diese Varianten erwarten ganz einfach Zellbezüge in der R1C1 Schreibweise (Z1S1)
.Range("e2").FormulaR1C1Local = "=SUMME(ZS(-3):Z(3)S(-2))"
.Range("e3").FormulaR1C1 = "=SUM(RC[-3]:R[3]C[-2])"
Tatsächlich ist das Ergebnis das Gleiche.
Es gibt Situationen, in welchen ich diese Schreibweise benötige - wenn ich mich recht erinnere, kann dies z.B. bei Formeln für Bedingte Formatierungen notwendig werden aber dies herauszufinden überlasse ich Ihnen.
Verknüpfungen in Formeln:
Nehmen wir an, Sie wollen in der Zelle E6 folgende Formel stehen haben und das Ergebnis soll so aussehen:
Per Hand ist dies leicht einzugeben, aber wenn wir es per Code eingeben wollen, stoßen wir auf ein Problem, nämlich die Anführungszeichen.
Da ich meine Stringzuweisung mit einem Anführungszeichen beginne, um VBA klar zu machen, dass eine Zeichenkette folgt, würde spätestens bei dem ersten Anführungszeichen in unserer Formel VBA denken, dass die Stringzuweisung zu Ende ist.
Aber dafür gibt es eine ganz einfache Lösung:
Will ich Anführungszeichen in einem String benutzen, muss ich diese DOPPELN
Unsere Formelzuweisung sieht demnach in der FormulaLocal-Schreibweise so aus:
(Wieder für unsere Prozedur FormelSchreiben1, einfach dort hinter den anderen Formelzuweisungen, vor End With einfügen)
Nehmen wir an, Sie wollen in der Zelle E6 folgende Formel stehen haben und das Ergebnis soll so aussehen:
| Tabelle1 | ||||||
| ||||||
Da ich meine Stringzuweisung mit einem Anführungszeichen beginne, um VBA klar zu machen, dass eine Zeichenkette folgt, würde spätestens bei dem ersten Anführungszeichen in unserer Formel VBA denken, dass die Stringzuweisung zu Ende ist.
Aber dafür gibt es eine ganz einfache Lösung:
Will ich Anführungszeichen in einem String benutzen, muss ich diese DOPPELN
Unsere Formelzuweisung sieht demnach in der FormulaLocal-Schreibweise so aus:
(Wieder für unsere Prozedur FormelSchreiben1, einfach dort hinter den anderen Formelzuweisungen, vor End With einfügen)
.Range("E6").Formula = "=""Die Summe ist: "" & Sum(B2:C5) "
Dynamische Formeln erstellen
Es gibt Situationen, in denen ich Variable in meine Formelerstellung mit einbringen will.
Sei es um einen ermittelten Bereich in die Formel zu schreiben oder oder oder ...
In solchen Fällen muss ich meinen Formelstring durch Verkettungen erstellen.
Ein simples Beispiel für unsere Tabelle (ohne größeren sittlichen Nährwert):
Es gibt Situationen, in denen ich Variable in meine Formelerstellung mit einbringen will.
Sei es um einen ermittelten Bereich in die Formel zu schreiben oder oder oder ...
In solchen Fällen muss ich meinen Formelstring durch Verkettungen erstellen.
Ein simples Beispiel für unsere Tabelle (ohne größeren sittlichen Nährwert):
Public Sub FormelnSchreiben2()
Dim oBlatt As Worksheet
Dim iStartReihe As Long, iEndReihe As Long
Dim iStartSpalte As Integer, iEndSpalte As Integer
iStartReihe = 2
iStartSpalte = 2
iEndReihe = 5
iEndSpalte = 3
Set oBlatt = ThisWorkbook.Worksheets("Tabelle1")
With oBlatt
oBlatt.Range("e2").Formula = _
"=Sum(" & .Range(.Cells(iStartReihe, iStartSpalte), _
.Cells(iEndReihe, iEndSpalte)).Address(0, 0) & ")"
oBlatt.Range("e3").Formula = _
"=Sum(" & .Range(.Cells(iStartReihe, iStartSpalte), _
.Cells(iEndReihe, iEndSpalte)).Address(1, 1) & ")"
End With
Set oBlatt = Nothing
End Sub
Hier ermitteln wir den Bereich für den unsere Summe gelten soll "dynamisch".
Diese Art der Rangezuweisung sollte Ihnen geläufig sein.
Vielleicht ist die Address - Geschichte noch nicht ganz klar: 0,0 heißt einfach relativ sowohl Reihe als auch Spalte und 1,1 absolut. Sie sehen es ja an der Ausgabe.
Wir wollen uns aber eine der beiden Zuweisungen etwas genauer anschauen:
"=Sum(" & .Range(.Cells(iStartReihe, iStartSpalte), .Cells(iEndReihe, iEndSpalte)).Address(1, 1) & ")"
Anhand der farblichen Aufteilung sollte es klar werden.
Das war doch auch leicht, oder?
Diese Art der Rangezuweisung sollte Ihnen geläufig sein.
Vielleicht ist die Address - Geschichte noch nicht ganz klar: 0,0 heißt einfach relativ sowohl Reihe als auch Spalte und 1,1 absolut. Sie sehen es ja an der Ausgabe.
Wir wollen uns aber eine der beiden Zuweisungen etwas genauer anschauen:
"=Sum(" & .Range(.Cells(iStartReihe, iStartSpalte), .Cells(iEndReihe, iEndSpalte)).Address(1, 1) & ")"
Anhand der farblichen Aufteilung sollte es klar werden.
Das war doch auch leicht, oder?
Zum Schluss:
Für die Matrixler:
Um eine Matrixformel zu schreiben, benötige ich die FormulaArray Eigenschaft.
Der Code hierfür in unserer Prozedur:
Für die Matrixler:
Um eine Matrixformel zu schreiben, benötige ich die FormulaArray Eigenschaft.
| Tabelle1 | ||||||
| ||||||
.Range("e7").FormulaArray = "=SUM((B2:B5) * (C2:C5))"
Weitere Artikel der Gruppe: Häufige Fragen Aus Excel VBA
Nach oben
rechte seite