I think the problem is in .SetSourceData ??

[keith]

Hello,
I am using VBA to create scatterplot charts. There are two series’ on each
chart. The VBA is working well, now, except that sometimes, in an
unpredictable way, the program is plotting a third series, where the X, Y
values are only 1, and 1. On the scatterplot they appear as a single dot in
the 1,1, position. At other times, the 1,1 point is plotted on a new chart
that is never defined in my code. When this happens, the chart is most
often, a single vertical bar at the location where the X value is 1.

As near as I can tell, this may have to do with the fact that my VBA does
not use the following statement (or something like it) to specify the source
data:

Charts(1).SetSourceData Source:=Sheets(1).Range("a1:a10"), _
PlotBy:=xlColumns

The statement is not used because my source data ranges are set by code,
later in the subroutine. When I record a macro, so I can look at the VBA
that is generated, the .SetSourceData method corresponds to the second Chart
Wizard dialogue box that asks a user to specify a data range, and whether the
data range is in columns or rows. However, when specifying a scatterplot
using the wizard, the data range in the second chart wizard dialog is
irrelevant. Instead, one should define the two series in “series†Tab, not
the “Data Range†tab.

So, if my assumption is true, and it’s the .SetSourceData method that is
causing the problem, what would be a way to fix it? Using the research
tools, and by recording a macro, all I see as parameters for the
..SetSourceData method are “Source:†and “PlotBy:â€

So far, nothing I’ve tried has passed the VBA error checker.

Does anyone have any thoughts on this?

Keith

 

[joel]

keith: there are two ways of generating multiple series charts. the first is
the simple verrsion of just specifying the range of data and if you have more
than 2 columns (or rows) excel is smart enough to know your data is more than
one series. the other method is to creatte a chart iwth one series
collection and then add more series.

If you are getting one of the series with only a single point then you need
to check the range of your chart. Don't create the chart until you know the
entire size of the chart and then set the size using the chart wizard 9or any
method you choose). You chang echeck the size of the chart like this

Set chartRange = range("A1:C25")
msgbox chartrange.address

 

[Keith]

Hi Joel,
Thanks very much.
Keith

keith: there are two ways of generating multiple series charts. the first is
the simple verrsion of just specifying the range of data and if you have more
than 2 columns (or rows) excel is smart enough to know your data is more than
one series. the other method is to creatte a chart iwth one series
collection and then add more series.

If you are getting one of the series with only a single point then you need
to check the range of your chart. Don't create the chart until you know the
entire size of the chart and then set the size using the chart wizard 9or any
method you choose). You chang echeck the size of the chart like this

Set chartRange = range("A1:C25")
msgbox chartrange.address