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 Programmier­sprachen 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