Click here to Skip to main content
13,259,771 members (55,539 online)
Click here to Skip to main content
Add your own
alternative version


32 bookmarked
Posted 19 Apr 2007

Dynamic formula calculator/evaluator in VB.NET

, 19 Apr 2007
Rate this:
Please Sign up or sign in to vote.
This class will help you to calculate/evaluate formulas based on a string which could be changed at run-time.


This class will help you to calculate/evaluate formulas based on a string which could be changed at run-time. For example, in a "Management Information System" or in "Workflow Definition Rules", you can store the formula of a decision and the related data in a data storage and use it in your business logic.


I was searching to find out a way to calculate a formula based value in which the parameters come from a data storage. For example, my customer wants to write/change his formula and calculate the value based on it:

Value = Round((P1+P3)/P2)

Using the code

I complete the job using MSScriptControl.ScriptControlClass; it helps me to evaluate the String using VB. Here is the complete code:

Public Class Evaluator

    Dim scriptControl As New MSScriptControl.ScriptControlClass
    Private _Formula As String
    Private _VariablesPreffix As String
    Private _Operators As ArrayList
    Private _CorrectedFormula As String
    Private _FormulaVariables As ArrayList
    Private _ParseCondition As ParseCondition = _

    Public ReadOnly Property Formula() As String
        Return Me._Formula
    End Get
    End Property

    Public ReadOnly Property VariablesPreffix() As String
        Return Me._VariablesPreffix
    End Get
    End Property

    Private Sub PreEvaluate(ByVal keyCode As String, ByVal keyValue As String)
        Me._Formula = Me._Formula.Replace(keyCode.ToUpper, keyValue)
    End Sub

    Private Sub PreEvaluate(ByVal keyValuesDictionary As Hashtable)
        For Each keyCode As String In keyValuesDictionary.Keys
            Me.PreEvaluate(keyCode, keyValuesDictionary(keyCode))
    End Sub

    Public Function Evaluate(ByVal values As Hashtable) As Double
        Dim tmpFormula As String = Me._Formula
        For Each var As String In Me._FormulaVariables
            Select Case var.ToLower
            Case "abs", "round", "fix", "sin", "exp", "cos", _
                 "int", "atn","tan","sqr", "sgn", "log"
            Case Else
                Dim index As Integer = CInt(var.Replace(Me._VariablesPreffix, ""))
                If Not values(index) Is Nothing Then
                    tmpFormula = tmpFormula.Replace(var, values(index))
                    If Me._ParseCondition = ParseCondition.RaiseErroForUndefinedVariable Then
                        Throw New Exception("Undefined variable!" & vbCrLf & _
                              "Base formula: " & Me._Formula & vbCrLf & _
                              "Corrected formula: " & Me._CorrectedFormula & _
                              vbCrLf & "On variable: " & var)
                        tmpFormula = tmpFormula.Replace(var, 0)
                    End If
                End If
            End Select
            Return CDbl(scriptControl.Eval(tmpFormula))
        Catch ex As Exception
            Return -1
        'Throw New Exception(tmpFormula, ex)
        End Try
    End Function

    Public Sub New(ByVal formula As String, ByVal variablePreffix As String, _
                   ByVal parseCondition As ParseCondition, _
                   Optional ByVal preEvaluateKeyValuesDictionary As Hashtable = Nothing)
        scriptControl.Language = "vbscript"
        Me._Formula = formula
        Me._VariablesPreffix = variablePreffix
        Me._ParseCondition = parseCondition
        If Not preEvaluateKeyValuesDictionary Is Nothing Then
        End If
    End Sub

    Private Sub InitializeOperators()
        Me._Operators = New ArrayList
    End Sub

    Private Sub InitializeFormula()
        Me._CorrectedFormula = Me._Formula.Replace(" ", "")
        Me._FormulaVariables = New ArrayList
        Dim arrOperators() As Char
        Dim variables() As String = _
            Me._CorrectedFormula.Split(CType(Me._Operators.ToArray(GetType(Char)), Char()))
        For Each var As String In variables
            var = var.Trim
            If var.Length > 0 AndAlso Not IsNumeric(var) Then
                If Not IsNumeric(var.Replace(Me._VariablesPreffix, "")) Then
                    Select Case var.ToLower
                     Case "abs", "round", "fix", "sin", "exp", _
                          "cos", "int", "atn","tan", "sqr", "sgn", "log"
                    Case Else
                        Throw New Exception("Wrong formula!" & vbCrLf & _
                                            "Base formula: " & Me._Formula & _
                                            vbCrLf & "Corrected formula: " & _
                                            Me._CorrectedFormula & vbCrLf & _
                                            "On variable: " & var)
                    End Select
                End If
            End If
    End Sub

    Public ReadOnly Property Operators() As ArrayList
        Return Me._Operators
    End Get
    End Property

    Public ReadOnly Property ParseCondition() As ParseCondition
        Return Me._ParseCondition
    End Get
    End Property

End Class

Public Enum ParseCondition
End Enum

Now, you can simply use it like:

Dim calculator As New Evaluator("Round((P1+P3)/P2)", "P", _
Dim parameters As New Hashtable
parameters.Add(1, 12)
parameters.Add(2, 24)
parameters.Add(3, 6)
Dim value As Double = calculator.Evaluate(parameters) 


  1. You should define the Parameters Prefix to be recognized (like "P").
  2. Do not use the predefined functions as the Parameters Prefix.
  3. If you do not assign a named parameter, it will assign zero as the parameter's value.
  4. If an error occurs, it will return -1.
  5. If you want to add more functions, you should add in Red lines of code!
  6. You can add more additional parameters before evaluation.
  7. You can also add your own custom variable before calculating in the PreEvaluate method.


I'm searching for another way to implement this with better performance...


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Amir Pournasserian
President Momentaj Inc.
Canada Canada
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMSScriptControl Pin
Mauricio Hirota16-Aug-10 3:26
memberMauricio Hirota16-Aug-10 3:26 
Generalhi i have a cuestion, Pin
krakxp23-Sep-08 21:53
memberkrakxp23-Sep-08 21:53 
QuestionSupported functions Pin
Daniel Kamisnki29-Mar-08 6:11
memberDaniel Kamisnki29-Mar-08 6:11 
GeneralRe: Supported functions Pin
Amir Pournasserian29-Mar-08 11:10
memberAmir Pournasserian29-Mar-08 11:10 
GeneralRe: Supported functions Pin
Daniel Kamisnki29-Mar-08 11:28
memberDaniel Kamisnki29-Mar-08 11:28 
Generalnothing special Pin
marko jovanovic19-Jun-07 21:12
membermarko jovanovic19-Jun-07 21:12 
GeneralRe: nothing special Pin
Amir Pournasserian29-Mar-08 11:04
memberAmir Pournasserian29-Mar-08 11:04 
QuestionMSScriptControl.ScriptControlClass in 3.0? Pin
tracyding22-May-07 13:55
membertracyding22-May-07 13:55 
AnswerRe: MSScriptControl.ScriptControlClass in 3.0? Pin
Amir Pournasserian23-May-07 0:57
memberAmir Pournasserian23-May-07 0:57 
Generalexcelent discource, Pin
m_s_rezaei24-Apr-07 2:08
memberm_s_rezaei24-Apr-07 2:08 
GeneralRe: excelent discource, Pin
Amir Pournasserian24-Apr-07 3:00
memberAmir Pournasserian24-Apr-07 3:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171114.1 | Last Updated 20 Apr 2007
Article Copyright 2007 by Amir Pournasserian
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid