CODEWITHSUNDEEP
excelvbatrendline
Andrew S
Andrew S

Reputation: 11

Excel VBA Trendline Data Label Text is Empty

I made this code a year or so ago and it's been working fine. Recently, however, it broke. I am assuming there was a Windows update that changed the way Excel's macros worked with charts. The pertinent sections of my code perform the following:

  1. Create an XY Scatter chart on a "prep" worksheet
  2. Create a trendline for each data series on the XY Scatter
  3. Parse each trendline text for slope and R^2, coping them as text onto another "summary" worksheet

What I've found is that Step 1 and 2 work fine, but when I try to parse the trendline text (expecting something like y = 0.0289x + 143), I get an empty string (see the commented out MsgBox, below). What's weirder is that when the Macro finishes (or fails) the chart updates and the text shows normally.

' Create the graph for the linear part of the data set
Worksheets(PrepSheetName).Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Range("$B$19:$G$38")
' ActiveChart.Name = "Linear"
'MsgBox "Past linear creation"

' Add each data set to the chart individually
ActiveChart.PlotArea.Select
For i = 1 To 5

    ' Construct a string like ='Data'!$C$19:$C$38
    YValues = "='" & PrepSheetName & "'!$" & DSMeasCol(i) & "$"
    YValues = YValues & CStr(PrepDataStart) & ":$" & DSMeasCol(i) & "$"
    YValues = YValues & CStr(PrepDataEnd(i))

    ' Construct a string like ='Data'!$C$19:$C$38
    XValues = "='" & PrepSheetName & "'!$" & DSCmdCol(i) & "$"
    XValues = XValues & CStr(PrepDataStart) & ":$" & DSCmdCol(i) & "$"
    XValues = XValues & CStr(PrepDataEnd(i))

    ' Give the chart values
    ActiveChart.SeriesCollection(i).Values = YValues
    ActiveChart.SeriesCollection(i).XValues = XValues

    ' Create a trendline for the chart
    Dim TL As Trendline
    Set TL = ActiveChart.SeriesCollection(i).Trendlines.Add(Type:=xlLinear, Forward:=0, _
        Backward:=0, DisplayEquation:=1, DisplayRSquared:=0, _
        Name:="LC" & CStr(i) & " Trend")
    TL.DisplayEquation = True
    TL.DisplayRSquared = False

    ' Exract the trendline formula
    Dim Eqn As String
    TL.Select
    'MsgBox "Trendline Text: " + TL.DataLabel.Text

    Eqn = Split(TL.DataLabel.Text, "=")(1)
    ' ... and place it on the coversheet ...
    CoverSheet.Cells(CSResults(i), CSFitSlope).Value = Split(Eqn, "x")(0)
    CoverSheet.Cells(CSResults(i), CSFitOffset).Value = Split(Eqn, "x")(1)

    'Find the RSquared of the Trendline
    TL.DisplayEquation = False
    TL.DisplayRSquared = True
    TL.Select
    Eqn = TL.DataLabel.Text
    Eqn = Split(TL.DataLabel.Text, "=")(1)

    ' ... and place it on the coversheet ...
    'CoverSheet.Cells(CSResults(i), CSFitCorr).Value = Eqn

Next i

If I try to run diagnostic code to parse the tredline datalabel text after running the macro the first time, it sees the text. When running the diagnostic code, though, I can't change the type of trendline data that is shown. I would expect, for example, that if I run:

 TL.DisplayEquation = True
 TL.DisplayRSquared = False
 MsgBox "Should show Equation."
 TL.DisplayEquation = False
 TL.DisplayRSquared = True
 MsgBox "Should show R^2."

... that I should see the trendline data label only show the equation when the first message box appears and the second message box should freeze the screen such that only the the R^2 is showing. When I run code like this, though, I find that my assumption is not true: The datalabel stays frozen until the macro completes, even with ScreenUpdating = True It seems like my charts aren't updating when the macro runs, but only updates at the end.

I've tried putting DoEvents and Application.Recalculate after creating the trendline, but it just causes my Excel to crash. Adding Application.ScreenUpdating = True or False doesn't seem to help, either...

Any ideas? I'm at a total loss...

Please let me know if I haven't provided enough information or anything is unclear.

Thanks!

Upvotes: 1

Views: 1737

Answers (1)

Andrew S
Andrew S

Reputation: 11

I ended up using chillin's suggestion and used LinEst. It seems this is a bug in how Excel handles Chart DataLabels during Macro execution, as per this thread. To summarize, my code is almost the same as above, but with the following change using LinEst instead of parsing the Trenline's DataLabel:

' Create a trendline for the chart
Dim TL As Trendline
Set TL = ActiveChart.SeriesCollection(i).Trendlines.Add(Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=1, DisplayRSquared:=0, _
Name:="LC" & CStr(i) & " Trend")      

' Generate the trendline constants and place them on the summary sheet
CoverSheet.Cells(CSResults(i), CSFitSlope).Value = "=INDEX(LINEST(" & YValues & "," & XValues & ",TRUE, TRUE), 1)"
CoverSheet.Cells(CSResults(i), CSFitOffset).Value = "=INDEX(LINEST(" & YValues & "," & XValues & ",TRUE, TRUE), 1,2)"
CoverSheet.Cells(CSResults(i), CSFitCorr).Value = "=INDEX(LINEST(" & YValues & "," & XValues & ",TRUE, TRUE), 3)"

Upvotes: 0

Related Questions