Interpreting formulas entered at run-time 5

[GPerk]

I want to let the user of my program to type a math formula into a text box. This formula would be used to modify hundredss of thousands of data records. Records to be modified would be selected based on another formula that would give a true or false result. I envision this as a selection textbox (IF) and a operation textbox (THEN). A simple example of what this might look like is :
IF [ R > S * 1.10 AND T + U < V ] THEN [ R = R + T * G ]
where [ ] denote textboxes and the letters denote fields in the data records. If the first box evaluates to TRUE then the data would be modified according to the formula in the second textbox. Then the next record would be processed.

I have written a program to do this by (1) transforming the formulas to Reverse Polish Notation and then (2) executing the RPN for each record. But step 2 is implemented using an interpretive process and is too slow even on a fast processor, especially when the number of records approaches one million.

My question is: Is it possible in VB6 to generate a machine language fragment of code at run-time and execute it?
(I remember doing something like this decades ago on the TRS-80 microcomputer using BASIC.)

Another possible solution is to pass the formulas to an Excel object which would calculate the results and return them to the VB program. But I suspect this would not be any faster.
I really would like to keep all the processing within VB6 because there a lot of other stuff in the overall process that is best done in VB.
Any ideas?

 

[CBrianA]

I can't claim to have developed this code; I knew I had done it in the past, and then searched until I found it in one of the newsgroups. I tried this on VB6 a few minutes ago.

'Requires the Microsoft Script Control (msscript.ocx)
Private Sub Command1_Click()
Dim A As String
Dim objScript As Object

Set objScript = CreateObject("MSScriptControl.ScriptControl")
With objScript
.Language = "VBScript"
.AddCode ("MyStr = ""How are you?""")
A = .Eval("Mid(MyStr, 1, 3)")
End With
MsgBox A
End Sub

You will need the MS Scripting control, which comes with IE5 and later.

 

[jel]

Isn't it easier to just genereate SQL, and execute using ADO-command?
strSQL = "UPDATE " & strTableName & " SET " & second_box & " WHERE (" & first_box & ")" ?
You'd have to find a way limit user-input to valid SQL-bits, but it's the fasted way I could think of.

 

[GPerk]

Thanks, CBrianA, this looks like this will solve my problem.
But I don't have msscript.ocx on my system so I downloaded
"Windows Script 5.6"
from MSDN and installed it.
I still can't find anything in Components with the word "Script" or "Scripting".
Under References I have checked "Microsoft Scripting Runtime" and "Microsoft VBScript Regular Expressions 5.5".
But when I run your program, I get "ActiveX component can't creat object".
Where am I going wrong?

 

[GPerk]

Disregard my preceding msg. After searching on this site, I searched MSDN for "script control" and found "Microsoft Windows Script Control". That let's you download a file called sct10en.exe which quickly installs and it now shows in Project/Components as "Microsoft Script Control 1.0".
I then tried CBrianA's code and it works!.

I added 2 textboxes to the form and modified CBrianA's code to:

Private Sub cmdEval_Click()
Dim A As String
Dim objScript As Object

Set objScript = CreateObject("MSScriptControl.ScriptControl")
With objScript
.Language = "VBScript"
.AddCode ("R=" & txtIN.Text)
txtOUT.Text = .Eval("R")
End With
End Sub

If I enter: 1+2*3 in txtIn and click cmdEval, txtOUT displays 7.
If I enter: "K = " & 10/3 and click cmdEval, txtOUT shows K = 3.333333

 

[MichaelRed]

hmmmmmmmmmm ... mmmmmmmmm

Some (randomized?) thoughts:

Do you really 'trust' users with so little knowledge of a database to be able to properly update THOUSANDS of records?

Evaldoes some nice things, and it was my first thought, however some more (second?) thoughts give me almost as much 'pause' to reconsider as handing over 'complete' (?) control of a database to inexperienced personnel. The 'syntax' which they would need to know and use is, while not as extensive as SQL, is alos somwhat arcane.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[daveleo]

hello
i duplicated what GPerk did and it works fine if i type only numbers into [txtIN.txt].
if [txtIN.txt] showed [5*8/10] then [txtOUT.txt] showed [4].

but i also tried typing [a+b+c] in [txtIN.txt] where the constants a,b,c are numbers [a=5,etc] set in the code. in this case [txtOUT.txt] showed [0].

i tried a few variations on the code CBrianA's code and still didnt work.

any advice would be appreciated.

daveleo

 

[DreXor]

i've been hunting the web for a while on this and the forums here.

end result i ended up using is pretty small.

just used the execute() method to process the equation, just make sure the input value is vb happy.

strExp = "(1+2)*3"
blah = Execute("thisvar=" & strExp)
msgbox(thisvar) ' 9

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[strongm]

Not in VB you didn't. That will be VBScript (and the code examples you see in this thread are on how to call VBScript from VB, which is why they are longer than your version)

 

[MichaelRed]

strongm is correct re the need to use the Scripting library with execute. To use the 'core' function ("Eval") you DO need a string expression which represents the function with literal values. What you can do to enter these in an Ad Hoc manner is (crudely )illustrated below:

Function basSeriesSum(strFnct As String, _
                      dblStrtVal As Double, _
                      dblLastVal As Double, _
                      dblIncrVal As Double) As Double

    'Michael Red    2/17/04 Simple example of the use of Eval _
     Returns the (four function) calculation of an expression _
     of one variable

    'Example Usage: _
     ? basSeriesSum("X^2 + 2 * X", 3, 12, 2) _
      355

    '? basSeriesSum("x", 1, 4, 1) _
     10

    '? basSeriesSum("x/25 + x* 3", 3, 12, 2) _
     106.4

    '? basSeriesSum("X - X/4", 20, 10, -2) _
     67.5

    Dim dblValX As Double
    Dim dblTmp As Double
    Dim MyFct As String

    dblValX = dblStrtVal
    Select Case dblIncrVal

        Case Is < 0
            While dblValX >= dblLastVal
                MyFct = Replace(strFnct, "X", dblValX)
                dblTmp = dblTmp + Eval(MyFct)
                dblValX = dblValX + dblIncrVal
            Wend

        Case Is = 0
            dblValX = 0

        Case Is > 0
            While dblValX <= dblLastVal
                MyFct = Replace(strFnct, "X", dblValX)
                dblTmp = dblTmp + Eval(MyFct)
                dblValX = dblValX + dblIncrVal
            Wend
        End Select
  
    basSeriesSum = dblTmp

End Function

Although I remain skeptical of the original enterprise

... would be used to modify hundredss of thousands of data records. ...

MichaelRed