FEATURED LINKS
PIE CHART OBJECT AND VBA
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 gone through few simple Property changes on 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.
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 in understanding them. The difference is only in the formatting method of individual PIE Slices and 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.
PieChart "MyReport1", "Chart1"
Sample run image of Option-1 is given below:
Sample run image of Option-2:
Sample run image of Option-3:
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 formats 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 other form 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 take the share of the Cost $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 share of Cake.
In the earlier examples the Formatting of all the Bars of a particular Data Series are applied together as a single collection of Objects with higher level reference grphChart.SeriesCollection(). But in Pie Chart, individual element of a Data Series (Pie Slices) is referenced by getting one step further deep into the collection of Objects as grphChart.SeriesCollection().Points() and formats them separately with different set 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
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. On these elements also we can use the Rnd() Function to generate different set of values in each Run, for Gradient Color formatting. 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 Color Values you may try out with different Color Values from the Color Chart given on the first Post Working with Chart Object in VBA.
Create ListBox from Another ListBox
Selected List Box Items and Dynamic Query
Database Daily Backup
Days in Month Function
Finding Consecutive Workdays with Query
You may have a look at the Column Chart and VBA topic at your convenience as well.
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 in understanding them. The difference is only in the formatting method of individual PIE Slices and 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.
- 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 - 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 image of Option-1 is given below:
Sample run image of Option-2:
Sample run image of Option-3:
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 formats 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 other form 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 take the share of the Cost $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 share of Cake.
In the earlier examples the Formatting of all the Bars of a particular Data Series are applied together as a single collection of Objects with higher level reference grphChart.SeriesCollection(). But in Pie Chart, individual element of a Data Series (Pie Slices) is referenced by getting one step further deep into the collection of Objects as grphChart.SeriesCollection().Points() and formats them separately with different set 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
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. On these elements also we can use the Rnd() Function to generate different set of values in each Run, for Gradient Color formatting. 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 Color Values you may try out with different Color Values from the Color Chart given on the first Post Working with Chart Object in VBA.
Create ListBox from Another ListBox
Selected List Box Items and Dynamic Query
Database Daily Backup
Days in Month Function
Finding Consecutive Workdays with Query
Labels: msaccess graphs
posted by a.p.r. pillai at 10:42 PM
0 comments
links to this post
