Figure 18-6: A chart, before and after being formatted.

Note Keep in mind that, after executing this macro, the actual appearance of the chart depends on the document theme that's in effect.

CD- a workbook with this example is available on the companion CD-ROM as a file named %

ROM format a chart.xlsm.

In the FormatChart procedure:

■ The ChartType property is straightforward enough, and VBA provides constants for the various chart types.

■ The ApplyLayout method uses a number to represent the layout, and the numbers vary with the chart type. These numbers appear as ToolTips when you hover the mouse over an icon in the Chart

Tools Design Chart Layouts gallery. The ApplyLayout method can also specify a chart type as its second argument. Therefore, I could have eliminated the statement that changes the ChartType property and used this statement:

.ApplyLayout 10, xlColumnClustered

■ The ChartStyle property also uses a nondescriptive number (from 1 to 48) for its argument. These numbers appear as ToolTips when you hover the mouse over an icon in the Chart Tools Design

Chart Styles gallery.

■ The SetElement method controls the visibility of just about every aspect of the chart. It accepts more than 120 descriptive constants. For example, the constant msoElementChartTitleNone hides the chart's title.

■ The ClearToMatchStyle method clears all user-applied formatting in the chart. This method is usually used in conjunction with the ChartStyle property to ensure that the applied style does not contain any formatting that's not part of the style.

More chart formatting examples

As I noted earlier, the macro recorder in Excel 2007 ignores many formatting commands when working with a chart. This deficiency is especially irksome if you're trying to figure out how to apply some of the new formatting options such as shadows, beveling, and gradient fills.

In this section, I provide some examples of chart formatting. I certainly don't cover all of the options, but it should be sufficient to help you get started so you can explore these features on your own. These examples assume an object variable named MyChart, created as follows:

Dim MyChart As Chart

Set MyChart = ActiveSheet.ChartObjects(1).Chart

If you apply these examples to your own charts, you need to make the necessary modifications so MyChart points to the correct Chart object.

Tip To delete all user-applied (or VBA-applied) formatting from a chart, use the ClearToMatchStyle method of the Chart object. For example:



One of the most interesting formatting effects in Excel 2007 is shadows. A shadow can give a chart a three-dimensional look and make it appear as if it's floating above your worksheet.

The following statement adds a default shadow to the chart area of the chart:

MyChart.ChartArea.Format.Shadow.Visible = msoTrue

In this statement, the Format property returns a ChartFormat object, and the Shadow property returns a ShadowFormat object. Therefore, this statement sets the Visible property of the ShadowFormat object, which is contained in the ChartFormat object, which is contained in the ChartArea object, which is contained in the Chart object.

Not surprisingly, the ShadowFormat object has some properties that determine the appearance of the shadow. Here's an example of setting five properties of the ShadowFormat object, contained in a ChartArea object, and Figure 18-7 shows the effect:


0 0

Post a comment