Mit der Methode Evaluate in VBA Texte berechnen

Im letzten Beitrag habe ich dir gezeigt, wie mit dem Excel4-Makro AUSWERTEN in Excel mit Formeltexten gerechnet werden kann.

In VBA geht das natürlich auch. Hier wird die Methode EVALUATE verwendet. Darum soll es in diesem Beitrag gehen.

Um die Funktion zu erklären, verwende ich die Beispiele aus dem AUSWERTEN-Beitrag. Damit hast du gleichzeitig den Vergleich dazu.

Beispiel 1

Im ersten Beispiel galt es, diesen Formeltext zu berechnen. Das Ergebnis soll in F19 stehen.

Mit diesem Makro, das du im VBA-Editor in ein Modul schreibst, berechnest du den Text.

Sub Evaluate1()
Range(“F19”)=Evaluate((12+(24*3))/3)
End Sub

Hier hast du den Formeltext zwischen die Klammern nach Evaluate geschrieben. Das Makro berechnet das Ergebnis richtig.

Aber: Der Formeltext ist nicht dynamisch. Willst du mit anderen Werten rechnen, musst du das Makro anpassen.

Beispiel 2

Im Beispiel 2 verwendest du eine Excel-Funktion. Du willst zwei Zahlen addieren, es bietet sich die Funktion SUM an.

Sub Evaluate2()
Range(“F37”)=Evaluate(“SUM(B37:C37)”)
End Sub

Beachte: Die Funktion (englische Schreibweise) wird innerhalb der Klammern in Anführungszeichen gesetzt.

Veränderst du jetzt einen oder beide Ausgangswerte und startest das Makro erneut, wird neu berechnet.

Beispiel 3

Aus der kleinen Tabelle soll unter Verwendung der INDEX-Funktion (als Text) der Wert für Dresden im zweiten Monat abgerufen werden.

Der Formeltext lautet:

Mit diesem Makro wird die Aufgabe gelöst:

Sub Evaluate3()
Range(“F59”)=Evaluate(“INDEX(C53:E55,3,2)”)
End Sub

Beachte: VBA trennt die Argumente in den Funktionen mit einem Komma, nicht mit dem Semikolon. Das Ergebnis ist richtig.

Beispiel 4

Jetzt gehst du einen Schritt weiter. In Abhängigkeit von der Zahl in B74 soll das Makro mit Evaluate das Beispiel 2 oder das Beispiel 3 rechnen.

Das Makro könnte z.B. so aussehen:

Sub Evaluate4()
If Range(“B74”)=1 Then
Range(“F74”)=Evaluate(“SUM(B37:C37)”)
End If
If Range(“B74”)=2 Then
Range(“F74”)=Evaluate(“INDEX(C53:E55,3,2)”)
End If
End Sub

Entscheidungen lassen sich in VBA auch auf anderen Wegen umsetzen, hier habe ich dazu geschrieben.

Beispiel 5

Kann Evaluate auf eine Variable zugreifen? Probiere es aus und schreib diesen Code ins Modul:

Sub Evaluate5()
Dim varText As Variant
varText=”INDEX(C53:E55,3,2)
“Range(“F95”)=Evaluate(varText)
End Sub

Das hat funktioniert.

Beispiel 6

Bau jetzt noch einen weiteren Schritt ein. Der Variablen soll in Abhängigkeit von der Zahl in B109 eine der Formeln aus Beispiel 4 zugewiesen werden. Dafür verwendest du diesmal die Methode Select Case.

Sub Evaluate6()
Dim varText As Variant
Dim bytWahl As Byte
bytWahl=Range(“B109”).Value
Select Case bytWahl
Case=1
varText=”SUM(B37:C37)”
Case=2
varText=”INDEX(C53:E55,3,2)”
End Select
Range(“F109”)=Evaluate(varText)
End Sub

Na bitte, so geht’s auch.

Beispiel 7

Im letzten Beispiel soll Evaluate auf namentlich hinterlegte Formeln zugreifen und rechnen.

Zelle C131 erhält den Namen “Formel1”, C132 den Namen “Formel2”.

In B137 schreibst du die Zahl, die zur Unterscheidung der Formeln führt, also 1 oder 2.

Du deklarierst zwei Variablen. strFormula soll über Verkettung den Formelnamen bilden.

varErg schließlich soll das Rechenergebnis aufnehmen.

Sub Evaluate7()
Dim strFormula As String
Dim varErg As Variant
strFormula=”Formel”&Range(“B137”).Value
varErg=Application.Evaluate(strFormula)
Range(“F137”)=Evaluate(varErg)
End Sub

Ist es dir aufgefallen? Der Code enthält zwei Zeilen, in denen Evaluate steht. Verwendest du den Code so

Sub Evaluate7()
Dim strFormula As String
Dim varErg As Variant
strFormula = “Formel” & Range(“B137”).Value
varErg = Application.Evaluate(strFormula)
Range(“F153”) = varErg
End Sub

erhältst du nur den Formeltext (F153), den du durch Verkettung berechnet hast. In einem zweiten Schritt musst du diesen Text noch berechnen, deshalb nochmal Evaluate.