Page 329 - Excel for Scientists and Engineers: Numerical Methods

306 EXCEL: NUMERICAL METHODS

Sub TrendlineToCell()
'Tranfers Trendline text to cell as formula.
'REMEMBER LOCATION OF CHART
If TypeName(ActiveSheet) = "Chart" Then
ChartSheetNarne = ActiveSheet.Name
Else
pointer = Application.Find("Chart", ActiveChart.Name)
ChartObjectName = Mid(ActiveChart.Name, pointer, 100)
End If

'MAKE SURE A TRENDLINE IS SELECTED.
On Error GoTo Badselection
'Selection.Narne e.g., "Text S3T1"
If Selection.Name Like "Text S*T*" Then
pointer = Application.Find("T", Selection.Name, 3)
SeriesNum = Val(Mid(Selection.Name, 7, pointer - 7))
TrendlineNum = Val(Mid(Selection.Name, pointer + 1, 3))
Else
Badselection: MsgBox "You must select a Trendline label."
Exit Sub
End If
On Error GoTo 0
'CHANGE NUMBER FORMAT TEMPORARILY TO GET SUFFICIENT PRECIS101
TLNurnberFormat = Selection.NumberFormat
Selection.Num berFormat = "0.0000000000E+00
'CONVERT TRENDLINE TEXT TO AN EXCEL FORMULA
'First, strip off y and R parts
TLText = Selection.Characters.Text
pointer = Application.Find("=", TLText)
TLText = Mid(TLText, pointer, 1024)
If Not (IsError(Application.Find("R, TLText))) Then
pointer = Application.Find("R, TLText)
TLText = Left(TLText, pointer - 2)
End If
'CONVERT DIFFERENT TYPES OF TRENDLINE EQUATIC, I
Select Case ActiveChart.SeriesCoIlection(SeriesNum) -
.Trendlines(TrendlineNum).Type
Case -4132 'Linear
TLText = Application.Substitute(TLText, "x", "*x")
Case -41 33 'Logarithmic
TLText = Application.Substitute(TLText, "L", "*L")
Case 3 'Polynomial
TLText = Application.Substitute(TLText, "x", "*xA")
TLText = Application.Substitute(TLText, "xA ", "x ")
Case 4 'Power
TLText = Application.Substitute(TLText, "x", "*xA")
Case 5 'Exponential
TLText = TLTexi 8, "Y'