Formeln validieren
Formeleingaben zu überprüfen, ist nicht einfach, weil viele verschiedene Symbole darin vorkommen können und die Anordnung sehr variabel ist. Außerdem müssen wir noch unterscheiden, ob darin eine, zwei oder noch mehr Variablen vorkommen dürfen.
Beispiele:
y = x^3 + x^2 + 5
y = 1/x – 5
y = sin(x)
y = 3
z = sin(x) + cos(y)
z = sin(x + y) + Abs(x)
Ob eine bestimmte Formel zulässig ist, lässt sich im Prinzip mit Hilfe einer Grammatik zuverlässig überprüfen. Die Grammatik beschreibt, nach welchen Regeln Formeln gebildet werden dürfen. Aus diesen Regeln lassen sich dann Prozeduren ableiten, welche im Einzelfall prüfen können, ob die Formel zulässig ist.
Dies ist ein aufwendiges Verfahren, das spezielle Kenntnisse erfordert. Manche Programmiersprachen enthalten auch vorgefertigte Bibliotheken, welche die Programmierung einer solchen Überprüfung erleichtern. VBA gehört leider nicht zu diesen Programmiersprachen.
Wir können Formeln trotzdem mit einem vertretbaren Aufwand überprüfen. Dabei machen wir uns den Umstand zunutze, dass Excel selbst Formeln auswertet, die in seine Zellen eingegeben werden. Wir müssen nur Excel dazu bringen, eine zu überprüfende Formel mit konkreten Werten auszurechnen. Gelingt dies, so ist alles in Ordnung, läuft es aber schief, ist dies ein Hinweis für einen Fehler. Bei diesem Vorgehen müssen wir allerdings verhindern, dass ein auftretender Fehler unser eigenes Programm abstürzen lässt.
Der Wertebereich spielt eine Rolle
Zusätzlich sollten wir berücksichtigen, dass nicht jede Formel für alle Werte der Variablen ein sinnvolles Ergebnis liefern kann. Betrachten wir die bereits oben aufgeführte Funktion
y = 1/x - 5
Sie ist fehlerlos formuliert, liefert aber für den Wert x = 0 kein sinnvolles Ergebnis (Division durch 0). Geschieht eine solche Division im Verlauf der Programmdurchführung, so führt dies zu einer Fehlermeldung des VBA-Systems mit anschließendem Programmabbruch.
Es ist deshalb sinnvoll, zusammen mit der generellen Zulässigkeit der Formel auch zu prüfen, ob eine Division durch 0 droht und, falls dies der Fall ist, ob die Formel diese Division „verträgt“.
Die Validierungsfunktion fvonxyOK
Die folgende Validierungsfunktion fvonxyOK validiert eine Formel für eine Funktion z = f (x, y). Sie liefert True, wenn die Formel zulässig ist und (mit hinreichender Wahrscheinlichkeit) im gewünschten Bereich korrekte Werte liefert. Ist dies nicht der Fall, liefert sie False.
Die Formel wird als String entgegengenommen (Parameter f), wobei die linke Seite einschließlich dem „=“ weggelassen ist. Für die Funktion z = sin(x + y) würde f also den Wert „sin(x + y)“ haben.
Vier weitere Parameter (xmin, xmax, ymin, ymax) geben den vorgesehenen Wertebereich der unabhängigen Variablen an. Der sechste Parameter, Testb, ist ein Bereich (Range) aus einem Tabellenblatt, den die Validierungsfunktion als Spielwiese zum Testen der Formel benötigt. Testb besteht aus drei nebeneinander liegenden Zellen. Die linke nimmt jeweils den Wert von x auf, die mittlere den von y und die rechte die Formel und damit den aus x und y resultierenden Wert von z.
Public Function fvonxyOK(ByVal f As String, _
ByVal xmin As Double, _
ByVal xmax As Double, _
ByVal ymin As Double, _
ByVal ymax As Double, _
ByVal Testb As Range)
Dim z As Double
Dim ok As Boolean
‘in der Formel werden x und y durch Bezüge ersetzt
f = "=" & Replace(Replace(f, "x", "(R[0]C[-2])"), "y", "(R[0]C[-1])")
ok = True ‘wenn im Folgenden nichts passiert, ist die Formel ok
With Testb
.Cells(1, 3).Formula = f
.Cells(1, 1) = xmin 'ab hier wird getestet
.Cells(1, 2) = ymin
On Error GoTo schlechtGelaufen
z = .Cells(1, 3)
.Cells(1, 1) = xmax
.Cells(1, 2) = ymax
On Error GoTo schlechtGelaufen
z = .Cells(1, 3)
If xmin <= 0 And xmax >= 0 Then
.Cells(1, 1) = 0
.Cells(1, 2) = ymin
On Error GoTo schlechtGelaufen
z = .Cells(1, 3)
End If
If ymin <= 0 And ymax >= 0 Then
.Cells(1, 1) = xmin
.Cells(1, 2) = 0
On Error GoTo schlechtGelaufen
z = .Cells(1, 3)
End If
End With
fvonxyOK = ok 'diese Stelle wird nur erreicht, wenn die Tests fehlerlos
waren
Exit Function
schlechtGelaufen: 'Ansprungstelle bei Fehlern
fvonxyOK = False
End Function
Beachten Sie, dass die Funktion auch dann Alarm schlägt, d.h. False liefert, wenn einer der für die Bereichsgrenzen errechneten Funktionswerte den Datentyp Double sprengt, denn das errechnete Ergebnis wird der Variablen z vom Typ Double zugewiesen. Falls dies misslingt, wird zur Marke schlechtGelaufen gesprungen.
Wie kann man fvonxyOK aufrufen?
Die folgende kleine Prozedur soll zeigen, wie die Funktion fvonxyOK aufgerufen werden kann:
Public Sub testfvonxy()
Dim w as Worksheet
Set w = Worksheets("Tabelle3")
MsgBox fvonxyOK("sin(x) + 1/y", 0, 10, 0, 10, Range(w.Cells(1, 1), w.Cells(1, 3)))
End Sub