Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I think the problem is in .SetSourceData ??
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 its 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 Ive tried has passed the VBA error checker. Does anyone have any thoughts on this? Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I think the problem is in .SetSourceData ??
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" wrote: 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 its 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 Ive tried has passed the VBA error checker. Does anyone have any thoughts on this? Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I think the problem is in .SetSourceData ??
Hi Joel,
Thanks very much. Keith "joel" wrote: 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" wrote: 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 its 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 Ive tried has passed the VBA error checker. Does anyone have any thoughts on this? Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
defined names and setsourcedata | Charts and Charting in Excel | |||
setSourcedata | Excel Programming | |||
charting problem with activechart.setsourcedata | Excel Discussion (Misc queries) | |||
SetSourceData for Chart | Excel Programming |