Excel VBA .AddChart Method 'SetSource Data' of Object' _chart' failed

Question

I'm trying to insert a chart into my spreadsheet, as outlined in msdn here:

https://msdn.microsoft.com/en-us/library/bb238877(v=office.12).aspx

Unfortunately i get the error in the

Method 'SetSource Data' of Object' _chart' failed

on the line starting ActiveChart. Why is it doing this? i've tried both string and range variables here to no avail.

In addition to the fact that i can't get this method to work, i don't like the fact you need to select the select the graph, surely there is a better way?

Function TimeSeries(rngToPrint As Range)
    Dim strRange As String
    Dim rngChart As Range

    lngstartrow = 8
    lngendrow = Range("a10000").End(xlUp).Row
    Range("$A$" & CStr(lngstartrow) & ":$B$" & CStr(lngendrow)).Select
    Sheets(rngToPrint.Worksheet.Name).Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("$A$" & CStr(lngstartrow) & ":$B$" & CStr(lngendrow)), PlotBy:=xlLine

End Function

user6585761 · Accepted Answer · 2016-07-22 16:55:00Z

2

The PlotBy argument specifies whether to plot the data by rows or columns. So the argument should be set to either xlRows or xlColumns.

answered Jul 22, 2016 at 16:55

user6585761

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2016-07-22 18:13:49Z

0

The error appears at the following line because there is no PlotBy:=xlLine

Modify it to:

ActiveChart.SetSourceData Source:=Range("$A$" & CStr(lngstartrow) & ":$B$" & CStr(lngendrow)), PlotBy:=xlRows

Or either:

ActiveChart.SetSourceData Source:=Range("$A$" & CStr(lngstartrow) & ":$B$" & CStr(lngendrow)), PlotBy:=xlColumns

answered Jul 22, 2016 at 18:13

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Collectives™ on Stack Overflow

Excel VBA .AddChart Method 'SetSource Data' of Object' _chart' failed

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related