Help!! Is there a VBA function for evaluating an expression

[andrew.ward]

Hi all,

I have formula as a text string. Is there a method of evaluating this equation directly in VBA rather than using string manipulation functions to extract the numerical values in order to create a new function?

That is, I have can obtain the equation of a trendline as a string. Can I evaluate this directly using some type of string evaluation function, and if so how? Or do I have to use the laborious string functions to extract the coefficients and recreate the function using variables?

E.G.

Dim x as double
Dim y as double
Dim TrendlineEquation as string

TrendlineEquation = “2.6667*x + 11.44”

x=2

y= SomeTypeOfEvaluationFunction (TrendlineEquation)

‘Or to put it another way:

y= SomeTypeOfEvaluationFunction (“2.6667*x + 11.44”)

‘Nb the value of ‘y’ should be 16.77

Thanks so much,
Andrew

 

[Juan Pablo González]

You could use the REPLACE function of Excel (Worksheetfunction.Replace) to replace "x" with the "real value". Then, use the EVALUATE method of VBA to get the correct answer.