Chart Type property

The ChartType property is a read-write property that can be set to any one of the XlChartType constants in Table 21-1.

Table 21-1. ChartType Constants

Type

Description

Constant(Value)

Area

3D Area

xl3DArea(-4098)

3D Stacked Area

xl3DAreaStacked(78)

3D 100% Stacked Area

xl3DAreaStacked100(79)

Area

xlArea(1)

Stacked Area

xlAreaStacked(76)

100% Stacked Area

xlAreaStacked100(77)

Bar

3D Clustered Bar

xl3DBarClustered(60)

3D Stacked Bar

xl3DBarStacked(61)

3D 100% Stacked Bar

xl3DBarStacked100(62)

Clustered Bar

xlBarClustered(57)

Stacked Bar

xlBarStacked(58)

100% Stacked Bar

xlBarStacked100(59)

Bubble

Bubble

xlBubble(15)

Bubble with 3D effects

xlBubble3DEffect(87)

Column

3D Column

xl3DColumn(-4100)

3D Clustered Column

xl3DColumnClustered(5 4)

3D Stacked Column

xl3DColumnStacked(55)

3D 100% Stacked Column

xl3DColumnStacked100(56)

Clustered Column

xlColumnClustered(51)

Stacked Column

xlColumnStacked(52)

100% Stacked Column

xlColumnStacked100(53)

Cone

Clustered Cone Bar

xlConeBarClustered(102)

Stacked Cone Bar

xlConeBarStacked(103)

100% Stacked Cone Bar

xlConeBarStacked100(104)

3D Cone Column

xlConeCol(105)

Clustered Cone Column

xlConeColClustered(99)

Stacked Cone Column

xlConeColStacked( 100)

100% Stacked Cone Column

xlConeColStacked 100(101)

Cylinder

Clustered Cylinder Bar

xlCylinderBarClustered(95)

Stacked Cylinder Bar

xlCylinderBarStacked(96)

100% Stacked Cylinder Bar

xlCylinderBarStacked 100(97)

3D Cylinder Column

xlCylinderCol(98)

Clustered Cylinder Column

xlCylinderColClustered(92)

Stacked Cylinder Column

xlCylinderColStacked(93)

100% Stacked Cylinder Column

xlCylinderColStacked100(94)

Doughnut

Doughnut

xlDoughnut(-4120)

Exploded Doughnut

xlDoughnutExploded(80)

Line

3D Line

xl3DLine(-4101)

Line

xlLine(4)

Line with Markers

xlLineMarkers(65)

Stacked Line with Markers

xlLineMarkersStacked(66)

100% Stacked Line with Markers

xlLineMarkersStacked100(67)

Stacked Line

xlLineStacked(63)

100% Stacked Line

xlLineStacked100(64)

Pie

3D Pie

xl3DPie(-4102)

Exploded 3D Pie

xl3DPieExploded(70)

Bar of Pie

xlBarOfPie(71)

Pie

xlPie(5)

Exploded Pie

xlPieExploded(69)

Pie of Pie

xlPieOfPie(68)

Pyramid

Clustered Pyramid Bar

xlPyramidBarClustered(109)

Stacked Pyramid Bar

xlPyramidBarStacked(110)

100% Stacked Pyramid Bar

xlPyramidBarStacked100(111)

3D Pyramid Column

xlPyramidCol(112)

Clustered Pyramid Column

xlPyramidColClustered(106)

Stacked Pyramid Column

xlPyramidColStacked(107)

100% Stacked Pyramid Column

xlPyramidColStacked100(108)

Radar

Radar

xlRadar(-4151)

Filled Radar

xlRadarFilled(82)

Radar with Data Markers

xlRadarMarkers(81)

Stock Quotes

High-Low-Close

xlStockHLC(88)

Open-High-Low-Close

xlStockOHLC(89)

Volume-High-Low-Close

xlStockVHLC(90)

Volume-Open-High-Low-Close

xlStockVOHLC(91 )

Surface

3D Surface

xlSurface(83)

Surface (Top View)

xlSurfaceTopView(85)

Surface (Top View) wireframe)

xlSurfaceTopViewWireframe(86)

3D Surface (wireframe)

xlSurfaceWireframe(84)

XY (Scatter)

Scatter

xlXYScatter(-4169)

Scatter with Lines

xlXYScatterLines(74)

Scatter with Lines and No Data Markers

xlXYScatterLinesNoMarkers(75)

Scatter with Smoothed Lines

xlXYScatterSmooth(72)

Scatter with Smoothed Lines and No Data Markers

xlXYScatterSmoothNoMarkers(73)

In Example 21-3, at the end of the chapter, we present a macro that scrolls through the chart types in Table 21-1, allowing you to determine which chart type is appropriate for a particular purpose.

Note that it is possible that the return value of the ChartType property may not be one of the values in Table 21-1. For instance, the code:

MsgBox ActiveChart.ChartType returns -4111 when applied to a chart with a particular user-defined chart type. This value actually occurs only once in the Excel object model. It is part of the Constants enum and is assigned the symbolic name xlCombination. (Since this seems not to be documented, I cannot say that this is the only value of ChartType that is not in Table 21-1.)

Note that each individual data series can have a chart type; that is, the Series object also has a ChartType property. In this way, if a chart has two data series, each series can have a different chart type. For instance, one series can be plotted as a line graph and the other as a column graph. (We will discuss Series objects later in the chapter.)

21.3.1.1 ApplyCustomType method

Contrary to its name, the ApplyCustomType method can apply either a standard or a custom chart type to a chart. The syntax is:

ChartObject .ApplyCustomType( ChartType, TypeName)

The ChartType parameter is either a standard chart type constant from Table 21-1 or one of the following XlChartGallery constants:

Enum XlChartGallery xlBuiltIn = 21 xlUserDefined = 22

xlAnyGallery = 23 End Enum

(The term ChartGallery does not seem to appear in the Excel documentation. However, in the Word object model, the term ListGallery refers to the objects that represent the three tabs in the Bullets and Numbering dialog box. Extrapolating to Excel, we might surmise that the term ChartGallery refers to the two tabs in the Chart Type dialog box in Figure 21-8.)

If ChartType is an XlChartGallery constant, then the optional TypeName parameter specifies the name of the custom chart type.

For instance, the following code:

ActiveChart.ApplyCustomType

ChartType:=xlBuiltIn, TypeName:="Blue Pie"

applies the built-in chart type called Blue Pie. This is equivalent to selecting Blue Pie in the Excel dialog box shown in Figure 21-8.

Figure 21-8. Illustrating ApplyCustomType

Figure 21-8. Illustrating ApplyCustomType

Macros Excel Definition

As another example, the following code sets the chart type to a user-defined type named DefaultXY:

ActiveChart.ApplyCustomType

ChartType:=xlUserDefined, TypeName:="DefaultXY"

+2 0

Post a comment