In Excel 2010 VBA, we can write code to create charts and graphs easily based on the data on the spreadsheet. Excel 2010 VBA has made charting engine as part of the Shape object. It is also an object by itself. We can create charts on a sheet of their own or embed them into an existing worksheet. The chart sheet is the Chart object whereas the embedded chart is part of the shape collection for the worksheet.
Figure 24.1: Pie Chart
To separate the pie chart into slices (The technical term is explode), you can add this line to the code:
ActiveChart.SeriesCollection(1).Explosion = 10
As there are five sectors, setting the explosion value to 10 explode all slices. You will get the following chart:
23.2 Creating a Bar Graph
To draw a bar graph, you just need to change the ChartType to xl3DColumn.
Excel VBA 2010 has built-in parameters to define the types of charts that can be drawn on a spreadsheet. You can refer to the parameters as the ChartType properties. The list of common properties for ChartType are listed below:
|xlXYScatter||XY Scatter Chart|
|xl3DArea||3D Area Chart|
|xl3DBar||3D Bar Chart|
|xl3DColumn||3D Column Chart|
|Xl3DLine||3D Line Chart|
For example, if you change the ChartType property to xlLine using the code
ActiveChart.ChartType = xlLine
you will obtain a line graph as follows: