How do I add or remove a series in an excel x-y plot in VB macro? 1

[navyguy]

Hi, I have a vb program running in an excel macro. The macro outputs a number of series to an excel spreadsheet which is then viewed in an xy plot. The trouble I am having is that the number of series I want to plot changes depending on the user input to the macro. The length of each series is also variable. The number of series and the length of each series is known after the macro is run. How can I add or remove series to what is referenced in the xy plot from the vb macro? Also, how can I chance the length of each series from vb.

Any help would be appreciated.

 

[RobBroekhuis]

You can add series using

activechart.seriescollection.add
(look up in Excel VBA help for a complete specification of parameters)

deleting series number i:

activechart.seriescollection(i).delete

to (re)set the data range, you can use the

activechart.seriescollection(i).formula

property. You'll have to look at how the formulas are formatted (you can see them by clicking on a series on the chart and looking at the formula bar), and create the string in VBA. If you need help with any of the above, let us know.


Rob

 

[navyguy]

Thankyou Rob. I started to do what you suggest. One thing I may need help on is converting a known starting reference and an number of rows and columns to the appropriate text string (ie knowing its 6 series that are 1131 long and converting that to A1:F1131)

 

[RobBroekhuis]

A trick that I often use is to use the address property of a properly defined range object, e.g.

range(cells(1,1),cells(1131,6)).address

will give

"$A$1:$F$1131"

It's easier than trying to format the string yourself.
Rob

 

[navyguy]

Wow, thats quick turn around. Thanks for the tip. I tried it got the error "1004 - Method 'Cells' of object '_Global' failed" Any idea what that might be?

Pasted below is a portion of my code. I hope to replace the 7 and the 1136 in the cell references with a number variable.

Sheets("plots&quot.Select
ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.SetSourceData Source:=Sheets("current output&quot.Range(Cells(1, 2), Cells(7, 1136)).Address _
, PlotBy:=xlColumns

 

[RobBroekhuis]

The error is because your active sheet is a chart, and the cells object is a property of the activesheet object. Also, the setsourcedata method uses a range, not a string representation of a range, as its parameter. Modify your code as follows:

with Sheets("current output&quot
ActiveChart.SetSourceData Source:=.Range(.Cells(1, 2), .Cells(7, 1136))
end with

Rob