Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Problem solved - How do I numerically evaluate an expression

  1. #1
    New Member
    Join Date
    May 2002
    Location
    New Zealand
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    May 2002
    Location
    New Zealand
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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




    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    New Member
    Join Date
    May 2002
    Location
    New Zealand
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •