Creating a Chart in Excel 2007:-

In previous versions of Excel, you used the Charts.Add command to add a new chart. You then specified the source data, the type of chart, and whether the chart should be on a new sheet or embedded on an existing worksheet. The first three lines of the following code create a clustered column chart on a new chart sheet. 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 on sharing your macros with people who still use Excel 2003, you should use the Charts.Add method. However, if your application will only be running in Excel 2007, you can use the new AddChart method. The code for the AddChart method can be as simple as the following:

Create chart on the current sheet

ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData Source: =Range(“A1:E4”)

ActiveChart.ChartType = xlColumnClustered