Problem solved - How do I numerically evaluate 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


[ This Message was edited by: andrew.ward on 2002-05-09 21:10 ]

[Tom Schreiner]

Hi Andrew.
I, with my limited knowledge of Excel's available functions, could not figure out a way to return a function from a string.
This could be done with a somewhat complicated user defined function.
So I think you may have answered yourself about using vaious string manipulations, ect to extract the values.
You might want to search the web for a function or wait until Aladin is browsing the board.
His functions generally give me a migraine, but they sure work.
Does your spreadsheet run in Office 2000?

Tom


_________________
Found a solution? If so, please post again so members of this board can spend their time helping others. Better still, edit your topic(intitial post), by tagging on a word or phrase such as, "Problem Solved", or "Resolved". Thanks for being courteous!

[ This Message was edited by: TsTom on 2002-05-09 19:41 ]

[andrew.ward]

I solved the problem using various string manipulations. The resultant function obtains the equation that describes a trendline. Then it allows the user to input a value into that equation in order to get out an interpolated value based on the trendline equation.

Thanks for the help,

best regards,
Andrew

Function TrendLineValue(ByVal XValue As Double, ChartName As String, SeriesName As String) As Variant

'EXPLANATION
'The purpose of this function is to obtain values interpolated from existing data, using
'a trend line of the existing data. The parameters SeriesName and ChartName point
'this function to the series that has the trend line. It then obtains the equation that
'describes the trend line.

'The Function then uses XValue as the input value to the trend line equation. The
'resultant value of the trendline equation is the required interpolated value.

'Note: This function current only works for linear and power trendlines. The case statement
'is easy enough to modify if trendlines of other types are required.

'This function will fail if the specified series does not have a trend line


'PARAMETERS=============================================================================
'SeriesName: The name of the series that has the trendline, the equation of which is
'required.
'ChartName: The name of the chart that displays the series and associated trendline.
'XValue: The input value that will be put into the trendline equation in order to
'return the required value.

'VARIABLES=============================================================================
'TLEqn: The equation of the trendline
'TLEqnMod: The modified version of the trendline


'Variable Declarations=================================================================

Dim TLEqn As String
Dim TLEqnMod As String
Dim TLEqualPos, TLXPos, TLAddPos
Dim TLCoeff, TLExp, TLConst
Dim Area As Double
Dim TLType As Long

'BODY ==================================================================================

'Obtain the type of trendline that has been used
TLType = Charts(ChartName).SeriesCollection(SeriesName).Trendlines(1).Type

'Obtain the equation that describes the trendline
TLEqn = Charts(ChartName).SeriesCollection(SeriesName).Trendlines(1).DataLabel.Text

'remove the 'y' from the start of the trend line equation
TLEqnMod = Right(TLEqn, Len(TLEqn) - 1)

'remove spaces from the string

TLEqnMod = Trim(TLEqnMod)

'The case statement replaces the 'x' in the equation with a reference to a cell.
'Also, it turns the string into a proper equation based on the type of trend line that has
'been used.

Select Case TLType 'this is identifies the type of trendline

Case xlLinear

'find the position of =, x and + in the equation
TLXPos = InStr(1, TLEqnMod, "x", vbTextCompare)
TLEqualPos = InStr(1, TLEqnMod, "=", vbTextCompare)
TLAddPos = InStr(1, TLEqnMod, "+", vbTextCompare)

'Obtain the constants in the linear equation
TLCoeff = CDbl(Trim(Mid(TLEqnMod, TLEqualPos + 1, TLXPos - TLEqualPos - 1)))
TLConst = CDbl(Trim(Mid(TLEqnMod, TLAddPos + 1, Len(TLEqnMod) - TLAddPos)))


'Evaluate the Trend line equation using the user input and return the value
TrendLineValue = TLCoeff * XValue + TLConst

Exit Function

Case xlPower

'find the position of =, x and + in the equation
TLXPos = InStr(1, TLEqnMod, "x", vbTextCompare)
TLEqualPos = InStr(1, TLEqnMod, "=", vbTextCompare)

'Obtain the constants in the linear equation
TLCoeff = CDbl(Trim(Mid(TLEqnMod, TLEqualPos + 1, TLXPos - TLEqualPos - 1)))
TLExp = CDbl(Trim(Mid(TLEqnMod, TLXPos + 1, Len(TLEqnMod) - TLXPos)))


'Evaluate the Trend line equation using the user input and return the value
TrendLineValue = TLCoeff * XValue ^ TLExp

Exit Function

Case xlExponential, xlLogarithmic, xlMovingAvg, xlPolynomial

'This part needs to be modified if this function is to cope with the above
'types of trendlines.
TrendLineValue = "This type of trendline is not supported. Currently, only linear and power trendlines are supported"
Exit Function

Case Else ' Other values.

TrendLineValue = "This type of trendline is not supported. Currently, only linear and power trendlines are supported"
Exit Function

End Select

End Function


[ This Message was edited by: andrew.ward on 2002-05-09 21:11 ]

[ This Message was edited by: andrew.ward on 2002-05-12 14:44 ]

[Mark O'Brien]

Better make sure "Farty Fart Fart, Inc." wouldn't be pissed off that some of the code from your project for them is now freely available on the 'net. You may consider just removing the comment header.

It looks like nice code though. Well done.

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-05-13 20:08 ]

[Ivan F Moala]

On 2002-05-09 18:16, andrew.ward wrote:
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


[ This Message was edited by: andrew.ward on 2002-05-09 21:10 ]

Hi Andrew...if I read you post correctly it
looks like your trying to evaluate the
Trend line eqaution from a chart Trend.

If so then try this;

Sub test()

Dim x As Double

Dim y As Double

Dim TrendlineEquation As String

Dim Rp As Double



Rp = 2

TrendlineEquation = "2.6667 * x + 11.44"

TrendlineEquation = Replace(TrendlineEquation, "x", Rp)



y = ExecuteExcel4Macro("Evaluate(" & TrendlineEquation & ")")

MsgBox y



End Sub

[andrew.ward]

Ivan,

you hit the nail on the head - I was looking for a command that allowed me to numerically evaluate a text string directly.

Thank you,

Andrew