Access VBA Macro Programming

In terms of the Chart Type, Excel version 97 and later have built-in parameters to define

the various types of charts. These all correspond to the gallery in the Chart Wizard that you

see if you insert a chart into a report or form.

Constant Description Value

xlArea Area Chart 1

xlBar Bar Chart 2

xlColumn Column Chart 3

xlLine Line Chart 4

xlPie Pie Chart 5

xlRadar Radar Chart –4151

xlXYScatter XY Scatter Chart –4169

xlCombination Combination Chart –4111

xl3DArea 3-D Area Chart –4098

xl3DBar 3-D Bar Chart –4099

xl3DColumn 3-D Column Chart –4100

xl3DLine 3-D Line Chart –4101

xl3DPie 3-D Pie Chart –4102

xl3DSurface 3-D Surface Chart –4103

xlDoughnut Doughnut Chart –4120

Working with the Series Collection Object

The Series Collection object represents all the data within your chart. It is very useful for

manipulating charts in appearance. The following code assumes you are still working with

thechobject from the previous code listing.

You can use VBA to turn on or turn off the data labels on the pie chart by setting the

HasDataLabelsproperty to True or False:

ch.SeriesCollection( 1 ).HasDataLabels = True

You can also explode slices of the pie chart:

ch.SeriesCollection( 1 ).Explosion = 8

TheExplosionproperty is a long integer and works on the basis that each bit represents

one slice in the pie chart. As there are four slices, setting this to 8 explodes all slices. If you

set it to 4, this would explode the third slice only of the chart. Setting the property to 0

indicates that no slices are exploded.

You can use VBA to manipulate the appearance of labels on your chart.

ch.SeriesCollection( 1 ).DataLabels.ShowValue = True

ch.SeriesCollection( 1 ).DataLabels.ShowCategoryName = True

236 Microsoft Access 2010 VBA Macro Programming