Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

PIE CHART OBJECT AND VBA

Introduction.

This is the continuation of a discussion on working with Chart Object in VBA. We have created a small Table and a Report with a Chart Object on it, in the earlier Post Working with Chart Object in VBA and going through a few simple Property changes in the Chart Object to get prepared for the Demo of the VBA Programs. Please go through Steps 1 to 7 explained in the earliest Post, by following the link above, and continue.

You may have a look at the Column Chart and VBA topic at your convenience as well.

PIE Chart Options.

If you have already tried out the Code given in the earlier Posts then you are familiar with most of the Code Segments that you see here and will have no difficulty understanding them. The difference is only in the formatting method of individual PIE Slices and a few property changes also. In this example, we will try three types of Pie Charts: 1. 3D Pie, 2. 3D Pie Exploded, 3. Pie of Pie.

  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:


Sample run: an image of Option-2:


Sample run: an image of Option-3:


Run it from the Command Button Click.

If you prefer to run this command from the On_Click() Event Procedure of a Command Button on your Form, you may do so. The Program will open myReport1 in Design View, resize the Chart1 Chart Object, big enough for Printing or Viewing, and format the Chart elements. The Program saves the Report and re-opens it in Print Preview.

The Analysis of Values appearing in a PIE Chart is done differently than in other forms of Charts. That is why PIE Charts take only one set of Values.

If, four of us join together and decided to buy a Cake worth $100 and each one of us takes a share of the Cost of $6 (I will take the first share amount if no icing), $60, $20 and $14, the claim on the Cake is in relation to the share of Amount. The Pie Slices will show the size of each one of our shares.

In the earlier examples, the Formatting of all the Bars of a particular Data Series is applied together as a single collection of Objects with higher-level reference grphChart.SeriesCollection(). But in Pie Chart, individual elements of a Data Series (Pie Slices) are referenced by getting one step further deep into the collection of Objects as grphChart.SeriesCollection().Points() and formats them separately with different sets of Gradient Colors.

Tips: If you would like to format individual Bars of a Bar Chart you can use this method. But you must remove the following Properties from the Code, used for PIE Chart:

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

Chart Formatting.

The X-Axis and Y-Axis Titles are not used in Pie Charts and these Code segments are removed from the Program. Chart Area and Plot Area are formatted with a different set of light Colors, different from our earlier examples. With these elements also we can use the Rnd() Function to generate different sets of values in each Run, formatting with Gradient Color. But, it may not come up with the correct color combination every time, good enough for viewing or Printing. If you do not like the current Scheme of  Color Values you may try out different Color Values from the Color Chart given on 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
Share:

4 comments:

  1. Thanks for all this information.

    I created the table and used "number" for each of the fields except the first which is a "text" and is the primary key. When I ran the first part of the program using then command line I got a msg box that was "False" and asking for an entry. Could not get past that.

    So I moved on to the second part, this area, and tried the pie chart. However, I am using 2003 and need to know what the error "Type Mismatch" is when the line:

    With grphChart
    .chartType = lngType
    .....


    runs? Can anyone help.

    ReplyDelete
  2. Download a sample database with all the running programs of charts from the following link:

    http://www.msaccesstips.com/downloads/2008/06/workingwith_chartobject.php

    ReplyDelete
  3. I downloaded the database above and it was helpful. I was a little disappointed that the example here (unlike the bar/column one)was so fancy with no queries, I would have preferred a dumbed down example like the bar/column one where I could follow the code (I am not a programmer). Anyway, I do not seem to be able to get my pie to work when I try to create one from scratch using the chart wizard. I only get a purple pie with a line to the center of it. Then I copied your report and pointed to my query and pasted and also pasted that into the a demo pie and it looked pretty good in that my pie divided into six with labels and values and different colors for each slice.

    The problem is that I need to use percentages. My values/percentages do not match the demo and report view of the data so though it looks ok it is completely wrong.

    My "crosstab" query for each column is:

    79.74% 14.16% 4.47% 1.46% 0.17% 0.00%

    My demo pie and report shows:

    70 22 7 1

    What am I doing wrong?

    Thanks

    Leah

    ReplyDelete
  4. I finally figured out what I was doing wrong. I seem to need an "extra" column with "column name" for my first column. My data was in a crosstab like form (one record with 6 different columns), but it was was not really a crosstab. Access apparently thought that my first column was the column name so it ignored it in the calculation and then all my percentages were off. The strange thing is that I pasted everything starting with the second column but when I closed and came back to it, everything moved.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code