Adding an Embedded Chart Using VBA Code

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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.

Was this article helpful?

+1 0
The Accidental Blogging Millionaires

The Accidental Blogging Millionaires

Get Inspired By The Most Popular Bloggers Online! If You Want To Skyrocket Your Success With Business And Improve Your Overall Life You Need To Have A Look At The Accidental Blogging Millionaires! Business can be a fight, particularly when you’re trying to establish one online and like all fights, to succeed you must find the winning techniques and apply them.

Get My Free Ebook

Post a comment