Convert Excel VBA string formula to real formula

[SafetyHammer]

Hi,

I have a code that copy the polynomial equation from a graph in excel,

y = -0.0008x3 + 0.0832x2 + 9.5105x - 0.7195 (formula keep changing depending on graph data)

I am trying to solve the formula for a given Y value. my problem is I am unable to convert it to a real formula in VBA so I can solve it

Sub CalcualteValveOPening()  Dim chrtobj As ChartObject
  Dim chrt As Chart
  Dim srs As Series
  Dim tline As Trendline
  Dim dlbl As DataLabel
  Dim str As String
  Dim z As Integer
  Dim x As Integer
  


  With ActiveSheet
    Set chrtobj = ActiveSheet.ChartObjects(1)
    Set chrt = chrtobj.Chart
    Set srs = chrt.SeriesCollection(1)
    Set tline = srs.Trendlines(1)
    Set dlbl = tline.DataLabel
    str = dlbl.Formula
    .Range("E27").Value = str
  End With
  
  

  For x = 0 To 100 Step 0.5
  z = Range("E27").Value
  If z / Range("E24").Value <= 1 Then Range("E29").Value = x
Next x


End Sub

Wondering if anyone can help with the conversion

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I would prefer to use the LinEst Worksheet Function to give you the numbers more directly.

For instance, this code evaluates the ranges specified and produced the polynomial coefficients up to third order and places them in a variant (v) which is set to an Array with all four values in it:

Sub Test()
    Dim v As Variant
    v = Application.LinEst(Range("B2:B14"), Application.Power(Range("A2:A14"), Array(1, 2, 3)))
End Sub

Regards,

 

[SafetyHammer]

Thank you, this made my life easier and worked perfectly

 

[RickXL]

You're welcome.

Regards,

 

[Kelvin Stott]

If your function has the form "y = f(x)" stored as string variable Fx

Then you can evaluate y for any given x with the following formula in VBA:

y = Evaluate(Replace(Right(Fx, Len(Fx) - 4), "x", x))

For example:

y = Evaluate(Replace(Right("y = x^2 + x + 1", Len("y = x^2 + x + 1") - 4), "x", 2))

Will give you y = 7 for x = 2