locked
Excel 2007: Trendline.Add fails to add a legend entry for the trendline sometimes RRS feed

  • Question

  • Hello!

    I have charts containing data from multiple data columns. Each data column is plotted with multiple series since there are more data than one series can contain (>32000 rows). After plotting the multiple series I remove all legends except for the first one of every data column, i.e. as a result I have as many legends as there are data columns.

    Then I want trendlines for each series in a single data column that in the same way as the series only have a single legend. Thus I have constructed a macro that adds moving average trendlines to the series when one of the series in this data column is selected and the macro is run. This macro also removes the all trendline legends except for the legend of the first one.

    Thus the chart (especially the legends) should look like this:

    My problem is that my macro stops working if I run it for a series and then remove the trendlines manually for these series and run the macro again.

    According to my debugging the code fails since excel does not always add a legend for the trendline when I add the trendline with this command:

    ActiveChart.SeriesCollection(i).Trendlines.Add
    <br/>
    

    Thus, as I try to remove the legend of the newly added trendline in VBA the delete method fails as there is no legend with that index to delete.

    If I remove the chart and redraw it the trendline macro works perfectly again until I remove the trendlines manually. It also works it if I save the excel file and reopen it and then run the macro.

    I could rewrite the code to remove the excess legends after the loop which creates the trendlines, but as the first legend sometimes is missing, this approach would probably still not work satisfactorily.

    Anyone who has a clue why this happens and what could be made to fix the problem?

    Thank you in advance!

    - Axel

    I attach the trendline adding code below: (Did not attach the code for the setting of the colors and disabling/enabling UI updates)

    'This sub adds a moving average trendline to the selected data series
    Sub addMovingAverageTrendline()
    
        'Call the code for turning UI updates off
        Call disableUIUpdating
    
        'define the used colors
        Call defineColors
    
        'Define variables
        Dim seriesPerDataColumn As Integer
        Dim movingAveragePeriod As Integer
        Dim userColorIndex As Integer
        Dim startSeriesIndex As Integer
        Dim rowcount As Long
        
        'Find the index of the first series in the same data column as the selected one
        startSeriesIndex = findIndexOfSelectedSeries
        
        'Call the code for turning UI updates on
        Call restoreUIUpdating
    
        'ask user about how long moving average should be used
        movingAveragePeriod = Application.InputBox(prompt:= _
                "How long should the period of the moving average be?" _
                & vbNewLine & vbNewLine & _
                "(Give a value between 2 and 250)", Type:=1)
        
        'check if the user pressed cancel
        If movingAveragePeriod = 0 Then
            Exit Sub
        End If
        
        'ask the user to choose a color
            '(beside the colors defined in the defineColors sub this uses the default
            'excel colors (see resetToDefaultColors), i.e. if the user enters any other
            'number than 7-10)
         userColorIndex = Application.InputBox(prompt:= _
                "What color should the trendline be?" & vbNewLine & _
                "(give the answer as a number between 1-56)" _
                & vbNewLine & vbNewLine & _
                "7 = brown, 8 = black, 9 = olive green, 10 = dark aqua", Type:=1)
        
        'check if the user pressed cancel
        If userColorIndex = 0 Then
            Exit Sub
        End If
        
        'Call the code for turning UI updates off
        Call disableUIUpdating
        
        'count the rows in the logging data sheet
        rowcount = countDataRows("Logging data")
        
        'calculate the amount of series used
        seriesPerDataColumn = Floor(rowcount / 32000, 1) + 1
      
        'find out how many legend entries there are before we insert the trendlines
        Dim legendEntriesAmount As Integer
        legendEntriesAmount = ActiveChart.Legend.LegendEntries.Count
        
        Dim i As Integer
      
        'Call the code for turning UI updates on
        Call restoreUIUpdating
      
        'for all series in that data column
        For i = startSeriesIndex To (startSeriesIndex + seriesPerDataColumn - 1) Step 1
                
            'add a trendline
            ActiveChart.SeriesCollection(i).Trendlines.Add
            
            'make it moving average with given period and color
            With ActiveChart.SeriesCollection(i).Trendlines(1)
                .Type = xlMovingAvg
                .Period = movingAveragePeriod
                .Border.colorIndex = userColorIndex
            End With
            
            'delete extra legend entries, i.e. save only the first one of all the created
            If Not i = startSeriesIndex Then
                ActiveChart.Legend.LegendEntries(legendEntriesAmount + 2).Delete
            End If
        Next i
        
    End Sub
    <br/><br/>
    'findIndexOfselectedSeries: <br/>'goes through the seriesCollection of the activeChart until
    'it finds the series, then it returns the index to the first
    'part of the series (i.e. the first series in the data column
    'from which the selected series was drawn).
    
    'So if the series are named " I electrolyser,
    'I electrolyser 32000-64000,... and the "I electrolyser 32000-64000
    'series is selected then the function returns the series index
    'of the series with the name "I electrolyser", since the trendline
    'plotting needs to start from there.
    
    Function findIndexOfSelectedSeries() As Integer
    
        Dim i As Integer
    
        Dim sel As Series
        Set sel = Selection
    
        For i = 1 To ActiveChart.SeriesCollection.Count Step 1
        
            Dim active As Series
            Set active = ActiveChart.SeriesCollection(i)
    
            If Not InStr(1, sel.Name, active.Name, vbTextCompare) = 0 Then
                Exit For
            End If
           'Else take the next i
        Next i
        
        findIndexOfSelectedSeries = i
        
    End Function
    



     

    Thursday, January 26, 2012 10:40 AM

Answers

  • The Object Model does not make it easy to determine the legend entry with a particular series.

    Maybe you can simply delete legend entries such that the legend contains 1 more entry than number of series.

        With ActiveSheet.ChartObjects(1)
            Set chtTemp = .Chart
            Do While chtTemp.Legend.LegendEntries.Count > (chtTemp.SeriesCollection.Count + 1)
                chtTemp.Legend.LegendEntries(chtTemp.Legend.LegendEntries.Count).Delete
            Loop
        End With

    I still think your code has an issue with the .trendline.add and then code to explictly use trendline(1). This could well result in additional trendlines for a given series and a legend that is out of sync.

    You could also try deleting and reapply the legend, which should reset to all entries being present, and then remove excess.


    Cheers,

    Andy
    www.andypope.info

    • Marked as answer by akohonen Tuesday, January 31, 2012 8:15 AM
    Monday, January 30, 2012 4:20 PM

All replies

  • Would it not make more sense to use the Trendline collection of a series to determine number of trendlines and if required delete or add it. The legend should then take care of itself.

     


    Cheers,

    Andy
    www.andypope.info

    Thursday, January 26, 2012 10:57 AM
  • Thank you for your fast reply, but I am not sure if I get your point.

    I have a single trendline for each data series, and multiple data series. I want to delete the legend entries for some of the trendlines belonging to the series, while keeping the trendlines themselves on the chart.

    E.g. I have the data column I current with over 96000 rows. Thus it is plotted as 4 series with the names: "I current", "I current 32000-64000", "I current 64000-96000" and "I current 96000-12800". I plot all the series with the same color and remove the legend entries for all but the first series (i.e. "I current") so the chart legend does not get to crowded.

    Then I want to do the same for the legends, i.e. add trendlines for the series: "I current", "I current 32000-64000", "I current 64000-96000" and "I current 96000-12800" and then remove the legend entries for all but the first one.

    Does this make sense?

    Doesn't the trendlines collection represent the trendlines of a single series? Or am I wrong?

    - Axel

     

    Thursday, January 26, 2012 11:07 AM
  • Me again.

    I think I got your point with the trendlines collection. I assume you meant that I would use:

    ActiveChart.SeriesCollection(i).Trendlines(1).Delete

    But I do not want to delete the trendline, only the trendline legend.

    Therefore I wonder if you can access the trendline's legend through the trendlines collection? Something like this (which does not work):

    ActiveChart.SeriesCollection(i).Trendlines(1).Legend.Delete
    

    Thank you for the help!

    - Axel


    • Edited by akohonen Thursday, January 26, 2012 1:11 PM formatting
    Thursday, January 26, 2012 1:10 PM
  • The Object Model does not make it easy to determine the legend entry with a particular series.

    Maybe you can simply delete legend entries such that the legend contains 1 more entry than number of series.

        With ActiveSheet.ChartObjects(1)
            Set chtTemp = .Chart
            Do While chtTemp.Legend.LegendEntries.Count > (chtTemp.SeriesCollection.Count + 1)
                chtTemp.Legend.LegendEntries(chtTemp.Legend.LegendEntries.Count).Delete
            Loop
        End With

    I still think your code has an issue with the .trendline.add and then code to explictly use trendline(1). This could well result in additional trendlines for a given series and a legend that is out of sync.

    You could also try deleting and reapply the legend, which should reset to all entries being present, and then remove excess.


    Cheers,

    Andy
    www.andypope.info

    • Marked as answer by akohonen Tuesday, January 31, 2012 8:15 AM
    Monday, January 30, 2012 4:20 PM
  • Thank you again!

    I was able to get the trendline code working when I deleted the legend, inserted it again and then deleted the excess legend entries.

    However I would be interested to know why the trendlines.add fails to add a trendline legend entry for some of the series roughly 50% of the time. I tried some different ways of using the add function and referencing the trendlines, but none of them worked all the time, though it always worked the first time after a new chart was created.

    So if someone knows the answer to this please let me know :)

    - Axel

    Tuesday, January 31, 2012 8:15 AM