To calculate a formula Expression

[riya]

HI,
How to calculate a formula (i mean the o/p of a formula)which is stored in the sql(backend) as a character field.
Fr ex: (A*A)+(B*B)+(2*A*B)-- data of a column
how to convert this expr: to a integer o/p.All answers are appreciated.

Thanx

 

[chiph]

There's no easy way to do it in VB (you would have to parse the expression and do your own variable replacements). But you can call out to Excel and use it's Eval() function.

Chip H.

 

[rvBasic]

You can dynamically evaluate expression during run time by using vb's Scripting facilities. First you have to add the Scripting Control to your project: Project Menu -> References -> Select Script Control
Then set the Scripting language to VBScript.

The following code exerpt supposes you know the name of the variables used in the expression: A and B. If you don't know them at design time, then you will probably have to extract the names by parsing the expression at runtime.

The code snippet accepts the values of the variables through an InputBox These values are assigned by the ExecuteStatement to the variable names.

The rest is a piece of cake: Ask for an expression and evaluate it using the Eval method e.g. enter a^2+b^2+2*a*B in another inputbox and the result will show up. (No checking on the validity of the expression is done). The above expression deliberately mixes
upper- and lowercase letters: it doesn't matter for VBScript.

Dim objSc As ScriptControl
Set objSc = New ScriptControl
objSc.Language = "VBScript"

'Dynamically assign values to variables
objSc.ExecuteStatement "A=" & InputBox("Enter First Value:", , 0)
objSc.ExecuteStatement "B=" & InputBox("Enter Second Value:", , 0)

'Ask for and Evaluate the expression
Dim dblResult As Double
dblResult = CDbl(objSc.Eval(InputBox("Enter a Valid Expression" & _
"with Two Variables A and B&quot))

Debug.Print dblResult

It should be well understood that evaluating expressions at runtime involves a heavy performance penalty: if possible avoid it! _________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]