Adding Datapoint to Excel Chart with VBA

Question 2

You can set the values on an individual series by using the value property on the series object.

However, in Help, it states that the values in a series can either be

a range on a worksheet or an array of constant values,

but not both.

This means that if you want to specify the series values as a range such as C1:C10, then I think you'll have to add cells if you want to add data points in the series.

If you don't want to add a cell, then you have to specify all values as an array constant.

Question 1

To add data points to a specific series, I believe you would have to select the series, and modify the Values and XValues properties.

Example:

Put this data in the "Sheet1" of Excel and graph it as "Chart1". y1 will be series 1, y2 will be series2 and y3 will be series 3.

     A    B     C     D
1    x    y1    y2    y3
2    1    10    100   400
3    2    20    200   500
4    3    30    300   600

Now, lets add a data point to y2.

     A    B     C     D
1    x    y1    y2    y3
2    1    10    100   400
3    2    20    200   500
4    3    30    300   600
5    4          1000

We have to select the series (by number or by name, in this case, 2 or "y2") and set the Value property to "C2:C5"

'using ranges
Charts("chart1").SeriesCollection("y2").Values = Worksheets("Sheet1").Range("C2:C5")

'using array constant
Charts("chart1").SeriesCollection("y2").Values = Array(100, 200, 300, 1000)

We'll also change the XValues property so that every Value has an XValue

'using ranges
Charts("chart1").SeriesCollection("y2").XValues = Worksheets("Sheet1").Range("A2:A5")

'using array constant
Charts("chart1").SeriesCollection("y2").XValues = Array(1, 2, 3, 4)

Note:

We can have Values as a range and XValues as an array constant or vice versa.

We can also have both Values and XValues as ranges or both as array constants.

We cannot have Values as a range and an array constant.

We cannot have XValues as a range and an array constant.