Excel 2010 Macro Create a Chart
In legacy versions of Excel, you used the Charts Add command to add a new chart. Then, you would specify the source data, type of chart, and whether the chart should be on a new sheet or embedded on an existing worksheet.
Create a clustered column chart on a new chart sheet with the first three lines of the code below. The fourth line moves the chart back to be an embedded object in Sheet1:
Charts.Add
ActiveChart.SetSourceData Source:=Worksheets(“Sheet1”).Range(“A1:E4”)
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:=”Sheet1”
If you plan to share your macros with people who still use Excel 2003, you should use the Charts Add method. But if your application will be running only Excel 2007 or Excel 2010, you can use the new AddChart method.
The code for the AddChart method is as simple as below:
‘ Create chart on the current sheet
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(“A1:E4”)
ActiveChart.ChartType = xlColumnClustered
Alternatively, you can specify the chart type, size, and location as part of the AddChart method.