excel-vba Charts and Charting Create a Chart by Modifying the SERIES formula
Example
For complete control over a new Chart and Series object (especially for a dynamic Series name), you must resort to modifying the SERIES formula directly. The process to set up the Range objects is straightforward and the main hurdle is simply the string building for the SERIES formula.
The SERIES formula takes the following syntax:
=SERIES(Name,XValues,Values,Order)
These contents can be supplied as references or as array values for the data items. Order represents the series position within the chart. Note that the references to the data will not work unless they are fully qualified with the sheet name. For an example of a working formula, click any existing series and check the formula bar.
Code to create a chart and set up data using the SERIES formula
Note that the string building to create the SERIES formula uses .Address(,,,True). This ensures that the external Range reference is used so that a fully qualified address with the sheet name is included. You will get an error if the sheet name is excluded.
Sub CreateChartUsingSeriesFormula()
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 refernce to chart object
Dim cht As Chart
Set cht = chtObj.Chart
'create the new series
Dim ser As Series
Set ser = cht.SeriesCollection.NewSeries
'set the SERIES formula
'=SERIES(name, xData, yData, plotOrder)
Dim formulaValue As String
formulaValue = "=SERIES(" & _
serName.Address(, , , True) & "," & _
xData.Address(, , , True) & "," & _
yData.Address(, , , True) & ",1)"
ser.Formula = formulaValue
ser.ChartType = xlXYScatterLines
End Sub
Original data and resulting chart
Note that for this chart, the series name is properly set with a range to the desired cell. This means that updates will propagate to the Chart.
