PIE CHART OBJECT AND VBA

Introduction.

This post continues our discussion on working with the Chart Object in VBA. In the earlier article, Working with Chart Object in VBA, we created a small table and a report containing a Chart Object, and we explored a few basic property modifications to prepare for the upcoming VBA demonstrations.

Before proceeding, please review Steps 1 to 7 outlined in the earlier post by following the provided link, and then continue from here.

You will find it helpful to read the related topics, Column Chart and VBA, for additional context and practical examples.

PIE Chart Options.

If you have already tried the code examples from the earlier posts, you should find most of the segments here familiar and easy to follow. The main differences in this section involve the formatting of individual pie slices and a few property adjustments.

In this example, we will experiment with three types of Pie Charts:

1. 3D Pie

2. 3D Exploded Pie

3. Pie of Pie

Each variation demonstrates a different way of emphasizing data segments and controlling the visual layout of your chart through VBA.

1. Copy and paste the code given below into a Global Module in your Database.

   Public Function PieChart(ByVal ReportName As String, ByVal ChartObjectName As String)
   '---------------------------------------------------
   'Author : a.p.r. pillai
   'URL    : http://www.msaccesstips.com
   'Date   : July-2008
   'Customized Source Code : from Microsoft Access Help
   '---------------------------------------------------
   Dim Rpt As Report, grphChart As Object
   Dim msg As String, lngType As Long, cr As String
   Dim ctype As String, typ As Integer, j As Integer
   Dim db As Database, rst As Recordset, recSource As String
   Dim colmCount As Integer, chartType(1 To 5) As String
   Const twips As Long = 1440
   
   On Error GoTo PieChartChart_Err
   
   chartType(1) = "3D Pie Chart"
   chartType(2) = "3D Pie Exploded"
   chartType(3) = "Pie of Pie"
   chartType(4) = "Quit"
   chartType(5) = "Select 1-4, 5 to Cancel"
   
   cr = vbCr & vbCr
   msg = ""
   For j = 1 To 5
     msg = msg & j & ". " & chartType(j) & cr
   Next
   
   ctype = "": typ = 0
   Do While typ < 1 Or typ > 4
    ctype = InputBox(msg, "Select Chart Type")
    If Len(ctype) = 0 Then
       typ = 0
    Else
       typ = Val(ctype)
    End If
   Loop
   
   Select Case typ
       Case 4
           Exit Function
       Case 1
          lngType = xl3DPie
       Case 2
          lngType = xl3DPieExploded
       Case 3
          lngType = xlPieOfPie
   End Select
   
   DoCmd.OpenReport ReportName, acViewDesign
   Set Rpt = Reports(ReportName)
   
   Set grphChart = Rpt(ChartObjectName)
   
   grphChart.RowSourceType = "Table/Query"
   recSource = grphChart.RowSource
   
   If Len(recSource) = 0 Then
      MsgBox "RowSource value not set, aborted."
      Exit Function
   End If
   
   'get number of columns in chart table/Query
   'if Table or SQL string is not valid then
   'generate error and exit program
   Set db = CurrentDb
   Set rst = db.OpenRecordset(recSource)
   colmCount = rst.Fields.Count
   rst.Close
   
   're-size the Chart
   With grphChart
       .ColumnCount = colmCount
       .SizeMode = 3
       .Left = 0.2917 * twips
       .Top = 0.2708 * twips
       .Width = 5 * twips
       .Height = 4 * twips
   End With
   
   'activate the chart for modification.
   grphChart.Activate
   
   'Chart type, Title, Legend, Datalabels,Data Table
   With grphChart
        .chartType = lngType
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Font.Name = "Verdana"
       .ChartTitle.Font.Size = 14
       .ChartTitle.Text = chartType(typ) & " Chart."
       .HasDataTable = False
   End With
   
   'format Pie slices with gradient color
   With grphChart.SeriesCollection(1)
       .HasDataLabels = True
       .DataLabels.Position = xlLabelPositionBestFit
       .HasLeaderLines = True
       .Border.ColorIndex = 19 'edges of pie shows in white color
       For j = 1 To .Points.Count
           With .Points(j)
               .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
               .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
               .DataLabel.Font.Name = "Arial"
               .DataLabel.Font.Size = 10
               .DataLabel.ShowLegendKey = False
               '.ApplyDataLabels xlDataLabelsShowValue
               .ApplyDataLabels xlDataLabelsShowLabelAndPercent
           End With
       Next
   End With
   
   'Chart Area Border
   With grphChart
       .ChartArea.Border.LineStyle = xlDash
       .PlotArea.Border.LineStyle = xlDot
       .Legend.Font.Size = 10
   End With
   
   'Chart Area Fill with Gradient Color
   With grphChart.ChartArea.Fill
       .Visible = True
       .ForeColor.SchemeColor = 17
       .BackColor.SchemeColor = 2
       .TwoColorGradient msoGradientHorizontal, 2
   End With
   
   'Plot Area fill with Gradient Color
   With grphChart.PlotArea.Fill
       .Visible = True
       .ForeColor.SchemeColor = 6
       .BackColor.SchemeColor = 19
       .TwoColorGradient msoGradientHorizontal, 1
   End With
   
   grphChart.Deselect
   
   DoCmd.Close acReport, ReportName, acSaveYes
   DoCmd.OpenReport ReportName, acViewPreview
   
   PieChart_Exit:
   Exit Function
   
   PieChart_Err:
   MsgBox Err.Description, , "PieChart()"
   Resume PieChart_Exit
   End Function
   

2. Press Ctrl+G to display the Debug Window (Immediate Window) in the VBA Editing View, type the following command in the Debug Window, and press Enter:

PieChart "MyReport1", "Chart1"

Sample run: an image of Option-1:

Right-click to open Large Image in New Tab/Window.

Sample run: an image of Option-2:

Right-click to open Large Image in New Tab/Window.

Sample run: an image of Option-3:

Right-click to open Large Image in New Tab/Window.

Run it from the Command Button Click.

You can run this program from the On_Click() Event Procedure of a Command Button on your Form. The program will:

1. Open myReport1 in Design View.

2. Resize the Chart1 Chart Object to a size suitable for printing or viewing.

3. Format the chart elements.

4. Save the report and reopen it in Print Preview.

Key points about PIE Charts:

• The analysis of values in a Pie Chart differs from other chart types, which is why Pie Charts accept only one set of values.

• For example, if four people decide to buy a cake worth $100, and their shares are $60, $20, $14, and $6, the Pie Slices will reflect the proportion of each share relative to the total cost.

• Unlike Bar or Column Charts, where formatting can be applied to an entire data series using grphChart.SeriesCollection() - Pie Charts require you to access individual slices through grphChart.SeriesCollection().Points() - This allows you to apply different gradient colors or formatting to each slice separately.

Tip: You can use the same Points() method to format individual bars of a Bar Chart, but you must remove the Pie-specific properties from the code to avoid errors.

.DataLabels.Position = xlLabelPositionBestFit
.HasLeaderLines = True
.Border.ColorIndex = 19 'showing edges of Pie slices with white color
.ApplyDataLabels xlDataLabelsShowLabelAndPercent

Chart Formatting.

In Pie Charts, the X-Axis and Y-Axis titles are not used, so the corresponding code segments have been removed from the program. The Chart Area and Plot Area are formatted using a different set of light colors, distinct from the previous examples.

You can also use the Rnd() function to generate different color sets for each run, applying gradient formatting. However, the resulting color combination may not always be ideal for viewing or printing. If you are not satisfied with the current color scheme, you can experiment with different color values from the Color Chart provided in the first post, Working with Chart Object in VBA.

Download the Demo Database.

Download Demo Database

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line-Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart