×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

convert string to formula

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,

 

**********************
"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

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

CODE

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/
 

RE: convert string to formula

(OP)
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/

RE: convert string to formula

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]

RE: convert string to formula

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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources