Étiquette : macro_graphics

Similar to the previous section 7.3.1, Managing Chart Sheets, here are three procedures for copying, deleting, and exporting an embedded chart.

Copying an Embedded Chart:

Sub CopyEmbeddedChart()
    With ThisWorkbook.Worksheets("Sheet1")
        .ChartObjects(1).Copy
        .Paste
        .ChartObjects(2).Top = 250
        .ChartObjects(2).Left = 200
    End With
End Sub

Explanation:
It is assumed that the worksheet « Sheet1 » initially contains only one chart frame. This chart frame is indexed as ChartObjects(1).

The Copy() method copies this chart frame (including its chart) to the clipboard.

The Paste() method pastes the copied chart frame back into the same worksheet, creating a new chart frame, which becomes ChartObjects(2).

The position of this new chart frame is set by modifying its Top and Left properties, placing it further down and to the right within the worksheet.

Deleting an Embedded Chart:

Sub DeleteEmbeddedChart()
    ThisWorkbook.Worksheets("Sheet1").ChartObjects(2).Delete
End Sub

Explanation:
The Delete() method removes the specified chart frame (here, the second chart frame on « Sheet1 ») without prompting for confirmation.

Exporting an Embedded Chart as an Image:

Sub ExportEmbeddedChart()
    ThisWorkbook.Worksheets("Sheet1").ChartObjects(1).Chart.Export "C:\Temp\April.jpg"
End Sub

Explanation:
The Export() method creates an image file from the chart contained within the specified chart frame. Here, it exports the chart from the first embedded chart frame as a .jpg file to the path C:\Temp\April.jpg.

Below are three procedures for copying, deleting, and exporting a chart sheet.

Copying a Chart Sheet:

Sub CopyChartSheet()
    ThisWorkbook.Charts("Chart1").Copy After:=Worksheets("Sheet3")
    ActiveChart.Name = "Chart1 Copy"
End Sub

Explanation:
A chart sheet can be copied just like a worksheet. The Copy() method creates a copy of the specified chart sheet, as shown in Figure 7.6. Using the Before or After parameters, you can specify the location of the copied sheet within the workbook. Without these parameters, Excel would create a new workbook containing only the copied chart sheet.

Deleting a Chart Sheet:

Sub DeleteChartSheet()
    ThisWorkbook.Charts("Chart1 Copy").Delete
End Sub

Explanation:
The Delete() method removes the specified chart sheet. Before deletion, Excel prompts the user for confirmation.

Exporting a Chart Sheet as an Image:

Sub ExportChartSheet()
    ThisWorkbook.Charts("Chart1").Export "C:\Users\POPOLY\Desktop\img.png"
End Sub

Explanation:
The Export() method generates an image file of the chart sheet. You must provide the filename, optionally including the directory path. The file extension determines the image format. Commonly supported formats include .jpg, .gif, and .png. The result is illustrated in Figure 7.7.

The program to modify an embedded chart is structured similarly to the one used for modifying a chart sheet; it also consists of two parts. Below is the part that applies exclusively to embedded charts:

Sub ModifyEmbeddedChart()
    Dim CO As ChartObject
    Dim CH As Chart
    Set CO = ThisWorkbook.Worksheets("Sheet1").ChartObjects(1)
    CO.Left = 220
    CO.Top = 30
    CO.Width = 400
    CO.Height = 300
    Set CH = CO.Chart
    ChangeChart CH
    Set CH = Nothing
    Set CO = Nothing
End Sub

Explanation:
A variable of type ChartObject is declared and assigned to the first chart frame on the worksheet named « Sheet1 » in the workbook.

Next, a variable of type Chart is declared and assigned the chart contained within this chart frame.

For embedded charts, it can be useful to adjust the position and size of the chart frame. This is done by modifying the Left, Top, Width, and Height properties of the ChartObject.

Finally, the (previously introduced) procedure ChangeChart() is called, with the reference to the chart passed as a parameter to apply the desired modifications.

First, the program code:

Sub ModifyChartSheet()
    Dim CH As Chart
    Set CH = ThisWorkbook.Charts(1)
    ChangeChart CH
    Set CH = Nothing
End Sub

This program is divided into two parts. The first part applies only to chart sheets:
A variable of type Chart is declared, and it is assigned to the first chart sheet in the workbook.
The procedure ChangeChart() is called, with the first chart sheet Charts(1) passed as a parameter.

The second part of the program applies both to chart sheets and embedded charts:

Sub ChangeChart(CH As Chart)
    ' Chart area formatting
    CH.ChartArea.Interior.Color = vbCyan
    ' Plot area formatting
    CH.PlotArea.Interior.Color = vbYellow
    ' Title
    CH.HasTitle = True
    CH.ChartTitle.Text = "Temperature"
    ' Legend
    CH.HasLegend = True
    With CH.Legend
        .Interior.Color = vbYellow
        .Border.Color = vbBlue
        .Border.Weight = xlThick
    End With
    ' Category axis
    With CH.Axes(xlCategory)
        .HasTitle = True
        .AxisTitle.Text = "Date"
        .TickLabels.NumberFormatLocal = "DD.MM."
    End With
    ' Value axis
    With CH.Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Text = "Degrees"
        .MinimumScale = 5
        .MaximumScale = 35
    End With
    ' Data series
    With CH.SeriesCollection(1)
        .Border.Color = vbRed
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerForegroundColor = vbRed
        .MarkerBackgroundColor = vbRed
    End With
    ' Data point
    With CH.SeriesCollection(1).Points(3)
        .Border.Color = vbBlue
        .ApplyDataLabels xlShowValue
        .MarkerStyle = xlMarkerStyleSquare
        .MarkerForegroundColor = vbBlue
        .MarkerBackgroundColor = vbBlue
    End With
End Sub

Changed chart area colors, legend, and data series

Modified axes and data point appearance

Explanation:
The ChartArea property represents the entire chart area, including the plot area, title, and legend. It has an Interior property that defines the fill (background) of this area, similar to how a cell’s interior can be formatted. In this example, the chart area’s interior color is set to cyan.

The PlotArea property represents the plot area specifically—the region where the data is graphed. Its Interior color is set to yellow here.

Charts may have a title, controlled by the Boolean property HasTitle. Setting this to True enables the title; False removes it. The title text is set using the ChartTitle.Text property.

Similarly, charts can include a legend, controlled by HasLegend. Its formatting is accessible via the Legend property, which exposes Interior and Border properties. These allow setting the background color and border color/thickness of the legend box.

All chart axes are contained in the Axes collection. Each axis is an object of type Axis. To select a specific axis, the Axes() method is called with parameters:

• xlCategory for the horizontal (category) axis
• xlValue for the vertical (value) axis

In this example, both axes have titles enabled (HasTitle = True), and the title text is assigned.
For the category axis, the tick label format is customized with NumberFormatLocal to display dates as “DD.MM.”.
For the value axis, the scale is set manually by defining minimum (MinimumScale = 5) and maximum (MaximumScale = 35) values.

The SeriesCollection holds all data series of a chart. Each series is a Series object. You access a series by its index starting at 1.
The series border color (which for line charts affects the line color) is set to red here. Marker appearance for data points on the series is customized with properties like MarkerStyle (e.g., circle), MarkerForegroundColor, and MarkerBackgroundColor.

The Points collection contains all data points in a series. Each data point is a Point object. You select a specific point by index.
In this example, the third data point’s border color is changed to blue. Data labels showing values are applied with ApplyDataLabels xlShowValue. The marker style is set to square with blue foreground and background colors.

Embedded charts exist within the object hierarchy beneath worksheets. The Worksheets collection contains a sub-collection called ChartObjects, which includes all the chart frames on a given worksheet. Each ChartObject represents a single chart frame. The actual chart itself is an object of type Chart and corresponds to the Chart property of a chart frame. Apart from this organizational structure, both embedded and chart sheet charts function similarly.

The following procedure creates a simple line chart embedded directly into the worksheet named « Sheet1 »:

Sub CreateEmbeddedChart()
    Dim CO As ChartObject
    Dim CH As Chart
    Set CO = ThisWorkbook.Worksheets("Sheet1").ChartObjects.Add(200, 10, 300, 150)
    Set CH = CO.Chart
    CH.ChartType = xlLine
    CH.SetSourceData Worksheets("Sheet1").Range("A1:C8")
    Set CH = Nothing
    Set CO = Nothing
End Sub

The result is shown in Figure 7.3.
Figure 7.3 illustrates the embedded chart.

Explanation:
First, a variable of type ChartObject is declared. This variable will later reference the newly created chart frame.

Next, a variable of type Chart is declared. This will refer to the chart inside the newly created chart frame.

The ChartObjects collection holds all embedded chart frames on a worksheet. The Add() method creates a new chart frame at a specified position and size on the worksheet, returning a reference to this newly created object. The four parameters of Add() represent:

• The distance in points from the left edge of the worksheet to the left edge of the chart frame
• The distance in points from the top edge of the worksheet to the top edge of the chart frame
• The width of the chart frame in points
• The height of the chart frame in points

The Chart property of a ChartObject accesses the actual chart contained within the frame.

The ChartType property specifies the type of the chart, here set to xlLine for a line chart.

The SetSourceData() method assigns the data source for the chart. In this example, the source data is the range A1:C8 on the active worksheet.

Charts placed on their own sheets (called chart sheets) exist at the same hierarchical level as worksheets within a workbook. There are several collections within a workbook that list different types of sheets:

• Sheets – contains all worksheets and chart sheets
• Worksheets – contains only worksheets (as commonly known)
• Charts – contains only chart sheets

The following procedure creates a simple line chart as a new chart sheet within the workbook:

Sub CreateChartOnNewSheet()
    ThisWorkbook.Charts.Add After:=Worksheets("Sheet1")
    With ActiveChart
        .ChartType = xlLine
        .SetSourceData Worksheets("Sheet1").Range("A1:C8")
        .Name = "Chart1"
    End With
End Sub

Explanation:

The Add() method of the Charts object creates a new chart sheet and adds it to the Charts collection of the active workbook. Similar to copying or moving worksheets, you can specify the position of the new sheet using the Before or After parameters. If no position is specified, the new sheet is inserted before the currently active sheet.

The newly created chart sheet is of type Chart and automatically becomes the active chart sheet. Therefore, it can be accessed using ActiveChart.

The ChartType property sets the type of the chart—in this case, xlLine for a line chart. Some commonly used chart types are listed below.

The SetSourceData() method defines the data source for the chart. Here, the data range is cells A1 to C8 on the worksheet named « Sheet1, » which contains the temperature data.

You can assign a name to the chart using the Name property.

Note:
The SetSourceData() method has a second optional parameter that specifies whether the chart plots data by columns (xlColumns, which is the default) or by rows (xlRows).

Table 7.1 lists several chart types and their corresponding ChartType property values:

(Table 7.1: Chart Types)

Embedded charts exist within the object hierarchy beneath worksheets. The Worksheets collection contains a sub-collection called ChartObjects, which includes all the chart frames on a given worksheet. Each ChartObject represents a single chart frame. The actual chart itself is an object of type Chart and corresponds to the Chart property of a chart frame. Apart from this organizational structure, both embedded and chart sheet charts function similarly.

The following procedure creates a simple line chart embedded directly into the worksheet named « Sheet1 »:

Sub CreateEmbeddedChart()
    Dim CO As ChartObject
    Dim CH As Chart
    Set CO = ThisWorkbook.Worksheets("Sheet1").ChartObjects.Add(200, 10, 300, 150)
    Set CH = CO.Chart
    CH.ChartType = xlLine
    CH.SetSourceData Worksheets("Sheet1").Range("A1:C8")
    Set CH = Nothing
    Set CO = Nothing
End Sub

Explanation:
First, a variable of type ChartObject is declared. This variable will later reference the newly created chart frame.

Next, a variable of type Chart is declared. This will refer to the chart inside the newly created chart frame.

The ChartObjects collection holds all embedded chart frames on a worksheet. The Add() method creates a new chart frame at a specified position and size on the worksheet, returning a reference to this newly created object. The four parameters of Add() represent:

• The distance in points from the left edge of the worksheet to the left edge of the chart frame
• The distance in points from the top edge of the worksheet to the top edge of the chart frame
• The width of the chart frame in points
• The height of the chart frame in points

The Chart property of a ChartObject accesses the actual chart contained within the frame.

The ChartType property specifies the type of the chart, here set to xlLine for a line chart.

The SetSourceData() method assigns the data source for the chart. In this example, the source data is the range A1:C8 on the active worksheet.