convert string to formula
convert string to formula
(OP)
I'm reading a string value from Excel into a string variable named strK. strK equals,
"0.035 * (-0.2 * x(2) ^ 4 + 3.45 * x(2) ^ 3 - 5 * x(2) ^ 2 + 50 * x(2) + 3)"
I have known values for the array x() and now I would like to have VBA evaluate the above string as a formula and set a variable, c2, equal to that.
Any ideas on how to accomplish this?
Thanks,
"0.035 * (-0.2 * x(2) ^ 4 + 3.45 * x(2) ^ 3 - 5 * x(2) ^ 2 + 50 * x(2) + 3)"
I have known values for the array x() and now I would like to have VBA evaluate the above string as a formula and set a variable, c2, equal to that.
Any ideas on how to accomplish this?
Thanks,
**********************
"The problem isn't finding the solution, its trying to get to the real question." BigInch
http://virtualpipeline.spaces.live.com/





RE: convert string to formula
ht
**********************
"The problem isn't finding the solution, its trying to get to the real question." BigInch
http://virtualpipeline.spaces.live.com/
RE: convert string to formula
CODE
For i = 1 To UBound(x)
EForm = "0.035 * (-0.2 * x ^ 4 + 3.45 * x ^ 3 - 5 * x ^ 2 + 50 * x + 3)"
EForm = Replace(EForm, "x", x(i))
ResA(i, 1) = Evaluate(EForm)
Next i
see also:
http://new
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: convert string to formula
Thanks
**********************
"The problem isn't finding the solution, its trying to get to the real question." BigInch
http://virtualpipeline.spaces.live.com/
RE: convert string to formula
I want the user to be able to type an equation which can be evaluated. But the kicker is this: I want the user to have the freedom to add VBA functions in the formula, even user-defined functions. So how can I recognize VB functions in a string and then call the function to evaluate the result? To my understanding, the Evaluate function cannot do this.
-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]
RE: convert string to formula
For instance if you have a UDF:
Function TFunc(A As Double, B As Double, C As Double, x As Double) As Variant
TFunc = A * Sin(x) ^ C + B * Cos(x) ^ C
End Function
and in cell A1 you have:
=tfunc(1,2,3,0.4)
Then Application.Evaluate(Range("A1").Value) will return 1.621824353, which is the right answer.
There is a useful article by Charles Williams on the use of Application.Evaluate (and some of its peculiarities) here:
http://www.decisionmodels.com/calcsecretsh.htm
(scroll down to the Evaluate Method section)
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/