VB - Evaluate a mathematical expression of a string

December 2016



Intro


In VBA the Evaluate function takes as argument a formula set out in a alphanumeric variable and return the result in an alphanumeric variable.
In VB, this function is missing and you must implement it by yourself.

This demo supports simple calculations +, -, *, and /, and parentheses.

The demo works the same way as the Evaluate function.


This sub is used only for testing ..

Sub TestCalcul() 
Dim A As String 
Dim Ret As String 
    A = "(((3*(12.223+ 15)) - 7)*21)/7" 
    Ret = Evaluer(A) 
    'Debug.Print Ret 
    '= 224.007 
    A = "((123.32/2.67)*6)+2127.34" 
    Ret = Evaluer(A) 
    '=2404.46359550562 
    'Debug.Print Ret 
End Sub 

The code


Function Evaluer(ByVal Txt As String) As String 
Dim i As Integer, oNB As Integer, fNB As Integer 
Dim P1 As Integer, P2 As Integer 
Dim Buff As String 
Dim T As String 
'Pour les calculs y faut un point à la place de la virgule 
    Txt = Replace(Txt, ",", ".") 
'Voir s'il y a des ( 
    For i = 1 To Len(Txt) 
        If Mid(Txt, i, 1) = "(" Then oNB = oNB + 1 
    Next i 
'S'il y a des ( (ouvrantes), voir si elle sont validée par  des ) (fermantes) 
    If oNB > 0 Then 
        For i = 1 To Len(Txt) 
            If Mid(Txt, i, 1) = ")" Then fNB = fNB + 1 
        Next i 
    Else 
'Pas de parenthèse, Evalue directement le calcul 
        Evaluer = EvalueExpression(Txt) 
        Exit Function 
    End If 
    If oNB <> fNB Then 
'Les parenthèses ne sont pas concordantes, mettre  message erreur parenthèse 
        Exit Function 
    End If 
     
    While oNB > 0 
'recherche la dernière parenthèse ouvrante 
        P1 = InStrRev(Txt, "(") 
'Recherche la parenthèse fermante de l'expression 
        P2 = InStr(Mid(Txt, P1 + 1), ")") 
'Evalue l'expression qui est entre parenthèses 
        Buff = EvalueExpression(Mid(Txt, P1 + 1, P2 - 1)) 
'Remplacer l'expression par le résultat et supprimer les parenthèses 
        Txt = Left(Txt, P1 - 1) & Buff & Mid(Txt, P1 + P2 + 1) 
        oNB = oNB - 1 
    Wend 
'plus de parenthèse, évaluer la dernière expression 
    Evaluer = EvalueExpression(Txt) 

End Function 
Function EvalueExpression(A As String) As String 
Dim T As Integer, S As Integer 
Dim B As String, i As Integer, C As Boolean 
Dim c1 As Double, c2 As Double, Signe As Integer 
Dim R As String, Fin As Boolean, z As Integer 
     
    'enlever les espace 
    A = Replace(A, " ", "") 
     
    While Not Fin 
        For i = 1 To Len(A) 
            T = Asc(Mid(A, i, 1)) 
            If T < 48 And T <> 46 Or i = Len(A) Then 
                If C Then 'évalue 
                    If i = Len(A) Then 
                        c2 = Val(Mid(A, S)) 
                    Else 
                        c2 = Val(Mid(A, S, i - S)) 
                    End If 
                    R = Str(CalculSimple(c1, c2, Signe)) 
                    If i = Len(A) Then 
                        Fin = True 
                    Else 
                        A = Trim(R & Mid(A, i)) 
                        C = False 
                    End If 
                    Exit For 
                Else 'sépare le 1er chiffre 
                    c1 = Val(Left(A, i - 1)) 
                    Signe = T 
                    S = i + 1 
                    C = True 
                End If 
            End If 
        Next i 
    Wend 
    'remplacer l'expression par le résultat 
    EvalueExpression = Trim(R) 
End Function

You can add different types of calculations in the below function:

Function CalculSimple(n1 As Double, n2 As Double, Signe As Integer) As Double 
    Select Case Signe 
    Case 43 ' + 
        CalculSimple = n1 + n2 
    Case 45 ' - 
        CalculSimple = n1 - n2 
    Case 42 ' * 
        CalculSimple = n1 * n2 
    Case 47 ' / 
        CalculSimple = n1 / n2 
    'Ici, ajouter d'autre calcul... 
    End Select 
End Function
  • Note: In order for it to be entirely consistent with a calculator, you must first evaluate the *adn / function and only then the + and - ones.
  • Example 3+5*7
  • A calculator process it as follows 5*7 = 35 + 3 = 38
  • But with this function: 3+5=8 *7 = 56
  • You can modify the "EvalueExpression" or enter the calculation as 3+(5*7)



Related :

This document entitled « VB - Evaluate a mathematical expression of a string » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.