BET: Microsoft Excel Charts - VBA Code

Microsoft Office Development and Consultancy

|

Excel

|

|

|

|

|

|

|

Excel > Charts > VBA Code > Creating

< Previous | Next >

Charts.Add

	This will create a new chart on a chart sheet
	When you create a chart using the Add method of the Charts collection the chart is created on a chart sheet

1
2
3

ThisWorkbook.Charts.Add
Charts.Add
Charts.Add Before:=objWorksheet

ChartObjects.Add

	When you create a new embedded chart you are adding the chart to the ChartObjects collection for that particular worksheet.
	This method returns an Excel.ChartObject

	ChartObject.Add(Left, Top, Width, Height)
	Left - the number of pointd from the upper left corner of cell A1
	Top - the number of points from the ??
	Width - The width of the embedded chart
	Height - The height of the embedded chart

This will create a new embedded chart on the active worksheet

4	Set objChartObject = ChartObjects(20,20,20,20)

There is no Charts collection for a worksheet.

5
6
7

ChartObjects.Add(before,after,count)
ActiveSheet.ChartObjects.Add(left,top,width,height)
ActiveSheet.ChartObjects.Add(50,40,40,50)

You can then use the Location method to move to the chart to an embedded chart on a particular worksheet

The following line of code moves an embedded chart back to a chart sheet

8	Sheets("Sheet1").ChartObjects(1).Chart.Location Where:=xlLocationAsNewSheet, "MyChart"

Always work with a Chart Object

Whenever you are working with charts you want to refer to a chart object not a chartobject object.

9	Set objChart = objChartObject.Chart

© Better Solutions Limited 10-May-2013

< Previous | Top | Next >