Programming Excel with VBA and .NET

Embed Charts

Charts may exist as separate sheets, as shown in the preceding section, or they can be embedded on a worksheet using the ChartObjects collection. To embed a chart quickly, use the Add method to create a new ChartObject, then use that object’s Chart property to control the underlying chart:

 Sub EmbedChart(  )
    Dim ws As Worksheet, co As ChartObject, chrt As Chart
    Set ws = ActiveSheet
    ' Create an embedded chart object.
    Set co = ws.ChartObjects.Add(40, 160, 400, 200)
    ' Name the ChartObject so it's easy to get later.
    co.Name = "FL Median Home Prices"
    ' Get the underlying Chart object.
    Set chrt = co.Chart
    ' Plot the chart using the ChartWizard method 
.
    chrt.ChartWizard [HomeSales], xlLine, , xlColumns, 1, 1, True, _
      "FL Median Home Prices"
End Sub

The ChartObject is simply a container for the chart on the worksheet. You use it to set the size and position of the chart on the worksheet, but for anything else you use the underlying Chart object. For example, the following code gets the Chart object from the embedded chart and rotates through different chart types:

Sub ChangeEmbeddedChart( ) Dim ws As Worksheet, chrt As Chart Set ws = ActiveSheet ' Get the chart. Set chrt = ws.ChartObjects("FL Median Home Prices").Chart ' Change the chart type and title. chrt.ChartWizard , xlBar, , xlColumns, 1, 1, True, _ "Bar Chart" Application.Wait Now + 0.00003 chrt.ChartWizard , xlBar, 8, xlColumns, 1, 1, True, _ "FL Median Home Prices (Bar 8)" Application.Wait Now + 0.00003 chrt.ChartWizard ...