excelvbaexcel-chartstrendline

Invalid parameter error when method is called from another sub


I have the below code that creates charts from some worksheets and put the charts in their own worksheets.

When I run the macro on it's own it works perfectly, but when I use Call InsertDNCCharts from another macro I get an "Invalid Parameter" error on .Period = 7 from within the With tl block.

Why is there a difference?
If the code runs on its own, shouldn't it run the same way when called from another sub?

Sub InsertDNCCharts()

   Dim ws As Worksheet
   Dim cws As Worksheet
   Dim country As String
   Dim lastrow As Long
   Dim chrt As Shape
   Dim chrtname As String
   Dim xvalues As Range
   Dim yvalues As Range
   Dim tl As Trendline

   For Each ws In ThisWorkbook.Worksheets

       If Right(ws.Name, 6) = "_Chart" Then
           country = Left(ws.Name, Len(ws.Name) - 6)
           Set cws = ThisWorkbook.Worksheets(country)

           lastrow = cws.Cells(Rows.count, "c").End(xlUp).Row
           Set xvalues = cws.Range("c5:c" & lastrow)
           Set yvalues = cws.Range("l5:l" & lastrow)

           cws.Activate
           Application.Union(xvalues, yvalues).Select
           Set chrt = cws.Shapes.AddChart2(201, xlColumnClustered, Cells(5, 2).Left, Cells(5, 2).Top, 1000, 420)

           chrt.Name = ws.Name
           chrtname = chrt.Name
           cws.Cells(5, 1).Select

           With chrt.Chart
               .Location Where:=xlLocationAsObject, Name:=ws.Name
               .Axes(xlCategory).HasMajorGridlines = True
               .Axes(xlCategory).HasMinorGridlines = False
               .Axes(xlValue).HasMajorGridlines = True
               .Axes(xlValue).HasMinorGridlines = False
               .HasLegend = False
           End With

           ws.ChartObjects(chrtname).Activate
           ActiveChart.ChartWizard Title:=country & " Daily New Cases (DNC)"

           Set tl = ws.ChartObjects(chrtname).Chart.SeriesCollection(1).Trendlines.Add

           With tl
               .Type = xlMovingAvg
               .Period = 7                '*******Error on this line. Debug says period=2, which is the default moving average period.
               .DisplayEquation = False
               .DisplayRSquared = False
               .Format.Line.DashStyle = msoLineSysDot
               .Format.Line.Weight = 3.5
               .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
               .Format.Line.Style = msoLineSingle
           End With
       End If
   Next ws
End Sub

Solution

  • If the chart in discussion (the created one) has at least 7 points, it is possible that the code is not referring to the appropriate chart, or the chart has not been created as necessary.

    In order to check that, I would suggest you putting a break point on line With tl and visually check if the active chart is the one you need and if it looks as expected. It looks that the problem has to be before the line raising the error.