Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel - changing series source.
I have a problem with changing the data source for a chart series.
I'm using a VBA program to input data to a database (really just a worksheet) then filter, select and manipulate the data, and graph the end results. I currently have a scatter chart which I've only got a single series on. There is also a 3-point moving average trendline and Y error bars (linked to a range in the worksheet). Changing the data source for this has been easy with the following: Charts(1).SetSourceData Source:=Range("Dataset!AH3:AI28") Fine. Great. Now I've added a second series to the chart, and I can't figure out how to tell the computer to change the data source for series 1 to X:Y and change the data source to series 2 to A:B. I've tried: Charts(1).SeriesCollection(1).SetSourceData Source:=Range("Dataset!AH3:AI28") But that crashes with "object doesn't support this property or method". Charts(1).SeriesCollection(1).Source = Range("Dataset!AH3:AI28") has the same problem. Intuitively it should work, but obviously I've got the syntax horribly wrong! I then recorded a macro of me changing the values manually and got the code: ActiveChart.SeriesCollection(1).XValues = "=Dataset!AH3C33:AH28C33" But for some reason when I run that in the program that says: "Error 1004: unable to set the Xvalues property of the series class". I cannot see where this "C33" bit came from - makes no sense to me. Having it or not makes no difference. That implies that I actually have the syntax correct but something else is stopping the code from executing. I have the code "Charts(1).Activate" prior to all of this to ensure it's looking at the right place. For a little while I had the same problem changing the title of the chart, but fixed that. I can't for the life of me remember how... I would suspect a data type mismatch, but that wouldn't make sense if I could change the data source using the other bit of code! The program is horribly complicated and the actual graphing is the easy bit, or so I thought. I don't, repeat DON'T, want to add a new series to the chart, because otherwise I'll have a dozen or more lines running across it. I just want to change the data source for an EXISTING series. I can do it for a single series using the Charts(1).SetSourceDate code but don't see a way to access individual series in the same way. The program is designed to be very dynamic - graphing multiple interpretations of the dataset within a few seconds/minutes instead of taking a few days by hand: hence adding new series with every iteration isn't going to be helpful. When the chart is created I load it into the actual interface Form as the background picture of a label and save/print it together with a description of the filtered dataset and all the filters/modifications I've applied. The graph really needs to be as tidy as possible. If it helps, my X values are always the same, for both series. I really only need to change the Y values. I guess if it comes to the worst I can set up the ranges manually and just move numbers in and out of them using the program, but the fact that I can't do this has begun to bug me ;o) Every other problem I've had I've managed to solve myself or looking online, but not this one. Help would be appreciated. I'm using Win2000, VB 6.3.8863, Excel 2002 SP2. Also WinXP, Excel 2002 SP3, VBA 6.4.8869 for some of the time (i.e. working from home!). Cheers Bennett |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel - changing series source.
Nick -
SetSourceData tells the chart what its entire range should be. This is fine if the ranges are all neatly aligned, and all series use the same range for their X values. What you need is to set the X and Y ranges for each series separately: With Chart(1).SeriesCollection(1) .Values = Worksheets("DataSet").Range("AI3:AI28") .XValues = Worksheets("DataSet").Range("AH3:AH28") .Name = Worksheets("DataSet").Range("AI2") End With - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Nick Bennett wrote: I have a problem with changing the data source for a chart series. I'm using a VBA program to input data to a database (really just a worksheet) then filter, select and manipulate the data, and graph the end results. I currently have a scatter chart which I've only got a single series on. There is also a 3-point moving average trendline and Y error bars (linked to a range in the worksheet). Changing the data source for this has been easy with the following: Charts(1).SetSourceData Source:=Range("Dataset!AH3:AI28") Fine. Great. Now I've added a second series to the chart, and I can't figure out how to tell the computer to change the data source for series 1 to X:Y and change the data source to series 2 to A:B. I've tried: Charts(1).SeriesCollection(1).SetSourceData Source:=Range("Dataset!AH3:AI28") But that crashes with "object doesn't support this property or method". Charts(1).SeriesCollection(1).Source = Range("Dataset!AH3:AI28") has the same problem. Intuitively it should work, but obviously I've got the syntax horribly wrong! I then recorded a macro of me changing the values manually and got the code: ActiveChart.SeriesCollection(1).XValues = "=Dataset!AH3C33:AH28C33" But for some reason when I run that in the program that says: "Error 1004: unable to set the Xvalues property of the series class". I cannot see where this "C33" bit came from - makes no sense to me. Having it or not makes no difference. That implies that I actually have the syntax correct but something else is stopping the code from executing. I have the code "Charts(1).Activate" prior to all of this to ensure it's looking at the right place. For a little while I had the same problem changing the title of the chart, but fixed that. I can't for the life of me remember how... I would suspect a data type mismatch, but that wouldn't make sense if I could change the data source using the other bit of code! The program is horribly complicated and the actual graphing is the easy bit, or so I thought. I don't, repeat DON'T, want to add a new series to the chart, because otherwise I'll have a dozen or more lines running across it. I just want to change the data source for an EXISTING series. I can do it for a single series using the Charts(1).SetSourceDate code but don't see a way to access individual series in the same way. The program is designed to be very dynamic - graphing multiple interpretations of the dataset within a few seconds/minutes instead of taking a few days by hand: hence adding new series with every iteration isn't going to be helpful. When the chart is created I load it into the actual interface Form as the background picture of a label and save/print it together with a description of the filtered dataset and all the filters/modifications I've applied. The graph really needs to be as tidy as possible. If it helps, my X values are always the same, for both series. I really only need to change the Y values. I guess if it comes to the worst I can set up the ranges manually and just move numbers in and out of them using the program, but the fact that I can't do this has begun to bug me ;o) Every other problem I've had I've managed to solve myself or looking online, but not this one. Help would be appreciated. I'm using Win2000, VB 6.3.8863, Excel 2002 SP2. Also WinXP, Excel 2002 SP3, VBA 6.4.8869 for some of the time (i.e. working from home!). Cheers Bennett |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel - changing series source.
Jon Peltier wrote in message ...
Nick - SetSourceData tells the chart what its entire range should be. This is fine if the ranges are all neatly aligned, and all series use the same range for their X values. Thanks for that: it's what I suspected, hence the need for some kind of series-specific code! What you need is to set the X and Y ranges for each series separately: With Chart(1).SeriesCollection(1) .Values = Worksheets("DataSet").Range("AI3:AI28") .XValues = Worksheets("DataSet").Range("AH3:AH28") .Name = Worksheets("DataSet").Range("AI2") End With With a little tweaking I think I've got it to work: minor hiccup though, it should read With ChartS(1)... which had me head-scratching for a bit ;-) I was under the impression that the With commands were only for setting up a chart at the start of a program or something, but it seems reasonably happy with changing it on the fly. Note for any readers with the same/similar problems that I expect it only works if you've already established a series the the sort of errors I was getting imply that unless you've previously established a NewSeries with a range etc you can't then go in and change it - since my chart and series pre-exists that criteria is filled. This is probably obvious to anyone with much experience with VBA but I'm learning as I go! I'm pretty sure I should be okay from here on. Thanks very much for the assistance. Cheers Bennett |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Excel - changing series source.
No, With/End With is a very handy construction in VB, and can be used anywhere a
block of code would otherwise have lots of repetition of the same object references. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Nick Bennett wrote: Jon Peltier wrote in message ... Nick - SetSourceData tells the chart what its entire range should be. This is fine if the ranges are all neatly aligned, and all series use the same range for their X values. Thanks for that: it's what I suspected, hence the need for some kind of series-specific code! What you need is to set the X and Y ranges for each series separately: With Chart(1).SeriesCollection(1) .Values = Worksheets("DataSet").Range("AI3:AI28") .XValues = Worksheets("DataSet").Range("AH3:AH28") .Name = Worksheets("DataSet").Range("AI2") End With With a little tweaking I think I've got it to work: minor hiccup though, it should read With ChartS(1)... which had me head-scratching for a bit ;-) I was under the impression that the With commands were only for setting up a chart at the start of a program or something, but it seems reasonably happy with changing it on the fly. Note for any readers with the same/similar problems that I expect it only works if you've already established a series the the sort of errors I was getting imply that unless you've previously established a NewSeries with a range etc you can't then go in and change it - since my chart and series pre-exists that criteria is filled. This is probably obvious to anyone with much experience with VBA but I'm learning as I go! I'm pretty sure I should be okay from here on. Thanks very much for the assistance. Cheers Bennett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2000: Changing path of an external data source | Excel Discussion (Misc queries) | |||
Changing a Data Source in Excel 2000 | Excel Discussion (Misc queries) | |||
Changing series information for approximately 60 series in a workb | Charts and Charting in Excel | |||
Excel changing number formatting and source data in graphs on it's own!!! | Excel Discussion (Misc queries) | |||
VBA Excel - changing series source. | Excel Programming |