VB - Evaluate a mathematical expression of a string

May 2017



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


Published by deri58. Latest update on November 21, 2011 at 12:24 PM by deri58.
This document, titled "VB - Evaluate a mathematical expression of a string," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).