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:
Tabelle1
 ABCDEF
1      
2 15 36 
3 26   
4 37   
5 48   
6      
7      
Formeln der Tabelle
E2 : =SUMME(B2:C5)
 
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:



' **************************************************************
'  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.

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.

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)
.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. sm3 (1K)
 Ihre Meinung zu Online Excel Geben Sie uns ein Feedback
Verknüpfungen in Formeln:

Nehmen wir an, Sie wollen in der Zelle E6 folgende Formel stehen haben und das Ergebnis soll so aussehen:
Tabelle1
 E
6Die Summe ist: 36
Formeln der Tabelle
E6 : ="Die Summe ist: " & SUMME(B2:C5)
 
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)
.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):


 
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?

Zum Schluss:

Für die Matrixler:
Um eine Matrixformel zu schreiben, benötige ich die FormulaArray Eigenschaft.
Tabelle1
 E
770
Formeln der Tabelle
E7 : {=SUMME((B2:B5) * (C2:C5))}

Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
 
Der Code hierfür in unserer Prozedur:
.Range("e7").FormulaArray = "=SUM((B2:B5) * (C2:C5))"


Weitere Artikel der Gruppe: Häufige Fragen Aus Excel VBA
Nach oben