PDA

View Full Version : [SOLVED] Chart in chart sheet - assign array values



krishnak
05-15-2015, 08:58 AM
Hi All,

I am creating a new chart sheet through VBA and I have two arrays for XValues and Values. For an embedded chart we can add a SeriesCollection and assign the array values for X and Y Axes.
For example -
SeriesCollection(1).XValues = xArr
SeriesCollection(1).Values = yArr

For a blank chart sheet, how do we add a new SeriesCollection and assign values through arrays as above?
The documentation for chart sheets says only about SetSourceData method for which the source is a Range.
Any assistance is appreciated.

Kenneth Hobs
05-15-2015, 09:55 AM
Sub ken() Dim c As Chart
Dim s As Series
Dim myData As Variant

Set c = ActiveChart ' Assumes a chart is currently active in Excel...
Set s = c.SeriesCollection(1)

myData = Array(9, 6, 7, 1) ' or whatever
s.Values = myData
End Sub

krishnak
05-15-2015, 10:45 AM
Thanks for the tip.
When I created a new blank Chart sheet, the chart does not have any SeriesCollections.
So I have to add
Set s = ActiveChart.SeriesCollection.NewSeries
Then the rest of the code works fine. Otherwise, we get an error.