convert string to formula 1

[BigInch]

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,



**********************
"The problem isn't finding the solution, its trying to get to the real question." BigInch

http://virtualpipeline.spaces.live.com/

 

[BigInch]

Got it! Found the answer here,

http://digilander.libero.it/foxes/mathparser/Run_Time_ Math_Formula_ Excel.htm

**********************
"The problem isn't finding the solution, its trying to get to the real question." BigInch

http://virtualpipeline.spaces.live.com/

 

[IDS]

You can replace the x(2) in the string with their numerical value, then use the Evaluate function:

ReDim ResA(1 To UBound(x), 1 To 1)
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://newtonexcelbach.wordpress.com/2009/09/10/evaluating-polynomial-functions/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[BigInch]

I like that! [∞] more elegant.
Thanks

**********************
"The problem isn't finding the solution, its trying to get to the real question." BigInch

http://virtualpipeline.spaces.live.com/

 

[JTBorton]

That's pretty good stuff. I've been working on similar issues. Perhaps you can offer a suggestion for me:

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]

 

[IDS]

You can't use Application.Evaluate to evaluate VB functions (as far as I know), but it will evaluate a UDF written in VBA.

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/