Introducing Charts

 < Day Day Up > 



The chart itself is considered an object within the Excel workbook; the ChartObject object acts as a container for the items located within it. These items include the source data plotted on the Chart Area, the Legend, and the Chart Titles. Each object within the chart contains its own properties and methods. Because of the complexity of the chart Object Model, manipulating charts with VBA code can be a challenge. A good understanding of the Chart model is vital when programming Chart objects to ensure that you are using the correct object and property.

Note 

Refer to the 'Defining the Chart Object Model' section located later in this chapter to review how the Chart Object Model is structured.

Before diving into Chart properties, methods and events, it'll pay to review the individual components used to generate the chart. Table 15-1 explains the objects located within a chart that will be used throughout this chapter.

Table 15-1: Components Contained Within the Chart

Object

Description

Chart Titles

Describes the information that is being plotted. They include the actual title for the chart as well as the x-axis and the y-axis titles.

Data Series

Stores numerically the data in the ranges that define the chart and determines how the information is displayed in the Plot Area. A chart can contain a minimum of one plotted data series.

Legend

Provides the visual explanation of how data is plotted. The series name is listed with the corresponding color.

Plot Area

Displays the data series information graphically. It's the background, or container, that surrounds the plot area.

X-Axis and Y-Axis

Determines where the information will be grouped and plotted on the chart.

Creating Embedded Charts or Chart Sheets

As you are aware, you can create a chart using the Excel Chart Wizard. The wizard guides you through the selection of all available chart options, including Chart Type, Data Range, and Location. There are two locations in which a chart can be inserted: within a worksheet as an embedded object or on its own chart sheet.

Which destination you choose depends on the effect you are trying to accomplish. If you want the user of the workbook to be able to compare several charts side by side, insert the charts within the same worksheet. However, you should probably choose to insert a chart on its own sheet when working with a large, complex chart with many data series. There are other times you'll use individual chart sheets for preference reasons as well. Remember that you can always embed a chart later using a simple location change.

You aren't limited to using the Chart wizard to create a chart. They can also be created using VBA code. The Add property for the Charts object can be used to insert the new chart.

Charts.Add

The following example displays a basic macro that creates a chart as an object variable. The macro defines the Chart Type, Data Source, and Chart Titles.

Sub AddChartSheet()
Dim Chrt As Chart

Set Chrt = Charts.Add
With Chrt
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Sheet1").Range("A4:D7"), _
PlotBy:=xlRows
.HasTitle = True
End With
End Sub

Note 

When creating charts in your workbook, create the chart as an object variable. It's easier to reference the Chart object and manipulate the chart when using this technique. The remaining examples in this chapter will use this method to create charts.

By default, if the chart Location property has not been set in the macro, the chart is placed on its own sheet, rather than as an embedded object. To set the chart location, you must use the Location property. Table 15-2 defines the three location options available.

Table 15-2: Chart Location Properties

Property

Location

xlLocationAsNewSheet

The chart is placed on a new chart sheet.

xlLocationAsObject

The chart is placed as an embedded chart object within the specified worksheet.

xlLocationAutomatic

The chart is placed as an embedded chart object within the active worksheet.

The following example sets the chart as an embedded object in 'Sheet1':

Variable = ChartObject.Location(xlLocationAsObject,"Sheet1')

When working with embedded charts, it's a good idea to name the ChartObject object so that you can easily reference the chart in later code. To rename an existing chart manually, hold down the Ctrl key and click the chart. This will select the ChartObject object, rather than activate the chart. Click the Name Box, and type the new name. You are also able to name the ChartObject object within the macro by setting the Name property of the object.

As displayed in Figure 15-1, notice that the chart handles are unfilled circles to help you distinguish whether the chart is selected or has been activated. The Name Box currently displays Chart1 as the name of the ChartObject object.

click to expand
Figure 15-1: The Name Box displays the name of the ChartObject object when it's selected, rather than active on the screen.

Note 

If the chart has been activated, the chart handles are displayed as black boxes. However, if the ChartObject object is selected, the handles appear as unfilled circles.

The following example creates an embedded chart named GSCProductChart. The macro starts by deleting any existing embedded charts on the active worksheet. It then creates the new chart and uses the Parent property to identify the ChartObject object. The code proceeds to set the object variable Chrt to refer to the ChartObject object. Because the default is to create a chart sheet, the Location method is used to define the chart as an embedded object.

Sub AddEmbeddedChart()
Dim Chrt As Chart

ActiveSheet.ChartObjects.Delete
Set Chrt = Charts.Add
Set Chrt = Chrt.Location(where:=xlLocationAsObject, Name:="Sheet1")
With Chrt
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Sheet1").Range("A4:D7"), _
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "=Sheet1!R1C1"
With .Parent
.Top = Range("A9").Top
.Left = Range("A1").Left
.Name = "GSCProductChart"
End With
End With
End Sub

Note 

Remember the default location is a chart sheet. So when the Location method of the Chart object is used, the Chart object is re-created and any reference to the original Chart object, which is the chart sheet, is destroyed. It's necessary to assign the return value of the Location method to the Chrt object variable so that it refers to the new Chart object. To test this, step through your code and view the workbook. You'll notice that the chart sheet is initially created and then removed after the Location has been set to xlLocationAsObject.

As you can see, the event procedure AddEmbeddedChart has introduced some additional settings. The ChartTitle is assigned to a formula referring to cell A1. The location of the embedded chart on the worksheet was set using the Top and Left properties of cell A9. The Parent property of the Chart object was used to refer to the ChartObject object, and it was set by defining the Top and Left properties of the ChartObject object to be the same as the Top property of cell A9 and the Left property of cell A1. The chart is aligned with the top of cell A9, but the chart will align with the left edge of cell A1. The AddEmbeddedChart macro finally assigns the new name to the ChartObject object so that it can easily be referenced in the future.

Note 

When defining the chart title as a formula, you must use the R1C1 addressing method, not the A1 addressing method.

start sidebar
Inside Out
The Recorded Macro and Creating Charts

The recorded macro generates code that is reasonably efficient. However, manipulating the chart is easier if the chart is created as an object. The following example displays the recorded macro, which uses the Add method to create a new chart. The macro defines the ChartType property and then uses the SetSourceData method to define the ranges plotted on the chart. The Location property defines the chart as a chart sheet and assigns the name Product Sales to the sheet. Then the macro sets the HasTitle property to True so that it can define the ChartTitle property. Finally, the code sets the HasTitle property of the axes back to False, which is an unnecessary step.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:D7"), _
PlotBy:= xlRows

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Product Sales" With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Product Sales"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

The recorded macro will create the chart; however, you'll see that there are additional and redundant lines of code added to the macro. Be sure to remove unnecessary lines of code within a recorded macro.

end sidebar

Defining the Chart Object Model

The Chart Object Model at times can be overwhelming due to the layering effect. However, use the Object Browser in the Visual Basic Editor to help you get your bearings as you begin the task of coding procedures involving charts.

The hierarchy of a chart is determined by its location. For example, when working with an embedded chart, if you would like to modify the text contained within the ChartTitle you'll need to review the object levels. The top-level object is the Application. The Application object contains the Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which contains a Chart object. The Chart object has a ChartTitle object, and the ChartTitle object contains a Characters object. The Text property of the Characters object stores the text that is displayed as the chart's title. To summarize how each object is connected, refer to the following list:

Application
Workbook
Worksheet
ChartObject
Chart
ChartTitle
Characters

However, when working with a chart that is located on its own sheet you'll see that the hierarchy is simplified. A chart sheet is technically at the same level as the worksheet because it's simply a different type of sheet. Review the levels as indicated here, and notice that two levels have been removed.

Application
Workbook
Chart
ChartTitle
Characters

The Charts collection holds the collection of chart sheets in a workbook. The Workbook object is always the parent of the Charts collection. The Charts collection holds only the chart sheets. Because individual charts can also be embedded in worksheets and dialog sheets, the Chart objects in the Charts collection can be accessed using the Item property. Either the name of the chart can be specified as a parameter to the Item's parameter or it can be an index number describing the position of the chart in the workbook from left to right.

The Chart object allows access to all of the attributes of a specific chart in Excel. This includes chart formatting, chart types, and chart-positioning properties. The Chart object also exposes events that can be used programmatically.

Event procedures are executed when the appropriate trigger is initiated. Specific events can also be monitored at the Chart level. For more information, review the side bar, 'To Use or Not to Use Chart Events.' For a review of event procedures at the Application, Workbook, and Worksheet levels, refer to Chapter 12, 'Understanding and Using Events.'

The ChartObjects collection represents all the ChartObject objects on a specified chart sheet or worksheet. The ChartObject object acts as a container for a Chart object. Properties and methods for the ChartObject object determine the appearance and the size of the embedded chart on the worksheet. The ChartObject object is a member of the ChartObjects collection. The ChartObjects collection contains all the embedded charts on a single sheet.

Use ChartObjects(index), where index is the embedded chart index number or name, to return a single ChartObject object. In the following example, the chart name has been set to SampleChart in an embedded chart on the worksheet named 'Sheet1.'

 Worksheets('Sheet1').ChartObjects('Chart 1').Name = 'SampleChart'

The embedded chart name is shown in the Name box when the embedded chart is selected. Use the Name property to set or return the name of the ChartObject object. The following example puts rounded corners on the embedded chart named 'SampleChart' on the worksheet named 'Sheet1.'

 Worksheets('Sheet1').ChartObjects('SampleChart').RoundedCorners = True



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net