Microsoft Office Automation with Visual FoxPro

The Chart object

We ve covered many of the basics of the Chart object: how to add one, and how to use the ChartWizard method. Now we can look at the object in more detail. Many of the Chart object s properties and methods reference other objects and collections. The ChartArea and PlotArea properties reference the ChartArea and PlotArea objects, and are always available.

Some objects are not always available. Chart titles, legends, and axes are optional components. The Chart object has properties to determine whether these objects are available. The three main properties that allow access to the optional components are:

• HasAxis. If true, the Axes method is available to access the Axes collection.
• HasLegend. If true, the Legend property is available to access the Legend object.
• HasTitle. If true, the ChartTitle property is available to access the ChartTitle object.

There are a few properties that don t access objects. These global properties, shown in Table 2, affect the whole chart.

Table 2. Chart object properties. These properties do not reference other objects; they affect the look of the chart.

Several methods take care of some cool features; these are discussed in the appropriate sections toward the end of this chapter. These are GetChartElement, PrintOut, PrintPreview, SaveAs, and Export. The SetBackgroundPicture method is discussed with the ChartArea object, as it affects the formatting of the chart area.

Chart types

We ve touched on chart types a bit in the ChartWizard section. The ChartWizard method uses two parameters, a Chart Type and a Chart Format, which combine to determine how the chart looks. The results of this combination are stored as a single number in the Chart object s ChartType property (certain formats also may set some properties of the Axis and Series objects, too).

Unfortunately, we know of no algorithm to convert from the ChartWizard parameters to the ChartType values, which is why we ve enumerated the values in Table 1 and Appendix B.

For each of the values, Excel has a defined constant. To conserve space in Table 1, we listed only the value. Check out the Object Browser for more information. Some of the constants are: xl3DColumn (-4100), xl3DColumnClustered (54), xlBarClustered (57), xlConeBarClustered (102), xlCylinderColClustered (92), and xlLineMarkers (65).

What are those cone and cylinder constants? Actually, you can add pyramid constants, too. These charts are not readily available to the ChartWizard. Here s a case where you need to manually set the graph type, to show the bars (or columns) as cones, cylinders, or pyramids, examples of which are shown in Figure 5. So, to set a graph to a columned cluster of cylinders, issue the following:

#DEFINE xlCylinderColClustered 92

oExcel.ActiveChart.ChartType = xlCylinderColClustered

Figure 5. Examples of different shapes for 3D bar and column charts. Cylinders, cones, and pyramids are available to add interest to your presentations.

Actually, if you examine Appendix B, you ll find that only 33 of the 73 ChartType values are represented. The ChartWizard is excellent for setting up the most common kinds of charts, but you need to manually set the ChartType for some of the uncommon graphs.

Another way to get the cylinder, cone, and pyramid shapes in bar and column charts is to set the ChartType as a 3D bar or column chart, and then set the BarShape property. When the BarShape property is set, it updates the ChartType property to reflect the new ChartType value.

Chart object properties available only to 3D charts

Some objects and properties are available only if the chart has 3D properties. The Floor and Walls properties reference Floor and Walls objects, respectively. The walls are the sides of the 3D chart, and the floor is the bottom. These objects are similar and contain two main properties to format the objects. The Border property and the Interior property explained later in this chapter in "The Border object" and "The Interior object" sections, respectively control all of the formatting. Generally, you will want to set these using the same properties as for PlotArea.

The DepthPercent property stores the depth of the chart (along the Z-axis) as a percentage of the chart width. Valid values range from 20 to 2000. The HeightPercent property stores the height of the chart as a percentage of the chart width. Valid values are between 5 and 500 percent.

Two properties set the angle at which you view the chart. The Elevation is the height at which you view the chart, in degrees. Figure 6 shows two graphs one with the elevation set to 10, and the other with it set to the maximum value of 90. The default is 15 degrees for most chart types. The valid range for the Elevation property is -90 to 90, except for bar charts, which can range from 0 to 44.

Figure 6. Illustrating the Elevation property. Elevation is the height at which you view the chart. The chart on the left has the Elevation property set to 10, which is near eye-level. The chart on the right shows the maximum elevation, or 90, which is looking down onto the chart. The minimum value is -90, or looking from below the chart.

The Rotation property determines how far the chart is rotated left or right (around the Y-axis), in degrees. Valid values range from 0 to 360, except for bar charts, which again can range from 0 to 44. The default is 20 for all charts. Figure 7 shows two charts with different rotations.

Figure 7. Rotating the chart. The chart on the left shows the default rotation of 20 degrees, while the chart on the right shows a rotation of 40 degrees.

The charts shown in Figure 7 are drawn in a style called isometric projection. This 3D drawing style is used for construction drawings, because accurate measurements can be made from anywhere on the chart. This is controlled by the RightAngleAxes logical property; when true, isometric projection is used it s characterized by putting the axes at right angles to each other. When it s false, you can set the Perspective property, which takes a value between 0 and 100. Perspective is another 3D drawing style where lines that are parallel in reality, like the horizontal grid lines, are not drawn parallel and meet at a point called the vanishing point. This drawing method is used for illustration, and measurements aren t accurate because items in the foreground are necessarily larger than those in the background. Perspective is the technique that makes 3D illustrations look more realistic. When you set the value to 0, the vanishing point is far in the distance, making the lines look parallel. When set to the maximum value of 100, the vanishing point is close to the graph, which greatly distorts the graph and over-emphasizes the large end of the graph. The default value is 30. Figure 8 shows two graphs one has its Perspective property set to 25, and the other has it set to 100. Compare these graphs to the graphs shown in Figure 7, which have the RightAngleAxes property set to true and use the isometric projection.

Figure 8. A difference in perspective. Using perspective enhances the 3D illusion, as in the chart on the left (compare to the boxy look in Figure 7). The chart on the right shows the maximum perspective, which gives a very different effect.

ChartArea

The ChartArea is the area containing the entire chart. As mentioned previously, if the chart is an embedded chart, you have access to the Top, Left, Height, and Width properties to set the size and location of the embedded chart. These properties are not available in chart sheets, because the chart is sized to take up the whole sheet; these properties are meaningless in that situation.

There is one property that does not reference an object. This is the AutoScaleFont property. Set this to false if you do not want the text to change size as the chart changes size. The default is true.

Three properties affect objects that format the entire chart area. These are the Border property (references the Border object), the Interior property (references an Interior object), and the Font property (references a Font object). These three objects are commonly used to format many chart objects (like PlotArea), and the Font and Border objects are even used in other areas in Microsoft Office.

The Border object

In Chapter 7, "Excel Basics," we looked at the Borders collection, which is used to format individual sides of each cell in a Range. Ranges work with a collection of Borders objects, one for each of the eight different borders. All other objects work with a single border, which affects all sides of the object equally. ChartArea, PlotArea, and others typically have rectangular borders, with all four sides formatted identically.

All Border objects have the same properties; these are listed in Table 3.

Table 3. Border properties. Borders can be placed around many objects, and their appearance can be altered with these properties.

To remove the border around the chart, use the following code:

#DEFINE xlNone -4142

oChart.ChartArea.Borders.LineStyle = xlNone

Remember that you can choose a fancy line style, or you can choose a continuous line style and set the weight. You cannot combine both.

The Interior object

The Interior object controls what the interior of the object looks like. To change the color, set the Color property of the Interior object to the desired RGB color. In this example, a pastel blue is chosen:

oExcel.ActiveChart.ChartArea.Interior.Color = RGB(192,192,255)

The Interior object also has a Pattern property. Yes, you can pattern the chart area, the plot area, the columns and bars, and so on, but please use this feature sparingly! Patterns can be visually overwhelming, especially if each column uses a different pattern and color. If the chart is in color, there should be plenty of colors to choose from. Okay, you could use a color and a pattern in the same color to indicate pairs of series for example, actual vs. forecast, where the actuals are solid colors and the forecast bars are a pattern in the same color. Where patterns really excel is when you need to print in black and white, and you just can t rely on how a printer maps the colors to shades of gray.

However, we really advise against patterning the plot area, and especially the chart area. The patterns are just too small for large areas, and they tend to look ugly.

With those caveats in mind, if you want to pattern an object, here s how. Set the Color property to the "background" color of the pattern. Set the PatternColor property to the color of the pattern. Some patterns, like Checker and Gray50, use equal amounts of Color and PatternColor, so it doesn t matter which one you select. However, CrissCross is made up of thin lines, so setting PatternColor to red and Color to white gives red lines on a white background. Reversing the colors gives the illusion of red diamonds (white lines on a red background). After you ve set the Color and PatternColor properties, you can set the Pattern property to the desired pattern. Table 4 lists the pattern constants.

The following code sample sets the PlotArea to a red grid on a white background:

#DEFINE xlPatternGrid 15

oExcel.ActiveChart.PlotArea.Interior.Color = RGB(255,255,255) && White

oExcel.ActiveChart.PlotArea.Interior.PatternColor = RGB(255,0,0) && Red

oExcel.ActiveChart.PlotArea.Interior.Pattern = xlPatternGrid

This probably won t win any visual awards, but it effectively illustrates patterning the Interior object.

Table 4. Excel Pattern constants. These can be used to pattern many objects. Be careful, though, and keep the patterning to a minimum to prevent a visual "ransom note" effect.

The Font object

If you ve been reading this book sequentially, by now you re already quite familiar with the Font object. It s discussed in the "Word Basics" chapter (Chapter 4), and again in the "Excel Basics" chapter (Chapter 7). We ll hit the highlights here and send you to the "Fonts" topic in Chapter 7 for details (Table 1 in Chapter 7 lists the commonly used Font properties in Excel).

When an object contains a Font object, the font properties are inherited by all child objects. For example, when you set the ChartArea s Font object properties, then all objects contained in the ChartArea inherit the font, unless you explicitly set the child object s font properties. Set the font properties just like you set them anywhere else:

oExcel.ActiveChart.ChartArea.Font.Name = "Times New Roman"

oExcel.ActiveChart.ChartArea.Font.Bold = .T.

oExcel.ActiveChart.Legend.Font.Name = "Arial"

oExcel.ActiveChart.Legend.Font.Bold = .F.

This example sets all text on the chart to bold Times New Roman, except text within the legend, which is set to unbolded Arial.

The PlotArea object

The PlotArea object is a child of the Chart object, and it represents the area bounded by the axes. You can resize this area, making it (and the axes that bound it) larger or smaller. The PlotArea is automatically optimized for that chart, and it usually doesn t need to be changed. But if you are trying to make the plot areas of two or more charts match in size, you need to play with the PlotArea size.

The PlotArea object has the standard Top, Left, Width, and Height properties. It also has properties for the InsideTop, InsideLeft, InsideWidth, and InsideHeight. The PlotArea object actually has two rectangular areas. The inside area is the area that defaults to gray, and it s bounded on the bottom by the category axis, on the sides by the value axes, and at the top by the maximum value. The whole plot area is the area bounded by a rectangle from the leftmost and topmost point of the primary value axis text to the rightmost and bottommost point of the secondary value axis and category axis text. Okay, a picture s worth a thousand words here. If you interactively select the PlotArea then try to move it, Excel indicates both areas with dashed lines when you move it, as shown in Figure 9.

Figure 9. Moving the PlotArea, which shows the boundaries of the outside and inside areas. The outside areas are controlled by the Top, Left, Height, and Width properties, while the inside areas are controlled by the InsideTop, InsideLeft, InsideHeight, and InsideWidth properties. The inside area properties are read-only.

Now, the only problem is that the Inside properties are read-only. To change any of the Inside values, you need to increase or decrease the Height or Width properties, until the InsideHeight and/or InsideWidth values are appropriate.

Fortunately, the default plot area size works for most charts, and you won t have to mess with it. What you want to change are the Interior and Border properties, which work just like their ChartArea counterparts. See the "The Interior object" and "The Border object" sections earlier in this chapter.

Data series

Data series objects store the references to the data to graph, as well as the numerous properties for each series. Each Series object is stored in the SeriesCollection collection object. Before we can get to the cool part (playing with the colors and other formatting features), we need to understand how the Series object stores and manages the data properties and objects.

How data and labels are stored

When the Series objects are set from the ChartWizard or from the SetSourceData method, each column or row (depending on the nPlotBy parameter) becomes a Series object. You can also add series (either to a new chart or to an existing chart, even if it was created with the ChartWizard) using SeriesCollection s Add method. The syntax is as follows:

oExcel.ActiveChart.SeriesCollection.Add( oSourceRange, [nPlotBy],

[lSeriesLabels], [lCategoryLabels], [lReplaceCategories] )

For example, starting with the data in Figure 4, if we add a column of data representing 1997 in Column G, we can update the chart with the following:

#DEFINE xlColumns 2

oExcel.ActiveChart.SeriesCollection.Add(oExcel.Sheets[1].Range("G1:G13"),;

xlColumns, .T., .F., .F.)

To use the Add method instead of the ChartWizard method to put data into a chart that was added with ChartsObjects.Add, use the following:

oExcel.ActiveChart.SeriesCollection.Add(oExcel.Sheets[1].Range("A1:F13"))

The data portion of each series is stored in the Values property, from which you can change the series range. From VFP, you need to set it to a Range object; from VB, you can set it to either a Range object or an array of points. When you read this Values property from VFP, you get the contents of the first cell, not a Range object or a character string representing a Range object. In all likelihood, this is due to the incompatibility of arrays between VFP and VB.

The SeriesCollection s XValues property stores the range used for the X-axis (category) values. These aren t stored in a normalized fashion, however. Each Series object in the collection stores an XValues value. But only the value of the first object in the collection (oChart.SeriesCollection[1]) appears to be used. Like the Values property, XValues is set to a Range (from VFP; VB also allows an array of points), but it returns only the first cell.

SeriesCollection s Name property stores the value of the series label. You set this to a range (generally representing a single cell) or a character string. This is a very handy command, because you can format your chart to readable names, while your data in the spreadsheet is abbreviated to fit in columns.

Formatting the Series

Now we re getting to the fun part. The intricacies of data storage pale in comparison to manipulating how the data looks (at least, in our opinion).

Two properties are already familiar: ChartType and BarShape. The Chart object s ChartType and BarShape properties set the formatting for the whole chart, and the Series object s properties change the format for just that particular series. The next example shows how to change the properties for each series. We ll set up some different series, so we can work on some of the other properties that affect certain types of charts.

#DEFINE xlArea 1

#DEFINE xlLineMarkers 65

oExcel.ActiveChart.SeriesCollection[1].ChartType = xlArea

oExcel.ActiveChart.SeriesCollection[4].ChartType = xlLineMarkers

oExcel.ActiveChart.SeriesCollection[5].ChartType = xlLineMarkers

Figure 10 shows the results of the code. Normally, you would not format this chart with different chart types, because each of the series represents the same kind of data. You might use different chart types if you were showing the profit, sales figures, and forecast goals. But we ll work on manipulating this data to illustrate the series concepts.

Figure 10. Each series can be a different chart type. It doesn t make sense to format this particular data set like this, but we can use the data to illustrate concepts.

There are two more objects that, by now, should be familiar. The Border and Interior objects format every series. The linear objects, like the line, use the Border properties to format the line itself; the Interior object is unavailable for lines. The other objects use the Border properties and the Interior properties to set the border (by default, it s black) and the color and patterns of the bar. See the sections "The Border object" and "The Interior object" earlier in this chapter for details.

Data labels

Data labels are text objects that label every point in the series. These properties and methods are available to the Chart, Series, and Point objects. Turn them on with the HasDataLabels property:

oExcel.ActiveChart.SeriesCollection[3].HasDataLabels = .T.

When HasDataLabels is .T., Excel labels each point with the value of the data point. Excel does a great job of centering the data label above the point, and setting the font size to something readable. However, it does not attempt to do anything about overlapping labels (if it did, then the labels wouldn t be centered at a calculated distance above the point). Turning on the labels gives us a very cluttered appearance, as shown in Figure 11. There are several ways to get around this problem, though none are foolproof in an automated environment, as it is difficult to query the data labels to determine whether they are overlapping.

Figure 11. Turning on data labels can give a very cluttered appearance. There are several workarounds: make the label font smaller, or turn them on for only selected points.

One way is to change the font size. Smaller fonts won t overlap as much. The problem here is that you can get just so tiny before the font size becomes unreadable. The DataLabels property allows you to address the entire collection of data labels, or you can select individual labels to format. Here s an example of changing font properties for the entire series of data labels:

WITH oExcel.ActiveChart.SeriesCollection[3].DataLabels.Font

.Name = "Times New Roman"

.Size = 6

ENDWITH

Selecting a single label from the collection is as easy as addressing it by its index. For example, this sets the third data label to a font size of six:

oExcel.ActiveChart.SeriesCollection[3].DataLabels[3].Font.Size = 6

There are more options to labeling than just the point value. You can use the ApplyDataLabels method of the SeriesCollection object. It has four parameters, but the most important is the first one. You can pass one of the values given in Table 5 to set the text of the label.

Table 5. Values for the ApplyDataLabels method. Note that some labels are available only for certain types of charts.

Just in case these options are not enough, you can set the text of individual labels by setting the DataLabel object s Text property to anything you desire:

oExcel.ActiveChart.SeriesCollection[3].DataLabels[1].Text = "Test"

The preceding code sets the label to "Test." Setting the text value to the empty string effectively removes the label.

Error bars

Error bars show potential error amounts for each value in a series. They are available for 2D area, bar, column, line, XY (scatter), and bubble charts. The Series object s ErrorBar method is used to set the values.

oExcel.ActiveChart.Series[n].ErrorBar( [nDirection], [nInclude],

[nType], [nErrorAmount], [nCustomNegAmount] )

The ErrorBars property (note that the method is singular, and the property is plural) references the ErrorBar object, with properties for additional formatting. The first is the Border property; yes, it references that same Border object to set the formatting of the error bar lines (see "The Border object" topic earlier in this chapter).

The second ErrorBar object property is the EndStyle property, which determines whether the error bars have a cap (perpendicular line at the end of the error bar) or not. It has two settings: xlCap (1) and xlNoCap (2).

Formatting data markers (line, radar, and scatter charts)

Line, radar, and scatter charts can use certain symbols to plot the points, known in Excel lingo as data markers. When you initially select a format with data markers, you get the default set of markers. The first series gets a six-pointed star (like an x with an additional vertical line), the second series gets an x, the third gets a triangle, the fourth gets a square, the fifth gets a diamond, and so on. Since the lines differ only by colors, it may look too busy for some people s taste. Personally, we think the first two symbols look a bit like barbed wire; we prefer the other symbols.

The Series object (and the Points object, too) uses the MarkerStyle property to set the shape of the marker. Shown in Table 6 is a list of all the marker constants, along with their values and shapes.

Table 6. The available marker styles. Markers are used on 2D line charts and scatter charts.

You can change the size of the marker with the MarkerSize property. We like the fact that this property sets the size in points, rather than relying on a series of constants!

You can change the marker color, too. You can separately set the foreground and background colors, allowing you to come up with quite a number of effects. The MarkerForegroundColor controls the border color, and the MarkerBackgroundColor controls the fill color. Set each to an RGB color value. Setting these values does not change the color of the lines connecting the markers (if any). For an example, let s change the marker of the 1995 data to an open dark blue diamond, and the lone marker of the 1996 data to a red square.

#DEFINE xlMarkerStyleDiamond 2

#DEFINE xlMarkerStyleSquare 1

#DEFINE ColorRed RGB(255, 0, 0)

#DEFINE ColorWhite RGB(255,255,255)

#DEFINE ColorDkBlue RGB( 0, 0,128)

* Format the 1995 data to an open dark blue diamond:

WITH oExcel.ActiveChart.SeriesCollection[4]

.MarkerStyle = xlMarkerStyleDiamond

.MarkerForegroundColor = ColorDkBlue

.MarkerBackgroundColor = ColorWhite

ENDWITH

* Format the 1996 data to red square on white background:

WITH oExcel.ActiveChart.SeriesCollection[5]

.MarkerStyle = xlMarkerStyleSquare

.MarkerForegroundColor = ColorRed

ENDWITH

Figure 12 shows the results of formatting the markers. Though black and white in the book, you can make out that the borders of the diamonds are darker than the line, and that the filled square is visible and is a different color than the line.

Figure 12. The results of changing the data markers. Note that markers can be colored separately from the line.

Smoothing lines on charts

By default, the lines on a chart are straight lines connecting each point. You can use the Series object s Smooth property to apply curve smoothing. Set this property to true to smooth the line, or false to keep the lines straight. This property applies only to line charts and scatter charts.

Formatting pie charts

Pie charts have features unique to themselves. In this example, we create a pie chart right next to the chart created for the previous examples, using the ChartWizard method.

#DEFINE xlPie 5

#DEFINE autoPieNoLabels 1

#DEFINE xlColumns 2

#DEFINE autoOneSeriesLabel 1

#DEFINE autoOneCategoryLabel 1

oRange = oExcel.Sheets[1].Range("CategoryNames, Year1994")

oChart = oExcel.Sheets[1].ChartObjects.Add(372.7, 173.2, 319.5, 190.5)

oChart.Activate()

oExcel.ActiveChart.ChartWizard(oRange, xlPie, autoPieNoLabels, ;

xlColumns, autoOneSeriesLabel, autoOneCategoryLabel)

The resulting graph is shown in Figure 13. It s a little plain-looking. The legend is bothersome too, as it s cut off part of the last entry. Fortunately, Excel gives us a range of formatting capabilities.

Figure 13. The default pie chart, as generated by the ChartWizard. Excel has a few formatting options to spruce this chart up.

We ve already covered adding data labels by using the Series object s ApplyDataLabels method (see the topic "Data labels" earlier in this chapter). Let s look at the values that are only available to pie charts (and doughnuts): xlDataLabelsShowPercent (3) and xlDataLabelsShowLabelAndPercent (5). Displaying both the label and the percent may cause the labels to overlap, as there are so many slices to the pie in this case, so displaying only the percentage makes a good choice.

#DEFINE xlDataLabelsShowPercent 3

oExcel.ActiveChart.SeriesCollection[1].ApplyDataLabels( ;

xlDataLabelsShowPercent)

Another possibility is the explosion effect, where one (or all) slices of the pie move out from the center of the pie. The Explosion property applies to the Series object (where all slices are exploded) and the Points object (where only that slice is exploded). The Explosion property value is a number representing the distance the slice moves from the center. Experiment a little to figure out the best value, as the value is a relative amount. One is just a tiny bit, something in the range of 5 20 looks good, and 50 separates the slices perhaps a bit too much (on this particular chart; your mileage may vary).

oExcel.ActiveChart.SeriesCollection[1].Explosion = 15

The results of formatting the labels and the explosion are shown in Figure 14.

Figure 14. The pie chart with some formatting added. Percentage labels are shown, and you could add the category label, too (though it would overlap). Exploding all of the slices gives another look to the pie chart.

Formatting individual points

In the previous sections, you ve seen reference to the ability to format individual points. Nearly all of the formatting features available to a Series object are available for a single point. Access the point through the Series object s Points method. You might use this feature to change the color and/or style of a marker, bar, column, or slice, to call attention to that data point. You can also add data labels to a point or two, again to draw attention to that point. You can manually set the values of the data labels, which works especially well if you want to add labels that are not part of your data in the spreadsheet.

On the example pie chart, perhaps the client wants to highlight December s slice with the category label, and to explode it (but not the rest). The following example first removes the explosion from the series, then sets up the formatting for December s slice:

#DEFINE xlDataLabelsShowLabelAndPercent 5

* Reset the Series explosion to 0

oExcel.ActiveChart.SeriesCollection[1].Explosion = 0

* Format the single point

WITH oExcel.ActiveChart.SeriesCollection[1].Points[12]

.ApplyDataLabels(xlDataLabelsShowLabelAndPercent)

.Explosion = 15

ENDWITH

Figure 15 shows the results of formatting a single point.

Figure 15. You can format a single point. Notice the December slice is exploded and has a different data label style.

The Legend object

The pie chart s legend has been bothering us since the first example. Access the Legend object through the Chart object s Legend property. The ChartObject also has a HasLegend property; if it s set to false, the Legend property (and therefore the Legend object) is unavailable. To quickly turn off the legend, simply set HasLegend to false:

oExcel.ActiveChart.HasLegend = .F.

This works, but it has the nasty habit of not recalculating the PlotArea to take advantage of the space previously consumed by the legend. You can also use the ChartWizard method to turn off the legend by restating the ChartFormat parameter (the second one) and the HasLegend parameter (the seventh), as in the following:

#DEFINE xlPie 5

oExcel.ActiveChart.ChartWizard(,xlPie,,,,,.F.)

This generally forces a recalculation of the plot area. However, now there s no legend, and the user is left to figure out what all of those slices mean. Perhaps we should put the legend back and figure out how to make it look better. Setting HasLegend to true recalculates the plot area, so we don t have to worry about remembering ChartWizard parameters.

oExcel.ActiveChart.HasLegend = .T.

You can change the font properties from the Legend s Font property, which accesses the Font object (explained in a previous topic in this chapter, "The Font object"). You can reduce the font a smidgen, and then you can make them fit.

oExcel.ActiveChart.Legend.Font.Size = ; oExcel.ActiveChart.Legend.Font.Size - 2

Figure 16 shows that changing the font size worked. You can also control the font name, bold and italic attributes, and so on. It s the same Font object used throughout the Excel model.

Figure 16. The effects of globally changing the font size of the legend. By reducing the size, we ve made it fit. The Legend object is as robust as the other chart objects and has many properties available to alter its format.

What else can you do to the Legend? You can set its Border and Interior properties (see the topics "The Border object" and "The Interior object" earlier in this chapter). You can manipulate the Top and Left properties to move it around (Height and Width are calculated for you and are therefore read-only). An easier way to move it around is to use the Position property, which takes one of the following values: xlLegendPositionBottom (-4107), xlLegendPositionCorner (2), xlLegendPositionLeft (-4131), xlLegendPositionRight (-4152), or xlLegendPositionTop (-4160). This also recalculates the Height and Width, allowing the legend to stretch along the bottom.

You can also format the font properties of each legend entry separately by accessing the LegendEntries property, which references the LegendEntries collection of LegendEntry objects. You cannot change the text (you do that through the Series object or the cell with the label), but you can access the font object, as in the following, which formats December s legend entry to bold:

oExcel.ActiveChart.Legend.LegendEntries[12].Font.Bold = .T.

Each LegendEntry object has a LegendKey object, which is that little sample that identifies the series. It has a Border object and an Interior object, as well as the other formatting properties used by the Series object. We recommend setting the formatting in the Series object; however, there is one exception. We ran into a problem formatting a Series object with an entirely blank data range. This can happen if you generate data for a standard chart and no data comes up for a series for example, if you graph monthly sales by customer for each category of products, and a customer orders no items in, say, the Beverage or Confections categories. Accessing the Series object gives an error, as the data range is empty. You can safely format the LegendKey object to customize the look of the legend key so the legend looks the same as other graphs.

Axes

The Chart object s Axes method returns an Axis object from the Axes collection. Each Axis object in the chart is contained in the Axes collection. The Axes method takes two parameters: the axis type and the axis group. The axis type is one of the following values: xlCategory (1), xlSeriesAxis (3), or xlValue (2). The axis group can be either xlPrimary (1) or xlSecondary (2). Secondary axes are available only on 2D charts, and only when a Series object s AxisGroup property is set to 2. The secondary axis allows two separate ranges of data to be graphed together. For example, the primary axis might be the value of the sales, with the secondary axis showing the percent of the total. Table 7 shows the properties available for each Axis object.

The TickLabels object

The TickLabels object controls the format of the labels on the axis. It gives you nearly complete control over the labels. Table 8 summarizes the important properties for the TickLabels object.

Table 7. Properties of an Axis object. These properties give a lot of flexibility for formatting axes.

Table 8. Properties for the TickLabels object. The TickLabels are the text that displays along the axis.

Gridlines

The Gridlines object is available only to primary axes. Its only relevant property is the Border property, accessing a Border object (see the topic "The Border object" earlier in this chapter). The Border object contains the formatting of the grid lines.

Titles

Charts have two kinds of title objects: ChartTitle objects and AxisTitle objects. Their structures are similar. The ChartTitle object is accessed through the Chart object s ChartTitle property, and it s used only if the Chart object s HasTitle property is true. The AxisTitle is accessed through the Axis object s AxisTitle property, and it s used only if the Axis object s HasTitle property is true. Table 9 shows their properties.

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved