I have some code built by butchering some sample code found on your site.

It was researched to get over the immensely irritating problem of being unable to get the formula from a trendline when executing chart creation VBA normally, but the formula becomes available when stepping through it.

From what I can gather, the normal solution to this problem is to use either the LinEst or Slope function to generate the formula from the values.

However, I seem to have a weird problem (which I suspect is related to the data involving a date) - the LinEst and Slope functions yield identical but significantly different formulae from the visible one (and by inspection, the visible one is correct)

On the Worksheet, I have the following data in cells A1:B5.

The date cells in col A are formatted as "dd/mm/yyyy".

The value cells in col B are formatted as numeric, with 0 decimal places.

15/02/2015 | 2 |

07/03/2015 | 55 |

27/03/2015 | 83 |

16/04/2015 | 27 |

06/05/2015 | 13 |

<tbody>

</tbody>

The visible equation on the chart is

"y=0.31x-13005",

but the LinEst and Slope functions both generate

"y = 6.2x + 24.2"

The code follows

Code:

```
Option Explicit
Sub Test()
Dim objChart As Excel.ChartObject
Dim strDataRange As String
Dim rngText As Excel.Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
For Each objChart In ActiveSheet.ChartObjects
objChart.Delete
Next objChart
strDataRange = "A1:B5"
Set rngText = ActiveSheet.Range("G1:K3")
rngText.Delete
Call TestGraph("TEST FORMULAE", strDataRange)
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Sub TestGraph(strTitle As String, _
strData As String)
'***** this sub-routine adds a graph to the current sheet
'On Error GoTo ErrMsg 'this error handling isn't normally here, I just added it incase something odd was happening
Dim intPARight As Integer
Dim intPATop As Integer
Dim rngToCover As Range
Dim strArg1 As String
Dim strArg2 As String
Dim strArg3 As String
Dim strArg4 As String
Dim strTP As String
Dim varTransposed As Variant
Dim varValues As Variant
Dim varWSFLinEst As Variant
Dim varWSFSlope As Variant
Dim varWSFIntercept As Variant
'*
'** Set the size and position of the Chart.
'*
Set rngToCover = ActiveSheet.Range("D6:K17")
With ActiveSheet.ChartObjects.Add(Left:=rngToCover.Left, _
Width:=rngToCover.Width, _
Top:=rngToCover.Top, _
Height:=rngToCover.Height)
With .Chart
' Get data to use later when positioning the formula.
intPATop = .PlotArea.Top
intPARight = .PlotArea.Left + .PlotArea.Width
.HasTitle = True
'Set Title
.ChartTitle.Text = strTitle
'Set source
.SetSourceData Source:=ActiveSheet.Range(strData)
'Set Chart type
.ChartType = xlLine
'Format the series
With .SeriesCollection(1)
.Name = "Raw data"
.trendlines.Add
With .trendlines(1)
.DisplayEquation = True
.DataLabel.Left = intPARight
.DataLabel.Top = intPATop
End With
'Recover the values into a variant
varValues = .Values
End With
End With
End With
'Now get the trend equation by other means.
strTP = "Transpose(Row(A1:A" & _
UBound(varValues) & _
"))"
varTransposed = Evaluate(strTP)
With Application.WorksheetFunction
varWSFLinEst = .LinEst(varValues, _
varTransposed)
varWSFSlope = .Slope(varValues, _
varTransposed)
varWSFIntercept = .Intercept(varValues, _
varTransposed)
End With
strArg1 = Format(varWSFLinEst(1), "+ 0.####;- 0.####")
strArg2 = Format(varWSFLinEst(2), "+ 0.####;- 0.####")
strArg3 = Format(varWSFSlope, "+ 0.####;- 0.####")
strArg4 = Format(varWSFIntercept, "+ 0.####;- 0.####")
With ActiveSheet
.Range("G1") = "LinEst: " & _
strArg1 & "x " & strArg2
.Range("G2") = "Slope: " & _
strArg3 & "x " & strArg4
End With
End Sub
```

Can anyone help me to get the correct equation?