How do I extract the data label of a trendline in VBA?

[RockandGrohl]

Hi guys, have done way too much searching on this, even wrestling with ChatGPT which is spinning me round in circles.

I have a chart called "CurveChart", and when I select information from 2 drop down boxes it populates a simple scatter chart with the data points, and a linear trendline, which then has the trendline Equation displayed as a data label.

What I'm trying to do is to get that Trendline equation to be input into cell H3 on the same sheet.

Below is my code. I am getting "parameter not valid"

My chart only has one series, and one trendline. When the drop-down boxes are filled in, it filters a table on Database (db) tab which then populates the series in the chart.

Private Sub worksheet_change(ByVal Target As Range)

Dim Keycells As Range
Dim cur As Worksheet, calc As Worksheet, db As Worksheet
Dim strFormula As String, asset As String, inter As String
Dim Lastrow As Long
Dim chart As ChartObject
Dim series As series
Dim trendline As trendline


Set Keycells = Range("B3:H5")
Set cur = Worksheets("Curves")
Set db = Worksheets("Database")
Set calc = Worksheets("Calculations")

Application.ScreenUpdating = False

If Not Intersect(Range("B3"), Target) Is Nothing Then
    Application.EnableEvents = False
    If Range("B3").Value = "" Then
    On Error Resume Next
    db.ShowAllData
    On Error GoTo 0
    cur.Range("B5").Value = ""
    Else
        If Not Range("B5").Value = "" Then
        Range("B5").Value = ""
        End If
       
    On Error Resume Next
    db.ShowAllData
    On Error GoTo 0
   
    calc.Range("M2:N500").ClearContents
    calc.Range("I2:I500").ClearContents
    asset = Range("B3").Value
    ' Disable line on chart
    Lastrow = db.Cells(Rows.Count, "A").End(xlUp).Row
    db.Range("H1").AutoFilter 8, asset
    calc.Range("H2").Value = asset
    'Add count in here to check visible rows exist
    db.Range("K2:K" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
    calc.Range("I2").PasteSpecial (xlPasteValues)
    cur.ChartObjects("CurveChart").Activate
    ActiveChart.ChartType = xlXYScatter
    End If
End If
Application.EnableEvents = True


        If Not Intersect(Range("B5"), Target) Is Nothing Then
        inter = Range("B5").Value
        calc.Range("M2:N500").ClearContents
        On Error Resume Next
        db.ShowAllData
        On Error GoTo 0
        Lastrow = db.Cells(Rows.Count, "A").End(xlUp).Row
        asset = cur.Range("B3").Value
        db.Range("A1:AG" & Lastrow).AutoFilter 8, asset
        db.Range("A1:AG" & Lastrow).AutoFilter 11, inter
        db.Range("A1:AG" & Lastrow).AutoFilter 20, "<>"
        'db.Range("T2:T" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
        'calc.Range("M2").PasteSpecial (xlPasteValues)
        'db.Range("P2:P" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
        'calc.Range("N2").PasteSpecial (xlPasteValues)
        cur.ChartObjects("CurveChart").Activate
        ActiveChart.ChartType = xlXYScatter
        'strFormula = ActiveSheet.ChartObjects("CurveChart").SeriesCollection(1).Trendlines(1).DataLabel.Text
        'strFormula = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
        Range("H3").Value = strFormula
        Else
        cur.ChartObjects("CurveChart").Activate
        ActiveChart.ChartType = xlXYScatter
        End If
       
       
Application.ScreenUpdating = True
End Sub

The part of the code that's erroring out is the strFormula lines near the bottom - neither of these seem to work. Whenever one works, I would simply drop that value into cell H3.


Cheers!

 

[RockandGrohl]

Possibly related?

Set cht = cur.ChartObjects("CurveChart").chart

Gives me a Type Mismatch error...

Other code which looks like this runs flawlessly

cur.ChartObjects("CurveChart").Activate
    ActiveChart.ChartType = xlXYScatter

So not sure why I can't find CurveChart as a chart...

 

[RoryA]

How did you define cht? It needs to be as Chart not Chartobject.

You'd get a more accurate trendline formula if you calculated it yourself from the data.

 

[RockandGrohl]

How did you define cht? It needs to be as Chart not Chartobject.

You'd get a more accurate trendline formula if you calculated it yourself from the data.

I've since changed it to Chart, but I still have issues with just extracting the formulaic value on the data label.

I'm using LineST to grab the same formula as a work around, but I'd love it if there was a way to simply snag the data label.

Looking at someone elses' project, they're able to do it like this:

        With .ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1)
            strFormula = .DataLabel.Text
        End With

But for some reason, it doesn't work on my sheet :/

 

[RoryA]

Could be a version issue, but I don't understand why you don't want to use LINEST which will be more accurate than whatever formula is showing up in the data label.

 

[RockandGrohl]

Could be a version issue, but I don't understand why you don't want to use LINEST which will be more accurate than whatever formula is showing up in the data label.

I've been spot checking and the LineST will always result in the same as what's in the data label.

y = 90.567x + 28362 is on the data label and the same figure in LineST.


Is there a nice clean way to get LINEST as a line of code? In my db tab, it's a data table, and the lines I would need are in column P and column T respectively. So whatever has been filtered should be calculated

Cheeers

 

[RockandGrohl]

More pain.

I'm trying to plug LINEST into a cell to capture the stats that way seeing as it's apparently impossible to just capture the data label.

When I do the LINEST formula manually, it gives me a second cell to the right which contains the Intercept Constant in the second cell. (If formula placed in A1, B1 contains Intercept Constant)

When I try and do this with VBA, the Intercept Constant is not produced.

calc.Range("Q2").FormulaR1C1 = "=LINEST(R2C14:R" & RowCount + 1 & "C14,R2C13:R" & RowCount + 1 & "C13,TRUE)"

 

[RockandGrohl]

I've since discovered that when you insert LinEst with VBA as above, it inserts an "@" symbol =@LINEST

When you remove the @ symbol, the intercept constant is produced in the adjacent cell.

I can't seem to figure out a way to remove that "@" with VBA, or produce the LinEst calculation without the "@".

My code is now also getting quite messy. In the original book I am trying to replicate the functionality of, they simply activate the chart & pull the data label out to a cell. This is all I want to do:

With ActiveSheet
            With .ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1)
            strFormula = .DataLabel.Text
            End With
            .Range("H3").Value = strFormula

For some reason, this just will not work with my code. How do I find out what name VBA has assigned to my chart, and series? The one I'm looking at is the second chart in the book,.

Ah, yes I just used a macro to identify all the charts in my book, found here:

Finding Chart Object Names

Hello. I have a big file with multiple sheets, each containing multiple charts. By default, Excel names the chart objects ( 1 to n) in a sheet. I need to create a list of all the chart objects in my workbook. How should I do this? i.e. a list that says : - Sheets(i), chartobjects("Chart...

www.mrexcel.com

It is indeed the second chart, so I will try ChartObjects(2).

 

[RoryA]

You need to use Formula2R1C1 rather than FormulaR1C1 to avoid the @

 

[RockandGrohl]

You need to use Formula2R1C1 rather than FormulaR1C1 to avoid the @

I'll keep that in mind, but that would solve the issue nicely. I've since changed code slightly to make everything more legible and cleaner.

Private Sub worksheet_change(ByVal Target As Range)

Dim Keycells As Range
Dim cur As Worksheet, calc As Worksheet, db As Worksheet
Dim strFormula As String, asset As String, inter As String
Dim Lastrow As Long, RowCount As Long
Dim Q1 As Double, pot As Double


Set Keycells = Range("B3:H5")
Set cur = Worksheets("Curves")
Set db = Worksheets("Database")
Set calc = Worksheets("Calculations")

Application.ScreenUpdating = False

'Asset
If Not Intersect(Range("B3"), Target) Is Nothing Then
Application.EnableEvents = False
    'Reset DB filters
    On Error Resume Next
    db.ShowAllData
    On Error GoTo 0
 
 
    'Remove Intervention & Quant fields
    cur.Range("B5").ClearContents
    cur.Range("F5").ClearContents
 
    'Filter DB and prep Calc tab
    asset = Range("B3").Value
    Lastrow = db.Cells(Rows.Count, "A").End(xlUp).Row
    db.Range("H1").AutoFilter 8, asset
    calc.Range("M2:N500").ClearContents
    calc.Range("I2:I500").ClearContents
    calc.Range("H2").Value = asset
    db.Range("K2:K" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
    calc.Range("I2").PasteSpecial (xlPasteValues)
    cur.ChartObjects("CurveChart").Activate
    ActiveChart.ChartType = xlXYScatter
End If

'Intervention
If Not Intersect(Range("B5"), Target) Is Nothing Then
    Application.EnableEvents = False

    'Remove Quant field
    cur.Range("F5").ClearContents
 
    'Filter DB and prep Calc tab
    inter = Range("B5").Value
    calc.Range("M2:R500").ClearContents
    On Error Resume Next
    db.ShowAllData
    On Error GoTo 0
    Lastrow = db.Cells(Rows.Count, "A").End(xlUp).Row
    asset = cur.Range("B3").Value
    db.Range("A1:AG" & Lastrow).AutoFilter 8, asset
    db.Range("A1:AG" & Lastrow).AutoFilter 11, inter
    db.Range("A1:AG" & Lastrow).AutoFilter 20, "<>"
 
    'Check to ensure Asset & Intervention combo exists
    RowCount = db.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    If RowCount > 1 Then
        db.Range("T2:T" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
        calc.Range("M2").PasteSpecial (xlPasteValues)
        db.Range("P2:P" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
        calc.Range("N2").PasteSpecial (xlPasteValues)
        calc.Range("Q2:R2") = WorksheetFunction.linest(calc.Range("N2:N" & RowCount + 1), calc.Range("M2:M" & RowCount + 1), True)
        With ActiveSheet
        DoEvents
            With .ChartObjects(1).chart.SeriesCollection(1).Trendlines(1)
                strFormula = .DataLabel.Text
            End With
            Application.EnableEvents = False
            DoEvents
            .Range("H3").Value = strFormula
            Application.EnableEvents = True
        End With
        End If
    cur.ChartObjects("CurveChart").Activate
    ActiveChart.ChartType = xlXYScatter

    Application.EnableEvents = True
End If


'Quant 1
If Not Intersect(Range("F5"), Target) Is Nothing Then
Application.EnableEvents = False
cur.Range("H5").Value = cur.Range("F5").Value * calc.Range("Q2").Value + calc.Range("R2").Value
Application.EnableEvents = True
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

The issue I have now is that everything is populating as it should, but when I change the Intervention drop down, the chart data label seems to be at odds with the Cost Curve that is in cell H3.

Cell H3 is updated in the Intervention section, AFTER the manual LinEst is called in calc.range("Q2:R2")

I don't understand why both of these numbers aren't updating to the same results at the same time. The chart is showing 5 data points, and the db table is filtered and showing 5 data points, if I perform the sum manually the trendline is absolutely correct, but the StrFormula is not correct at the time when it's called halfway through the Intervention section.


What I've noticed is that if I pause the code at that point, run it, the StrFormula is whatever the previous curve of the chart is, and then if I drag the playhead back up a few lines and run the "With" block again, it produces the correct code.

For some reason, even with DoEvents, it seems to be running behind what it should be.



EDIT: When I step through the code, it works perfectly, however running the code appears like it's processing too fast & excel needs to catch up. I've seen two suggestions on how to mitigate this:

1) Use a wait loop to force the application to pause for a bit to catch up
2) In the With container that references the chart objects, I need to instruct it to look at the parent... stuck on that bit for now.

EDIT 2: Application.wait did not work, no matter where it was inserted in code or how long I paused for.