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

rogerdavid

New Member
Joined
Jan 3, 2013
Messages
15
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?




Code:
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

New Member
Joined
Jan 3, 2013
Messages
15
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

New Member
Joined
Jan 3, 2013
Messages
15
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

New Member
Joined
Jan 3, 2013
Messages
15
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

New Member
Joined
Jun 20, 2013
Messages
1
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,726
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.
 

Forum statistics

Threads
1,081,518
Messages
5,359,237
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top