Last time we learnt how to create a chart using Excel VBA. Today we learn how to embed a chart in an Excel worksheet using VBA.
A chart on its own chart sheet is a Chart object. A chart embedded on a worksheet is a ChartObject object
Chart sheets are a member of workbook’s Charts Collection. Each ChartObject embedded on a worksheet is a member of worksheet’s ChartObjects collection.
Based on the above facts we will now write our VBA code to embed a chart on an Excel worksheet:
‘We Declare an Object variable for the Chart and for the embedded ChartObject.
Dim myChart As Chart, cht As ChartObject
‘Next we declare a Range variable to specify the range that the chart will occupy and on which worksheet.
Dim rngChart As Range, destinationSheet As String
‘we will place the chart on the active worksheet.
destinationSheet = ActiveSheet.Name
‘we add a new chart
Set myChart = Charts.Add
‘we specify the chart’s location as the active worksheet.
Set myChart = myChart.Location(Where:=xlLocationAsObject, Name:=destinationSheet)
‘Here we define the new chart’s source data
myChart.SetSourceData Source:=Range(“A1”).CurrentRegion, PlotBy:=xlColumns
‘We Define the type of chart as a Column chart.
myChart.ChartType = xlColumnClustered
‘We activate the ChartObject. The 1 assumes this is the first (index no. 1) chart object on the worksheet.
‘A Range object’s parent is the worksheet. Here the ActiveChart is the parent of the ChartObject cht
Set cht = ActiveChart.Parent
‘Now we specify the range we want the embedded chart to occupy and the exact position
Set rngChart = Range(“D5:F10”)
cht.Left = rngChart.Left
cht.Top = rngChart.Top
cht.Width = rngChart.Width
cht.Height = rngChart.Height
‘we deselect the active chartobject
Watch the Excel training video:
Understanding Object Parents
Chart – add embedded chart via VBA