Manipulating Charts

 < Day Day Up > 



A procedure can be used to create a chart; however, you'll commonly create macros that will modify existing charts. For example, a procedure can be used to streamline the formatting of all the embedded charts within a workbook for the Garden Company. The procedure might include resizing all charts to a standard size, specifying the chart location within a worksheet, or even adding the company name in a label using the company colors.

Activating a Chart

A chart is activated when a user selects a chart, regardless of its location. Using VBA code, you can activate an embedded chart using the Activate method.

ActiveSheet.ChartObjects("Chart1").Activate 

To activate a chart on an individual chart sheet, you can use the following statement:

Sheets('Chart1').Activate

When a chart is activated, you can refer to it in your code as ActiveChart. This is a great way to simply your code. For troubleshooting purposes, you can verify which chart has been activated by adding a message box to display the chart name. Once you have verified that the correct chart has been activated, you can add a single quote at the beginning of the line that calls the message box to make the line a comment. Making the line a comment means the line won't be executed. You can, of course, delete the code after you have completed testing the procedure.

MsgBox ActiveChart.Name

When creating a procedure that modifies a chart, you are not required to activate it, but this is an easy way to refer to the chart in your code. The following examples modify the chart type and return the same results, but the first procedure activates the chart sheet and the second procedure accesses an embedded chart:

Sub ModifyChart1()
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.Type = xlLine
ActiveChart.Deselect
End Sub

Sub ModifyChart2()
ActiveSheet.ChartObjects("Chart1").Chart.Type = xlLine
End Sub

Keep in mind when creating your macro that if the procedure is written so that it's dependent on the chart being activated, an error will occur if the user hasn't activated the chart. The following example displays several different formats that can be changed. Try executing the macro with the chart active, as well as with cell A1 selected.

Sub ModifyActiveChart()
With ActiveChart
.Type = xlArea
.ChartArea.Font.Name = "Tahoma"
.ChartArea.Font.FontStyle = "Regular"
.ChartArea.Font.Size = 8
.PlotArea.Interior.ColorIndex = xlNone
.Axes(xlValue).TickLabels.Font.Bold = True
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
End With
End Sub

Notice that run-time error ‘91': Object variable or With block variable not set occurs when you execute the ModifyActiveChart procedure and the chart is not selected. To solve this problem, specify which chart to modify when the procedure is executed. Modify the previous procedure to include the chart reference.

Sub ModifySpecificChart()
With Sheets("Sheet1").ChartObjects("Chart1").Chart
.Type = xlArea
.ChartArea.Font.Name = "Tahoma"
.ChartArea.Font.FontStyle = "Regular"
.ChartArea.Font.Size = 8
.PlotArea.Interior.ColorIndex = xlNone
.Axes(xlValue).TickLabels.Font.Bold = True
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
End With
End Sub

start sidebar
Inside Out
To Use or Not to Use Chart Events

An event is used to monitor an object. Your chart is considered an object regardless of its location. So if you have a specific event that in turn requires a specific action, you should use Chart events.

Some of the triggers available for a Chart object are Activate, MouseDown, MouseMove, and SeriesChange. To write an event procedure for an embedded chart, you must create a new object using the WithEvents keyword in a class module and declare an object of type Chart with events.

Consider the following example. Assume a new class module is created and named EventClassModule. The new class module contains the following WithEvents statement:

Public WithEvents myChartClass As Chart

After the new object has been declared with events, it appears in the Object drop-down list in the class module. You can now create an event procedure for this object. However, before the procedure will run, you must connect the declared object with the embedded chart. The following code can be used in any module to achieve the required result:

Dim myClassModule As New EventClassModule

Sub InitializeChart()
Set myClassModule.myChartClass = Worksheets(1).ChartObjects(1).Chart
End Sub

After the InitializeChart procedure has been executed, the myChartClass object in the class module points to the first embedded chart on the first worksheet in the workbook. All event procedures in the class module for the object will now be evaluated as the triggers occur.

end sidebar

Deactivating a Chart

When a macro is created using the recorder, you'll find the recorder generates a statement, such as

ActiveWindow.Visible = False

This statement deactivates the chart, but it's not clear as to why the chart is deactivated. When writing a macro that involves charts, try using the Deselect method.

ActiveChart.Deselect

These two statements have slightly different results. Setting the Visible property of the ActivateWindow object to False causes the embedded chart to be selected but no longer activated. The Deselect method will deactivate and deselect the chart.

Modify the AddEmbeddedChart event procedure that was created in the 'Creating Embedded Charts or Chart Sheets' section earlier in the chapter by setting the ActiveWindow object to False to deactivate the chart.

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
ActiveWindow.Visible = False
End Sub

Now that you have tested the ActiveWindow method to deactivate the chart, use the deselect method. Replace the ActiveWindow.Visible = False line with ActiveChart.Deselect.

For each procedure you create, you can evaluate which method of deactivation will achieve the required results for your current scenario.

Troubleshooting

start example

How Can I Determine If a Chart Has Been Activated? A macro can manipulate an active chart, which has been selected by the user. For example, a macro might change the chart type, apply colors, or change the font size. The question is, how can you determine using VBA code whether the user has actually selected the chart? The chart could be selected by activating the chart sheet or activating an embedded chart by clicking it. If the chart that needs to be activated is on a chart sheet, you can use the following line of code to determine if it has been selected:

TypeName(Selection) = "Chart"

However, if the chart is an embedded object, the actual selection would be an object within the chart. For example, the object could be a Series object or a ChartTitle object. The ChartIsSelected function returns True if a chart sheet or an embedded chart is activated and False if a chart is not activated. The following function determines if the ActiveChart object is Nothing. If it equals Nothing, the chart is not activated.

Private Function ChartIsSelected() As Boolean
ChartIsSelected = Not ActiveChart Is Nothing
End Function

end example

Modifying a Chart's Data Series

A chart consists of any number of series, and the data used in each series is determined by the range references in its SERIES formula. When a series is selected in the chart, the SERIES formula is displayed in the formula bar. In some cases, using range names in the SERIES formulas in a chart can simplify things when you need to change the chart's source data. For example, consider the following SERIES formula:

=SERIES(,Sheet1!$A$1:$A$6,Sheet1!$B$1:$B$6,1)

You can define range names for the ranges and then edit the SERIES formula so that it uses the range names instead of the range references. For example, if you have two ranges that are named Categories and Data you can modify the formula to

=SERIES(,Sheet1!Categories,Sheet1!Data,1)
Note 

This formula might change when you type it in the formula bar. For example, Sheet1 might evaluate to the name of the workbook, producing an argument such as Products.xls!Categories.

When the names have been defined and the SERIES formula has been edited, your VBA code can work with the names, and the changes will be reflected in the chart. For example, the following statement redefines the range as Data:

Range('B1:B12').Name = 'Data'

After executing the Name statement, the chart will update itself and use the new definition of Data.

Now that you understand what the SERIES formula is responsible for, how do you modify it? The easiest method is to redefine the chart data using the SetSourceData method of the Chart object. However, you can manipulate individual series using the Series object. The Series object is a member of the chart's SeriesCollection object.

start sidebar
Inside Out
How the Chart SERIES Formula Works

The data used in each series in a chart is determined by its SERIES formula. When you select a data series in a chart, the SERIES formula appears in the formula bar. The SERIES formula is not a formula that you are able to enter into a worksheet as a traditional formula. Basically, you can't use it in a cell, nor can you use a function or formula within a SERIES formula. It's possible, however, to edit the SERIES formula.

The Catalog Sales series is currently selected on the chart, indicated by the handles on the Catalog plotted points. Notice the formula bar contains the SERIES formula for the Catalog series.

click to expand
Figure 15-2: The selected data series is indicated by marks on the corresponding chart elements.

A SERIES formula uses the following syntax:

=SERIES(name,category_labels,values,order)
  • name (Optional) The name used in the legend. If the chart has only one series, the name argument is used as the title.

  • category_labels (Optional) The range that contains the labels for the category axis. If omitted, Excel uses consecutive integers beginning with 1.

  • values The range that contains the values.

  • order An integer that specifies the plotting order of the series (relevant only if the chart has more than one series).

    Range references in a SERIES formula are always absolute, and they always include the sheet name, as in the following example:

    =SERIES(,Sheet1!$B$1,Sheet1!$B$2:$B$7,1)

    A range reference can consist of a noncontiguous range. If so, each range is separated by a comma, and the argument is enclosed in parentheses. In the following SERIES formula, the values' ranges consist of B2:B3 and B5:B7:

    =SERIES(,,(Sheet1!$B$2,Sheet1!$B$5:$B$7),1)

    You can substitute range names for the range references. If you do so, Excel changes the reference in the SERIES formula to include the workbook, as in the following example:

    =SERIES(Sheet1$B$1,,budget.xls!MyData,1)
end sidebar

Modifying a Chart to Use Data from Arrays

A chart series can be defined by assigning a VBA array to its Values property. This is useful if you need to generate a chart that is not linked to the original data. The chart can be distributed in a separate workbook that's independent of the source data.

Figure 15-3 displays the Garden Company Product Sales Chart with the Catalog series selected. Notice the difference between this graphic and the graphic located within the Inside Out: 'How the Chart SERIES Formula Works' that displayed the Catalog series using information from Sheet1. You can see the definition of the first data series in the formula bar above the worksheet. The values on the y-axis are defined by an Excel array. The category names have been assigned as text to the series names.

click to expand
Figure 15-3: The Catalog series is displayed with the SERIES formula in the Formula Bar.

Note 

The array is limited to approximately 250 characters. This limits the number of data points that can be plotted using an array.

You can easily convert an existing chart to use arrays instead of cell references. This will make the chart independent of the original data it was based on. The following code shows how to achieve this effect:

Sub ConvertSeriesValuesToArrays()
Dim Ser As Series
Dim Chrt As Chart

On Error GoTo Failure

Set Chrt = ActiveSheet.ChartObjects(1).Chart
For Each Ser In Chrt.SeriesCollection
Ser.XValues = Ser.Values
Ser.Name = Ser.Name
Next Ser

Exit Sub
Failure:
MsgBox "The data exceeds the array limits."
End Sub

For each series in the chart, the XValues and Name properties are set equal to themselves. Although these properties can be assigned range references, they always return an array of values when they are referenced. This behavior can be used to convert the cell references to arrays.

Keep in mind that the number of data points that can be contained in an array reference is limited to approximately 250 characters. The code will fail if the limits are exceeded, so use an error trap to cover this possibility.

Defining a Chart's Labels

Adding data labels to a chart is easy, as long as the labels are based on the data series values or x-axis values. These options are available using the Chart menu and Chart Options.

You can also enter your own text or formula into each label, but this involves a lot of manual work. You would need to add standard labels to the series and then individually select each one and either replace it with your own text or click in the formula bar and enter a formula. To save time and effort, you can write a macro to achieve the same results.

Figure 15-4 displays the Garden Product Sales Chart with the Monthly Sales and the top selling product. The labels have been defined by formulas linked to row 4 of the worksheet, and as you can see, Fertilizer was the top-selling product in April. The formula in the formula bar points to cell E4.

click to expand
Figure 15-4: The labels have been programmed with formulas to point to a cell in the worksheet.

For example, set up a line chart similar to Figure 15-4. Add the following macro to add the chart labels that correspond to the top-selling products in row 4:

Sub AddDataLabels()
Dim seSales As Series
Dim Pts As Points
Dim pt As Point
Dim rng As Range
Dim i As Integer

Set rng = Range("B4:G4")
Set seSales = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
seSales.HasDataLabels = True
Set Pts = seSales.Points
For Each pt In Pts
i = i + 1
pt.DataLabel.Text = "=" & rng.Cells(i).Address(RowAbsolute:=True, _
ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=True)
pt.DataLabel.Font.Bold = True
pt.DataLabel.Position = xlLabelPositionCenter
Next pt
End Sub

The object variable rng is assigned a reference to B4:G4. The seSales series is assigned a reference to the first and only series in the embedded chart, and the HasDataLabels property of the series is set to True. The For Each…Next loop processes each point in the data series. For each point, the code assigns a formula to the Text property of the point's data label. The formula refers to the worksheet cell as an external reference in the R1C1 format. The data label is also boldfaced, and the label positioned above the data point.

Formatting a Chart

Chart formatting is a broad category. You are able to format the ChartObject itself, as well as all the objects contained within the chart. For example, you may modify the ChartObject Location, such as changing an embedded chart to its own chart sheet. You may also classify modifying the color selection for the data series as chart formatting. There are more than 60 chart properties available; Table 15-3 lists the commonly used Chart property names and the results returned.

Table 15-3: Chart Properties

Name

Returns

Description

ChartType

xlChartType

Used to set the chart type or return the current chart type.

HasDataTable

Boolean

Used to set whether the associated data table will be displayed on the chart. The default is set to False, so the data table is not included if this property is not set.

HasLegend

Boolean

Used to set whether the legend will be displayed.

HasTitle

Boolean

Used to set whether the chart will display a chart title.

PlotBy

xlRowCol

Used to set whether columns in the original data are used as individual data series (xlColumns) or if the rows in the original data are used as the data series (xlRows).

Table 15-4 lists the commonly used ChartObject property names and their results.

Table 15-4: ChartObject Properties

Name

Returns

Description

BottomRightCell

Range

Returns the single cell range located under the lower right corner of the embedded ChartObject.

Chart

Chart

Returns the actual chart associated with the ChartObject.

Height

Double

Sets the height of the embedded chart.

Left

Double

Sets the distance from the left edge of the margin to the left edge of the ChartObject.

Name

String

Sets the name of the ChartObject.

PrintObject

Boolean

Sets whether the embedded object will be printed when the worksheet is printed.

RoundedCorners

Boolean

Sets whether the embedded chart will have rounded corners. By default, the property is set to False, which displays right-angled corners.

Shadow

Boolean

Sets whether a shadow will appear around the embedded chart.

Top

Double

Sets the distance of the Top edge of the ChartObject to the top of the worksheet.

Visible

Boolean

Sets whether the ChartObject is visible.

Width

Double

Sets the width of the embedded chart.

Each object within the chart has a series of properties as well. For example, the ChartTitle object can have a navy blue 2 point border or no border with a shadow. The ChartTitle can be set using the position properties, such as Left and Top. In fact, the orientation can also be set. When it comes to formatting a chart by enhancing the cosmetic appearance, the possibilities are almost endless. Before programming the macro, modify an existing chart and document the changes you have made to the chart. Now based on the sample chart and your documentation, add the appropriate lines of code to your procedure.

Consider this scenario. The Garden Company has a Product Sales chart that is created monthly. Each month several modifications need to be applied after the chart has been created. To ensure consistency to the charts, you decide to create a procedure that applies the required formats. Figure 15-5 shows the desired end result. Notice that the chart type is set to 3D Clustered Bar, the Data Table is visible, and the Legend has been removed.

click to expand
Figure 15-5: The sample formatting for the Garden Supply Company Monthly Sales Chart.

The following procedure will modify the chart shown in Figure 15-5 to include the formatting that was determined necessary:

Sub FormatChart()
Dim chrt As Chart
Set chrt = ActiveSheet.ChartObjects(1).Chart
chrt.ChartType = xl3DBarClustered
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
End Sub

Modifying All Charts in a Workbook

There will be times where you want to establish consistency throughout your workbook by applying the same formats to all charts. You can specify whether to apply the formats to all ChartObjects or just to a specific ChartObject type, such as embedded charts or chart sheets. To achieve this result, use a For…Next loop to cycle through each object in the ChartObjects collection, and then accesses the Chart object in each and change its ChartType property. The following example changes all charts on the active worksheet to an Area chart:

Sub ChangeChartType()
Dim chtobj as ChartObject
For Each chtobj In ActiveSheet.ChartObjects
chtobj.Chart.ChartType = xlArea
Next chtobj
End Sub

Printing Charts

When printing a chart sheet, by default it will print on its own page. However, when working with embedded charts, you need to determine if the chart should be printed on its own page or with the worksheet. For a user to print an embedded chart on its own page, they are required to first select the chart and then choose File, Print. This will allow the embedded chart to be printed as if it were on a chart sheet.

The following procedure demonstrates how to preview all embedded charts in a worksheet as full pages. To send the charts to the default printer, change the PrintPreview method to PrintOut.

Sub PrintEmbeddedCharts()
For Each chtObj In ActiveSheet.ChartObjects
chtObj.Chart.PrintPreview
Next chtObj
End Sub

In contrast, if you would like to print the embedded chart as an object with the contents of the worksheet, you can simply print the worksheet and the chart will be included automatically. However, there might be situations where you want to exclude the chart when printing the worksheet. To accomplish this task successfully, you must set the PrintObject property for the ChartObject to False. By default, the ChartObject is included when printing a worksheet, so the only time you need to set the PrintObject property is when you need to exclude the embedded charts from the print job.

The following procedure prints the active worksheet and excludes all chart objects. Substitute the PrintPreview property to PrintOut to send the print job to the default printer.

Sub PrintWorksheetOnly()
For Each chtObj In ActiveSheet.ChartObjects
chtObj.PrintObject = False
Next chtObj
ActiveSheet.PrintPreview
End Sub



 < 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

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