Check if Trendline Exists in VBA

Question

I have a sheet with a chart in it. I need to insert a button to TOGGLE the Trendline On/Off. So first, I need to check whether a trendline exists or not. Sounds simple, but is driving me NUTS!!!

Here is the code that I use to create the trednline and format it:

Sub Trending() 

Sheets("Sheet1").ChartObjects(1).Activate
    ActiveChart.SeriesCollection(1).Trendlines.Add
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    With Selection
        .Type = xlPolynomial
        .Order = 3
        .Border.ColorIndex = 3
        .Border.Weight = xlMedium

    End With

End Sub

To check for the existence of a trendline, I tried:

If Sheets("Sheet 1").ChartObjects(1).SeriesCollections(1).Trendlines(1).Count = 1 Then
    [Statement]
End If

But the test fails.

What am I doing wrong? What is a non-dirty way to do it?

Thanks, Al

Have you tried using `Trendlines.Count` instead of trying to index with `Trendlines(1)`? — Alex P, Apr 30 '12 at 16:05
From VBA Help: *Use the Trendlines method to return the Trendlines collection. The following example displays the number of trendlines for series one in Chart1.* `MsgBox Charts(1).SeriesCollection(1).Trendlines.Count` — Alex P, Apr 30 '12 at 16:06
I have tried this yes Remnant. Says "Subscript is out of range", even though I do have ONE chart on the sheet, with a trendline active on it. Really frustrating. — Alaa Elwany, Apr 30 '12 at 16:22
Minor point, but you code has an `s` on the end of `SeriesCollection` and I wonder whether this is a typo that is impacting your code? — Alex P, Apr 30 '12 at 16:27

score 6 · Accepted Answer · answered Apr 30 '12 at 16:37

There are three things wrong with your If statement. Had you broken down your statement in smaller bits and tested each of them separately, you would have found that:

It's Sheet1, not Sheet 1. No space. This is what causes your "Subscript is out of range" error.
The SeriesCollection property applies to Chart objects, not ChartObject object. Yes, I know this stupid Excel terminology is confusing. Anyhoo, you need ChartObjects(1).Chart.SeriesCollection(1), not ChartObjects(1).SeriesCollection(1).
Trendlines(1) returns a Trendline object, which is not Countable. You want to count the itemts in the trendlines collection, i.e. Trendlines.Count, not Trendlines(1).Count.

To summarise, this is how to write it:

If Sheets("Sheet1").ChartObjects(1).Chart _
    .SeriesCollection(1).Trendlines.Count > 1 Then
    MsgBox "there is exactly one trendline"
End If

Note: this will only work if the trendline count is exactly one. Consider replacing = with >= if there may be more than one.

Jean-Francois, your comments are very useful, for resolving this problem and for my VBA beginner knowledge in general. Thank you very much! — Alaa Elwany, Apr 30 '12 at 16:41
Sorry Jean-Francois, I am new to Stackoverflow. By upvoting, you mean setting that number on the left to "2" rather than "1" (is it kind of a score?). If so, I just did :-) Let me know if this is what you were referring to. You explained well and deserve the upvote. — Alaa Elwany, Apr 30 '12 at 17:16

Check if Trendline Exists in VBA

1 Answers1