Microsoft Office Tutorials and References
In Depth Information
Basic Formatting and Customizing Examples
Basic Formatting and
A common use for a macro is to apply formatting or various customizations to a
chart. This involves changing properties of the objects in the chart. The easiest way
to determine which properties are appropriate is to record a macro while you make
the changes to the chart. The macro may be usable as is. At the very least, the
recorded macro will identify the relevant properties.
Following is a macro that sets the series colors for three series in the active chart.
If ActiveChart Is Nothing Then Exit Sub
.SeriesCollection(1).Interior.ColorIndex = 3
.SeriesCollection(2).Interior.ColorIndex = 5
.SeriesCollection(3).Interior.ColorIndex = 4
The ChangeSeriesColors macro sets the ColorIndex property of the Interior
object, which is contained in each of the three Series objects. These ColorIndex
values correspond to red, blue, and green in the standard color palette. Note that
this macro performs no error checking. In other words, it will generate an error if
the chart doesn’t have the referenced Series objects, or if the Series object does
not have an Interior object — which is the case for a line chart series. To avoid
these error messages, the simplest approach is to ignore them by adding the
following statement at the top of the procedure:
On Error Resume Next
The ChangeSeriesColors macro originated as a recorded macro, which follows.
The modified version is much simpler and is more efficient because it doesn’t select
the objects before setting the property values. Notice that the modified macro does
not set the Pattern property. This property is rarely used and it will almost
certainly be set to its default value ( xlSolid ).