excel-vba Charts and Charting Creating a Chart with Ranges and a Fixed Name
Example
Charts can be created by working directly with the Series object that defines the chart data. In order to get to the Series without an exisitng chart, you create a ChartObject on a given Worksheet and then get the Chart object from it. The upside of working with the Series object is that you can set the Values and XValues by referring to Range objects. These data properties will properly define the Series with references to those ranges. The downside to this approach is that the same conversion is not handled when setting the Name; it is a fixed value. It will not adjust with the underlying data in the original Range. Checking the SERIES formula and it is obvious that the name is fixed. This must be handled by creating the SERIES formula directly.
Code used to create chart
Note that this code contains extra variable declarations for the Chart and Worksheet. These can be omitted if they're not used. They can be useful however if you are modifying the style or any other chart properties.
Sub CreateChartWithRangesAndFixedName()
Dim xData As Range
Dim yData As Range
Dim serName As Range
'set the ranges to get the data and y value label
Set xData = Range("B3:B12")
Set yData = Range("C3:C12")
Set serName = Range("C2")
'get reference to ActiveSheet
Dim sht As Worksheet
Set sht = ActiveSheet
'create a new ChartObject at position (48, 195) with width 400 and height 300
Dim chtObj As ChartObject
Set chtObj = sht.ChartObjects.Add(48, 195, 400, 300)
'get reference to chart object
Dim cht As Chart
Set cht = chtObj.Chart
'create the new series
Dim ser As Series
Set ser = cht.SeriesCollection.NewSeries
ser.Values = yData
ser.XValues = xData
ser.Name = serName
ser.ChartType = xlXYScatterLines
End Sub
Original data/ranges and resulting Chart after code runs
Note that the SERIES formula includes a "B" for the series name instead of a reference to the Range that created it.
