Adding an Embedded Chart Using VBA Code

The following code creates a Shape object containing the required chart:

Sub AddChart()

Dim shp As Shape

'Delete any existing ChartObjects On Error Resume Next ActiveSheet.ChartObjects.Delete On Error GoTo 0

'Create new embedded chart

Set shp = ActiveSheet.Shapes.AddChart

'Position Shape over range With Range("F3:M19") shp.Top = .Top shp.Left = .Left

shp.Height = .Height shp.Width = .Width End With

With shp

'Assign name to Shape containing chart .Name = "MangoesChart"

With .Chart

'Specify source data and orientation

.SetSourceData Source:=Sheets("Sales").Range("A3:D7")

PlotBy:=xlRows .ChartType = xlColumnClustered

'Add a title and assign it a value .SetElement msoElementChartTitleCenteredOverlay .ChartTitle.Text = "Mangoes"

End With

End With

End Sub

AddChart first deletes any existing ChartObjects. It then sets the object variable shp to refer to the Shape object added using the AddChart method. Although the Shape is also a ChartObject, the AddChart method returns a Shape object. There is no AddChart method for the ChartObjects collection.

AddChart aligns the Shape with F3:M19 by assigning the Top, Left, Width, and Height property values of the range to the same properties of the Shape, and then applies the name "MangoesChart" to the Shape. The Chart property of the Shape object is then used to return a reference to the embedded chart and the properties set, as you have seen previously.

+1 0

Post a comment