executing an equation stored as a text

[SunWuKung]

I am storing values on scales.
I would like to store an equation which would use those values and return the result. I am not sure how to do this.
Here is an example:
I stored the values on a number of scales:
S1 = 3
S2 = 5
S3 =10

Now the user can specify an equation which I store as text in a different table. I will need to check if the equation is correctly formatted but for now lets suppose it is. Say the equation is the following:
(S1*S2)*5-S3^2*6

I would like a form to display the result of:
3*5*5-10^2*6 = -525

Unfortunately I need a generic solution as I will not know either the names or the number of the scales used.

Thanks for the help.
SWK

 

[pdx_man]

This will not be an easy answer, believe me. We had to write something like this in a LISP class I took years ago. We had to parse the equation text down to the simplest components in a Btree memory stack based on the parenthesis, and then by the primary order, substituting the values in as you go. I would do a search on the net for a library or something that may do this for you.

I don't believe Gizmo fully understood what you are asking, or maybe I'm not, but you need to be able to pass any equation with any number of variables ... right?

So, the same function would evaluate:

(((a1+a2/a3)-(a2+a4^a5*(a4/a1))+a3)/a6)
and evaluate
(a1/a2+5)*a3

First, you gotta see if even the equation makes sense and disallow things like:

a2/*a1
or
((a1*a3+a2) + A3*a2))

Is this what you're after?

 

[SunWuKung]

That is right, I need to be able to pass any number of variables to the equation.
pdx_man - Does it really need to be that difficult?
Let's not worry about syntax checking now.

I could perhaps do some symple text manipulation: put the variable values in a recordset than replace all a1, a2 to rst!a1, rst!a2 etc. than use the eval function.

 

[ChrisO]

A very simple answer:-

'   There is no simple answer. 
'
'   But keeping to the immediate question of passing unknown number of arguments;   
'
'  Passing multiple variables is perhaps better done like this.
Public Function EvaluateFormula(ParamArray vntArgList() As Variant) As Double
    Dim lngArgCount As Long
    Dim WhatEver    As Double
                              
    For lngArgCount = LBound(vntArgList) To UBound(vntArgList)
        WhatEver = WhatEver + vntArgList(lngArgCount)
    Next lngArgCount
                              
    EvaluateFormula = WhatEver
    
End Function

Not so simple…

Regards,
Chris.

 

[pdx_man]

pdx_man - Does it really need to be that difficult?
Let's not worry about syntax checking now.

Short answer ... yes

Syntax checking (parsing) will have to be a requirement as it has to know the order of operations.

(((a1+a2/a3)-(a2+a4^a5*(a4/a1))+a3)/a6)

In the example I gave above, which calculation do you evaluate first? ... and second ... and ...

It's one thing for a person to look at it and figure it out, but an entirely different kettle of fish having a computer do it.

 

[SunWuKung]

This is how I imagine it now.
You don't think this could work?

EqString="(((a1+a2/a3)-(a2+a4^a5*(a4/a1))+a3)/a6)"
For each variable (I can scan the string for possible variable names)
Replace each variable with its value as a string operation
Next

Debug.Print EqString 'eg. "(((1+2/3)-(2+4^5*(4/1))+3)/6)"
Debug.Print Eval(EqString) 'eg. -682.222222222222

On Error
'I can trap the error of the equation here

 

[pdx_man]

Hey, I've never used that one before ... Eval. Well, it makes sense to have that as a function. I try to learn something new everyday. I'm sure Pat can tell me when it became part of the VB syntax. As I mentioned, we had to churn this one out years ago ... anyway ... Then all you have to do is separate your input parameters and evaluate your function:

Public Function RetFunc(TheFunc As String, TheVals As String) As Variant
Dim VarArray(100) As Variant
Dim i, j As Byte
On Error GoTo StringParsed
 
    i = 0
    VarArray(i) = Left(TheVals, InStr(TheVals, ",") - 1)
    TheVals = Mid(TheVals, InStr(TheVals, ","))
    While InStr(TheVals, ",") > 0
        i = i + 1
        TheVals = Mid(TheVals, 2)
        VarArray(i) = Left(TheVals, InStr(TheVals, ",") - 1)
        TheVals = Mid(TheVals, InStr(TheVals, ","))
    Wend
    
    For j = 0 To i
        TheFunc = Replace(TheFunc, "A" & j, VarArray(j))
    Next j
    
    RetFunc = Eval(TheFunc)
    
    Exit Function

StringParsed:
    If Err.Number = 5 Then
        VarArray(i) = TheVals
        Resume Next
    End If
    
End Function

with:
TheAnswer: RetFunc([FormulaField],[ValueListField])

*** EDIT ***

Just to mention that this code is very primative in the sense that it does not validate the formula and the user must not include commas in their value list. If the user wants to put in a value of 13,452, they must put in 13452. Nor does it validate the number of parameters passed vs the number of variables in the formula. I'll let someone else rev it up to a good production level code.

 

[Cosmos75]

Check out JonK's post in this thread.

 

[pdx_man]

That example does work well with a fixed number of variables, but, as I understand it, SunWuKung needs to be able to have any number of variables so as to evaluate:

a0*a1

as well as

a0 + a1 * a2

I believe my example will do this.

 

[Cosmos75]

Good job, pdx_man!

pdx_man,

Yes, you are correct. I forgot to mention that. Thought that it might be good as an example for when you have a fixed number variables but since your method can handle multiple variables, it is the way to go for SunWuKung.

This thread and pdx_man's code is a keeper!