Creating

There is no Charts collection for a worksheet, only a ChartObjects collection.
It does not matter which method you use to create a chart, the chart will be added to the worksheets ChartObjects collection.
Whenever you are working with charts you want to always refer to an actual Excel.Chart object.


Shapes.AddChart2

Excel 2013 introduced this syntax

Shapes.AddCharts2(ChartStyle, ChartType, Left, Top, Width, Height) 

ChartStyle - (Long) no enumeration
ChartType - xlChartType enumeration
Left - the position of the left corner of the chart relative to the anchor (in points)
Top - the position of the top corner of the chart relative to the anchor (in points)
Width - The width of the embedded chart (in points)
Height - The height of the embedded chart (in points)

Dim oShape As Excel.Shape 
Set oShape = ActiveSheet.Shapes.AddChart2()

Dim oChart As Excel.Chart
Set oChart = oShape.Chart

Shapes.AddChart

Excel 2007 introduced this syntax.

Shapes.AddChart(ChartType, Left, Top, Width, Height) 

ChartType - xlcharttype enumeration
Left - the position of the left corner of the chart relative to the anchor (in points)
Top - the position of the top corner of the chart relative to the anchor (in points)
Width - The width of the embedded chart (in points)
Height - The height of the embedded chart (in points)

Dim oShape As Excel.Shape 
Set oShape = ActiveSheet.Shapes.AddChart()

Dim oChart As Excel.Chart
Set oChart = oShape.Chart

ChartObjects.Add

This creates an embedded chart on a worksheet of whatever chart type you have set as the default.
This method returns an Excel.ChartObject

ChartObjects.Add(Left, Top, Width, Height) 

Left - the number of points from the upper left corner of cell A1
Top - the number of points from the ??
Width - The width of the embedded chart (in points)
Height - The height of the embedded chart (in points)

Dim oChartObject As Excel.ChartObject 
Set oChartObject = ChartObjects.Add()

Dim oChart As Excel.Chart
Set oChart = oChartObject.Chart

Charts.Add

This will create a new chart on a chart sheet
When you create a chart using the Add method of the Charts collection the chart is created on a chart sheet

ThisWorkbook.Charts.Add 
Charts.Add
Charts.Add Before:=oWorksheet

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext