Trendline DataLabels evaluating as Blank in VBA but still displayed correctly on Charts

[rogerdavid]

Ok so I have this problem and its doing my head in.

Basic set up: One one sheet I have a pivot table with bulk data, on another sheet I have 3 XY charts that run off the pivot table.

I have one subroutine that manipulates a pivot table based on a comboBox selection. It basically turns on/off certain filters depending on the selection made.
This is my DROPDOWNLIST_change subroutine. It is stored in Module 2.

In Module 3 I have another subroutine in a seperate module called refreshTrendlines() that takes every chart in the specified worksheet, deletes the old trendlines and adds a new one (I found this was the only way to properly refresh the Formula & R^2 values supplied in the associated Data Label.

The refreshTrendlines() subroutine is called inside the DROPDOWNLIST_change subroutine.

The issue is when I attempt to extra the text data from the Trendline data label. During the refreshTrendlines subroutine, I also take the trendline data labels from each chart and parse the text so that I can split the fTrendline Formula & R^2 values, save them in a string array, and place them into seperate cells in my worksheet. This works perfectly when I hit the "play" button from within the refreshTrendlines subroutine when editing the Macro. But when I hit "play" from within the DROPDOWNLIST_change subroutine, or when I attempt to execute the whole thing by making a selection in my comboBox - the trendline datalabels fail to get read by the script (but they show up and work on the physical chart) and end up showing as empty strings - which then causes me a "Sub Script Out of Range" error when I try to access one of the array elements of the split up data label.

But try as I might I can not figure out why the hell its doing this. I have been playing with it an occasionally I get it to work but ONLY if it is directly run from inside the macro editor and not called by an external subroutine.

Was wondering if someone could look at my code to see if they can find an error or something that might help?

Sub refreshTrendlines()
   Sub refreshTrendlines()
      '''''''' SET UP ALL THE VARIABLES ETC '''''''
      Dim oChtObj As ChartObject
      Dim oSerie As Series
      Dim i As Integer
      Dim dtLabels() As String
      
      Dim originalFormulaCell As Range
      Set originalFormulaCell = ActiveSheet.Range("V41")
      originalFormulaCell.Activate
    
      Dim offset_value As Integer
      offset_value = 0
    
      '''''''' TRENDLINE STUFF
      For Each oChtObj In ActiveSheet.ChartObjects
          ''' If the chart is an XY scatter chart, proceed..
          If oChtObj.chart.ChartType = xlXYScatter Then
             
             For Each oSerie In oChtObj.chart.SeriesCollection
             
                 '''''' DELETE TRENDLINES FROM THE LAST POSITION DUE TO UPDATING INDEX
                 For i = oSerie.Trendlines.Count To 1 Step -1
                     oSerie.Trendlines(i).Delete
                 Next i
                 
                 '''''' REMOVE POINT DATA LABELS, TAKES TOO LONG OTHERWISE
                 oSerie.HasDataLabels = False
                 
                 
                 '''''' ADD NEW TRENDLINES FOR THE DATA THAT WAS UPDATED WHEN THE DROP DOWN LIST WAS CHANGED
                 With oSerie.Trendlines.Add
    
                     .Type = xlLinear
                     
                     '''''' GET TRENDLINE FORMULAS INTO CELL
                     '''''' THIS ONLY WORKS WHEN I HIT "PLAY" FROM WITHIN THE MACRO EDITOR WHILE EDITING THIS SUB ROUTINE
                     '''''' *it does not work when called from the other sub routines *
                     .DisplayEquation = True
                     .DisplayRSquared = True
                     dtLabels = Split(.DataLabel.Text, Chr(10))
                     Debug.Print .DataLabel.Text '<-------- SHOWS NOTHING, .DataLabel.Text is BLANK for whatever reason Im not sure why??
                     originalFormulaCell.offset(offset_value, 0).Value = Replace(Replace(dtLabels(0), "y = ", ""), "x", "*(BM_grade)") '<--------- THIS IS THE LINE THAT ERRORS.
                     originalFormulaCell.offset(offset_value, 1).Value = Replace(dtLabels(1), "R² = ", "")
                     offset_value = offset_value + 1
    
                 End With
             Next oSerie
         End If
     Next oChtObj
End Sub

 

[rogerdavid]

Edit -
That code I pasted above actually errors at all times now, not just when I try to call the script externally. I must have made some minor changes that I dont remember which stopped it from working even without being externally called. ****. Anywya, its still the most up to date code that I've written, and its still doing my head in :P
Cheers.

 

[rogerdavid]

Bumpity bump

 

[rogerdavid]

Nobody? :-\

 

[rogerdavid]

Can I please have someone at least acknowledge they have seen this thread, and are unable to solve the problem, or require more information, etc? I'm still stuck. Thanks.

 

[rogerdavid]

So I ended up solving the issue.
I dont really understand why, butit was an issue with the manual updating. I had to turn ManualUpdating to FALSE for the charts to begin to acknowledge and plot the new data series defined in the pivot table. Then my trendlines started working.

 

[hussiano3]

So I ended up solving the issue.
I dont really understand why, butit was an issue with the manual updating. I had to turn ManualUpdating to FALSE for the charts to begin to acknowledge and plot the new data series defined in the pivot table. Then my trendlines started working.

Did this actually work?? I tried it, it didn't work..
I tried splitting the task of acquiring the data label in different sub and it worked :D
Good luck

 

[Jon Peltier]

Parsing the trendline's data label risks subsequent errors related to significant digits in the displayed formula. Use SLOPE(Yrange,Xrange), INTERCEPT(Yrange,Xrange), and CORREL(Yrange,Xrange) and build your own formula from these. Keep SLOPE and INTERCEPT in their own cells for any calculations you need to make, and keep in mind that R² is CORREL()^2.

 

[Jon Peltier]

I wrote about this a while back, and described the use of LINEST(), which provides more statistical feedback than just SLOPE(), INTERCEPT(), and CORREL(), in my blog post, Trendline Fitting Errors.

 

[WurstPanzer]

Try

.Trendlines(1).DataLabel.Select
Selection.Characters.Text

It worked for me.

Cheers