Embed A Chart Within A Worksheet

You can use VBA to embed a new chart to a worksheet in the existing workbook. When you embed a chart, Excel creates a new Chart object, which contains all the options that correspond to the chart. Each Chart object contains several objects that represent the settings for the chart, such as the ChartTitle object, which contains the chart title, its font and border properties, and other associated attributes. See the section "Chart Basics" for more information about the various child objects for the Chart object.

When you embed a chart on a worksheet, the corresponding Chart object that Excel creates becomes an actual part of the Worksheet object. Because you can place multiple embedded charts on one worksheet, the Worksheet object consists of a ChartObjects collection object that contains all Chart objects on the worksheet. Because of this, when you add and remove embedded charts, Excel requires you to use the ChartObjects collection object.

To add a chart to an existing worksheet, you must use the Add method with the ChartObjects object. The Add method has four optional parameter values, which help you indicate the location and size of the chart in points: Left, Top, Width, and Height. You use the Left parameter to specify the location of the chart in relation to the left edge of column A. You use the Top parameter to specify the location of the chart in relation to the top edge of row A. You use the Width parameter to indicate the initial width, and the Height parameter to specify the intial height of the chart object.

You specify the type of chart that Excel creates using the ChartType property. With this property you specify the chart type using one of the XlChhartType constant values. For example, to create a line chart, you use the constant xlLine. See Appendix A for the XlChartType constants.

EMBED A CHART WITHIN A WORKSHEET

EMBED A CHART WITHIN A WORKSHEET

—D Create a new subroutine.

L0 Dim EChart As ChartObject, replacing EChart with the name of the embedded chart variable.

-0 Type Set EChart = Sheets("Sheet1").ChartObjects .Add(), replacing Sheets("Sheet1") with the name of the worksheet to contain chart.

—D Create a new subroutine.

L0 Dim EChart As ChartObject, replacing EChart with the name of the embedded chart variable.

-0 Type Set EChart = Sheets("Sheet1").ChartObjects .Add(), replacing Sheets("Sheet1") with the name of the worksheet to contain chart.

'-Q Within the Add method parentheses, type Left:=50, Top:=30, Width:=400, Height:=400, replacing the numbers with the points measurement values.

Exir

WORKING WITH CHARTS

a The only real difference between embedded charts and chart sheets is the fact that the Chart object for an embedded chart is part of the ChartObject collection for the worksheet, whereas the Chart object for a chart sheet is part of the Workbook object. Other than that, if you compare the code that creates an embedded chart to the code that adds a new chart sheet, you may notice that specifying chart properties and methods requires reference to the Chart object. This is due to the fact that when you create a new chart sheet you create a new Chart object, but when you create an embedded chart you add a Chart object to the ChartObjects collection for a worksheet and, therefore, the Chart object becomes a child of the ChartObjects collection object. To set the chart type of an embedded chart, you specify the following:

Example:

Worksheets("Sheet1").ChartObject(1).Chart.ChartType = xlColumnStacked

This code sets the chart type of the first chart object in the worksheet named Sheet1 to a stacked column chart. If you compare that code to the code required for changing the chart type of a chart sheet, you see the similarities.

Example:

Sheets("Chart1").ChartType = xlColumnStacked

Type

EChart.Chart.SetSourceData Source:=Range("A1:D5"), replacing Range("A1:D5' with the range reference.

□ Switch to Excel and run the macro.

Type

EChart.Chart.SetSourceData Source:=Range("A1:D5"), replacing Range("A1:D5' with the range reference.

EChart.Chart.ChartType = xl3DColumn, replacing xl3DColumn with the xlChartType constant for the chart to create.

Note: See Appendix A for the xlChartType constant values.

□ Switch to Excel and run the macro.

-■ Excel embeds the new chart into the worksheet.

0 0

Responses

  • gustava
    How to embed a column chart within the worksheet?
    10 months ago

Post a comment