Determine Variations In A Series Of Data

You can use up and down bars on a line chart to illustrate the variations in different data series shown on a line chart. By assigning up and down bars to a line chart, you can compare the first data series to the last data series to easily view where the values ascend or descend. You place up and down bars on your chart using the HasUpDownBars property.

Part of the DownBars object, down bars connect the points on the first data series with the lower values in the final series. Conversely, up bars, part of the UpBars object, connect the points on the first data series with the higher values in the final series.

You can customize the look of the UpBars and DownBars objects with the Border and Interior object properties. The Interior object determines how the interior of each bar appears, for example, whether the bar has a solid color or a pattern. The Border object controls the border drawn around each bar. You customize each of these objects with the corresponding properties. If you add up and down bars to your chart, you want to customize the color of each bar type so that you can easily distinguish the difference between the up bars and the down bars. You customize the color of the bar types using the Color property of the Interior object.

When you use the Color property, you specify the color you want to apply with the RGB function. This function accepts three values in the range of 0 to 2 55, indicating the amount of red, green, and blue in the color. The color values range from black at 0, 0, 0 and white at 255, 255, 255.

DETERMINE VARIATIONS IN A SERIES OF DATA

DETERMINE VARIATIONS IN A SERIES OF DATA

—D Create a new subroutine.

-0 Type Set NewChart = ThisWorkbook.Charts.Add().

Type

NewChart.SetSourceData Source:=Worksheets("Sheet1") .Range("A1:C5"), replacing Worksheets("Sheet1"). Range("A1:C5") with the data range.

-"□ Type With NewChart.LineGroups(1).

—D Create a new subroutine.

Type Dim NewChart As Chart, replacing NewChart with the chart variable.

-0 Type Set NewChart = ThisWorkbook.Charts.Add().

—Q Type NewChart.ChartType = xlLine.

Type

NewChart.SetSourceData Source:=Worksheets("Sheet1") .Range("A1:C5"), replacing Worksheets("Sheet1"). Range("A1:C5") with the data range.

-"□ Type With NewChart.LineGroups(1).

_Q Type .HasUpDownBars

Exiri

WORKING WITH CHARTS

You can use Border and Interior objects with several of the Chart object child objects to customize the border and interior settings. Each object has multiple properties to customize it as shown in the following table:

PROPERTY

OBJECT

DESCRIPTION

Color

Border Interior

Uses the RGB function to select the color by specifying a value between 0 and 255 for each color element: red, green, and blue.

ColorIndex

Border Interior

An index value between 1 and 56 indicating the desired palette color.

LineStyle

Border

An xlLineStyle constant from Appendix A specifying the style of the border line.

Parent

Border

Indicates the parent object.

Weight

Border

An xlBorderWeight constant from Appendix A specifying the line weight of the border.

InvertIfNegative

Interior

A value of True inverts the interior pattern for negative values.

Pattern

Interior

An xlPattern constant from Appendix A indicating the pattern for the interior.

PatternColor

Interior

Uses the RGB function to select the pattern color by specifying a value between 0 and 255 for each color element: red, green, and blue.

PatternColorIndex

Interior

An index value between 1 and 56 indicating the desired palette color for the pattern.

_, Type additional properties for the up and down bars.

Switch to Excel and run the macro.

-■ Excel creates the line chart using up and down bars to indicate differences in point values.

_O Type UpBars.Interior.Color = RGB(255, 255, 0), replacing (255, 255, 0) with the color reference.

—0 Type DownBars.Interior .Color =RGB(0, 0, 255), replacing(0, 0, 255) with the color reference.

_, Type additional properties for the up and down bars.

Type End With.

Switch to Excel and run the macro.

-■ Excel creates the line chart using up and down bars to indicate differences in point values.

0 0

Post a comment