How to add new data points to existing series?

[Kelvin Stott]

Hello,

I would like to add new data points, one by one, to an existing data series...

Is there any way to do this in VBA using a construct similar to adding a new series (SeriesCollection.NewSeries), such as SeriesCollection(1).NewPoint?

Thanks for any help!

Kelvin

 

[JLGWhiz]

Hello,

I would like to add new data points, one by one, to an existing data series...

Is there any way to do this in VBA using a construct similar to adding a new series (SeriesCollection.NewSeries), such as SeriesCollection(1).NewPoint?

Thanks for any help!

Kelvin

Here is a snippet that I had in one of my files. You can probably adapt a similar procedure.

nxtSer = Worksheets(1).ChartObjects(1).Chart.SeriesCollection(2).Points.Count 'Counts existing data series points.
 If nxtSer > 0 And nxtSer < 12 Then 'Makes sure at least one exists but does not exceed the chart parameter.
            Select Case nxtSer  'Uses select case to determine which series point needs to be added to series array.
                Case 1
                    Ser = "$M$5"
                Case 2
                    Ser = "$M$7"
                Case 3
                    Ser = "$M$9"
                Case 4
                    Ser = "$M$11"
                Case 5
                    Ser = "$M$13"
                Case 6
                    Ser = "$M$15"
                Case 7
                    Ser = "$M$17"
                Case 8
                    Ser = "$M$19"
                Case 9
                    Ser = "$M$21"
                Case 10
                    Ser = "$M$23"
                Case 11
                    Ser = "$M$25"
     End Select
 End If
ch7.SeriesCollection.Extend Worksheets(1).Range(Ser) 'Adds the appropriate cell to the array for ch7 object variable.

BTW, these data points are non-contiguous, although that is inconsequential, thought I point it out.

 

[Kelvin Stott]

Thanks! Can the .Extend method be used with just one series, rather than the entire series collection?

 

[JLGWhiz]

yes, there is only one series in the snippet illustrated. It just adds a data point based on the conditions in the select case. I use it to update a chart on a monthly basis. When the current month is Complete, it will add the data point for the next month. That way my chart does not have a long line across at a zero value.

 

[Kelvin Stott]

Great, thank you!

 

[JLGWhiz]

You're welcome and good luck.
Regards, JLG