How Can I let a user create formulas? Can’t change a textbox 2 formula…
I am trying to allow the user to be able to create or edit formulas used in a program. The way I have it set up is that there are different command buttons that have different variables that are used as well as buttons for each # 0-9 and + - / * and ( and ) so they build a formula and it shows it in the textbox. The thing is I am trying to test the formula but it just shows it as text. Or it will just put a 0 if I use Val(TestFormula.text) How can I turn the Textbox text into a formula. So this TestVal = “(Area + 2000)/2” turns to TestVal= (Area + 2000/2)
You might want to build a "Formula builder" part to your project are as an activeX control, so you can control the flow of what is entered into the formula.
You can't far as I am aware take text from anything and insert into a variable for it to do computations on it. You can do some heavy string manipulation to decipher their formula and then control the way it is processed, but this is very difficult and time consuming. What are you trying to let them build formulas for? Is it a math program?
I am making a program that is used to calculate road meterials and cost. I just wanted the people with the program to be able to add new things down the road without me having to hard code it into the program itself. Also if there ever was a change in a formula they can edit it by themself... I just thought this would be a good option for the program since the formulas aren't really that hard. But now I don't see any easy way of doing this.
I wanted to be able to store the formula info into a database so if they added another item say Item2 they would be able to make the formula and then that option would show up on the options list and if they picked it then it would calculate out...
If i understand your question right, then you could try making a function to to insert the values in for the variables you specify before evaluating the expression.
Kind of like this:
Code:
Public Function setVar(expression As String, var As String, val As Double)
Dim i As Integer
setVar = ""
For i = 1 To Len(expression) - Len(var) + 1
If Mid(expression, i, Len(var)) <> var Then
setVar = setVar + Mid(expression, i, 1)
Else
setVar = setVar + CStr(val)
i = i + Len(var) - 1
End If
Next i
setVar = setVar + Right(expression, Len(expression) - i + 1)
End Function
Then you could use the original expression, variable name, and variable value for the arguments "expression", "var", and "val" respectively, and evaluate what it returns. (or use it again for multiple variables)
Last edited by KingMoogle; Jan 27th, 2003 at 11:59 PM.
I want to be able to enter in the value of these into a formula that the user creates.
So how I find out how much dirt is needed for the Area you would use this formula:
Dirt = (CubicYards *2800) / 2000
This would give you that amount in tons
So I want a user to be able to enter in a new Item like Dirt and write the formula for it and it would be saved in a database.
I just can’t get the variables to work… It saves the formula correct but it puts “ “ around it. Or if I use the ScriptControl1.Eval(Text1.Text) it only works with numbers. It will not calculate the value of SquareFeet or any other variable it gives them a 0.
So the Dirt formula would calculate out like this (0 *2800) / 2000
I attached the file that I just started on so maybe that will help show what I am talking about…
Just a note... For the testing I just put in the values of CubicYards, CubicFeet ect in the code... No L x W x D on this yet... that was 2 come after I got this to work.
If your only problem is geting the variables to work right with it, then the function i posted earlier could be used as in the attachment on this post.
If that's not the problem though, then i'm unclear as to your problem.
(BTW, why does it keep making me add the Script control every time i open the file?)
An example. Have a command button name "Command1" and text box named "Text1". This code does not have any Error Handling code. Step through (F8) the code to see how this code works
VB Code:
Private Type MyVariables
strVarName As String * 10
lngVarValue As Double
End Type
Dim myVar() As MyVariables
'We use a ByRef argument so that we can return the
'new variable's name as well in addition to the
'result of the Evaluation
Private Function Evaluate(strExpression As String, ByRef LeftHandSide As String) As Double
Dim tmpString() As String
Dim LHS As String, RHS As String
Dim RHSBegin As Long
Dim lngLoop As Long
Dim tmpResult As Double
'Find out where in the Expression the Right Hand Side begins
Tsur, the attachment here is the form U posted in the other thread. It works ok, I don't get any error except when brackets are used. I shall keep u posted on any progress made.
regards
kayjay
"Brothers, you asked for it."
...Francisco Domingo Carlos Andres Sebastian D'Anconia
Don't give up on the Script Control just yet..... I use it in our app to let the user build some extremely complex formulas. The trick is in how the functions get built. The Script Control has Code modules that you can add code to and then execute. Two words of warning... the code has to be added, there's no way to save/load directly to a file (you would have to do that yourself) And 2, it uses VBScript, so there are somethings that are not available, like the IIF function....
Here's what you do, build a string that has all of the code, incl any constants, and functions you want to use.
so that it looks something like this:
VB Code:
function CalcSqrFeet(Length, Width)
CalcSqrFeet= Length * Width
end function
function CalcCubicFeet(Length, Width, Depth)
CalcCubicFeet = Length * Width * Depth
end function
function CalcSquareYards(SquareFeet)
CalcSquareYards = SquareFeet / 9
end function
function CalcCubicYards(CubicFeet)
CalcCubicYards = CubicFeet / 27
end function
Then, using the Script Control, create a CodeModule, give it what ever name you want (maybe "AreaFunctions").
Then Add the string as the CodeModule's code.
You'll need to do a bit of checking to find out how to pass in parameters (I think it's just an array passed in after the function name) as I don't do that in my code.
Hope this helps
And once you've "loaded" the VBCode into a CodeModule, as long as you don't destroy the object, the formulas will remain there, to be used over and over and over again......
Originally posted by techgnome Don't give up on the Script Control just yet..... I use it in our app to let the user build some extremely complex formulas. The trick is in how the functions get built. The Script Control has Code modules that you can add code to and then execute. Two words of warning... the code has to be added, there's no way to save/load directly to a file (you would have to do that yourself) And 2, it uses VBScript, so there are somethings that are not available, like the IIF function....
Here's what you do, build a string that has all of the code, incl any constants, and functions you want to use.
so that it looks something like this:
VB Code:
function CalcSqrFeet(Length, Width)
CalcSqrFeet= Length * Width
end function
function CalcCubicFeet(Length, Width, Depth)
CalcCubicFeet = Length * Width * Depth
end function
function CalcSquareYards(SquareFeet)
CalcSquareYards = SquareFeet / 9
end function
function CalcCubicYards(CubicFeet)
CalcCubicYards = CubicFeet / 27
end function
Then, using the Script Control, create a CodeModule, give it what ever name you want (maybe "AreaFunctions").
Then Add the string as the CodeModule's code.
You'll need to do a bit of checking to find out how to pass in parameters (I think it's just an array passed in after the function name) as I don't do that in my code.
Hope this helps
I did a little bit of research on the Scripting Control. You were right. It really is quite extendable, though being VB Script, there are some restrictions. The best of all its functionalities is its ExecuteStatement method. Here is a sample of what it can do.
Required; a Scripting control, named "SC", a TextBox, "Text1" with its Multiline property set to "True", copy the attached text file into the application's path, a command button "Command1" and paste the following code
VB Code:
Private Sub Command1_Click()
Dim result
SC.AddCode Text1.Text
'Execute a Function
result = SC.Run("Avg", "10,2,51,89,48")
MsgBox result
'Assign the result to a variable in the script
SC.ExecuteStatement ("SomeVar = " & result)
'Display the script's variable's value
MsgBox SC.Run("GetSomeVar")
'Change that variable's value from this code
SC.ExecuteStatement ("Call SetSomeVar(1024)")
'Verify that it has actually changed in the Script
MsgBox SC.Run("GetSomeVar")
'Use the new value to assign a value to another variable
Originally posted by TSur Ok how about just this... Say you have a textbox that a user enters this text:
(2 * 3) + 1
How would you code a cmd button so that it will tell you the answer of 7? I just get 0 when I do val(text1.text)
The attached demo app will do that for you. It is actually an example of LALR parsing. It has no look ahead capability at the moment. I may get around to adding it one of these days.
Originally posted by KayJay I did a little bit of research on the Scripting Control. You were right. It really is quite extendable, though being VB Script, there are some restrictions. The best of all its functionalities is its ExecuteStatement method.
Was there any doubt.
We use this method in our product, where we let the user build formulas (using a set of menus and a grid) then at run-time, convert the data into a VBScript that we then stuff into the script control. The biggest limitation is that because it is VBScript (but you can load other languages, but I have not been able to figure out how - I think the method is LoadLanguage, or something like that) it doesn't have IIF... but you can build your own and add it if you need it.
Reply With Quote
2009 - 2016
Did not know that was possible with the Script control. 
