Column Chart and VBA

Introduction.

In our earlier example, working with Access Graph Chart in VBA, we prepared MS Access Charts with VBA and ran sample tests for three types of charts: Clustered Column, Line, and Pie. If this is your first time here, refer to the earlier post by clicking the link above. Follow Steps 2 to 7 to complete the preparations before trying the code presented here.

Previously, we were able to change the chart type in code to any of the three types while applying common property settings. However, some properties, such as Chart Axis Titles, were skipped conditionally for the Pie Chart. We will handle Pie Charts separately later, including formatting individual pie slices and modifying other settings.

Here, we will focus only on Column Charts and run the code for four different designs. Column Charts, with vertical bars, are the most commonly used type and are often loosely referred to as bar charts. The category labels for the plotted values appear horizontally along the X-axis below the chart, while the chart scale is calculated automatically and displayed vertically along the left side (Primary Y-Axis). This scale can also be set manually if needed.

When small values are plotted alongside large values, the differences in the small values may not be clearly visible. In such cases, you can plot the small value series on the Secondary Y-Axis (right-side vertical axis) to make the variations more noticeable. Adding data labels further enhances the readability and analysis of the chart.

For 3D Column Charts, the Y-axis is replaced by a Z-axis to display tick labels and axis titles. Note that horizontal bar charts are the true representation of bar charts.

Sample Demo Run Code

We have created options in the Code to run for the following Options:

1. Column Clustered.
2. Column Clustered (Reverse Plot Order - flipped upside down)
3. 3D Column Clustered.
4. 3D Column Stacked.

There are optional properties in this Code that you can use to change the shape of the Bars to a Cone or a Cylinder.

In this example, we enable the HasDataTable property of the Chart Object, which displays the source data table directly on the chart. The X-Axis category labels, Qrtr1 to Qrtr4, become part of this data table display. Note that if you try to modify the X-Axis Tick-Label properties, such as Font or Font Size, while the data table is visible, you may encounter errors. We have included this code conditionally rather than removing it completely, so you can observe the difference.

The DataLabels Orientation property allows you to tilt the data labels to a specified angle in degrees, in addition to the standard horizontal or vertical display.

To try out these examples, follow the steps below:

The VBA Code

1. Copy and paste the following code into a Global Module of your Database and save it. If you have already gone through the earlier Post and gone through steps 2 to 7, then you are ready to run the Code.

   Public Function ColumnChart(ByVal ReportName As String, ByVal ChartObjectName As String)
   '---------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : June-2008
   'URL    : http://www.msaccesstips.com
   '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 6) As String
   Const twips As Long = 1440
   
   On Error GoTo ColumnChart_Err
   
   chartType(1) = "Clustered Column"
   chartType(2) = "Reverse Plot Order"
   chartType(3) = "3D Clustered Column"
   chartType(4) = "3D Stacked Column"
   chartType(5) = "Quit"
   chartType(6) = "Select 1-4, 5 to Cancel"
   
   cr = vbCr & vbCr
    msg = ""
   For j = 1 To 6
     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 5
           Exit Function
       Case 1,2
          lngType = xlColumnClustered
       Case 3
          lngType = xl3DColumnClustered
       Case 4
          lngType = xl3DColumnStacked
   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
   Set db = CurrentDb
   Set rst = db.OpenRecordset(recSource)
   colmCount = rst.Fields.Count
   rst.Close
   
   With grphChart
       .ColumnCount = colmCount
       .SizeMode = 3
       .Left = 0.2917 * twips
       .Top = 0.2708 * twips
       .Width = 5 * twips
       .Height = 4 * twips
   End With
   
   grphChart.Activate
   
   'Chart type, Title, Legend, Datalabels,Data Table
   With grphChart
        .chartType = lngType
        If typ = 3 Or typ = 4 Then 
   ' for 3D Charts only
          .RightAngleAxes = True
          .AutoScaling = True
        End If
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Font.Name = "Verdana"
       .ChartTitle.Font.Size = 14
       .ChartTitle.Text = chartType(typ) & " Chart."
       .HasDataTable = True
       .ApplyDataLabels xlDataLabelsShowValue
   End With
   
   'apply gradient color to Chart Series    
   For j = 1 To grphChart.SeriesCollection.Count
         With grphChart.SeriesCollection(j)
           '.Interior.Color = RGB(Int(Rnd(j) * 200), Int(Rnd(j) * 150), Int(Rnd(j) * 175))
           .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
           .Fill.Visible = True
           .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
           '.Barshape = xlCylinder 
   ' xlCylinder, xlConeToPoint, xlBox, xlPiramidtoMax
           If typ = 1 Then
               .Interior.Color = msoGradientVertical
           End If
           .DataLabels.Font.Size = 10
           .DataLabels.Font.Color = 3
           If typ = 1 Then
               .DataLabels.Orientation = xlUpward
           Else
               '.DataLabels.Orientation = xlHorizontal
               .DataLabels.Orientation = 45 'titlted angle in degrees
           End If
           End With
       Next
   
   'Y-Axis /(Z-Axis for 3D)Title
   With grphChart.Axes(xlValue)
      If typ = 2 Then
       .ReversePlotOrder = True
    'flips upside down
      Else
       .ReversePlotOrder = False
      End If
       .HasTitle = True
       .HasMajorGridlines = True
       With .AxisTitle
           .Caption = "Values in '000s"
           .Font.Name = "Verdana"
           .Font.Size = 12
           .Orientation = xlUpward
       End With
   End With
   
   'X-Axis Title
   With grphChart.Axes(xlCategory)
       .HasTitle = True
       .HasMajorGridlines = True
       .MajorGridlines.Border.Color = RGB(0, 0, 255)
       .MajorGridlines.Border.LineStyle = xlDash
       With .AxisTitle
           .Caption = "Quarterly"
           .Font.Name = "Verdana"
           .Font.Size = 10
           .Font.Bold = True
           .Orientation = xlHorizontal
       End With
   End With
   
   'Primary Y/Z Axis values label's font size
   With grphChart.Axes(xlValue, xlPrimary)
        .TickLabels.Font.Size = 10
   End With
   
   'X-Axis category Labels (Qrtr1, Qrtr2...)
   If grphChart.HasDataTable = False Then
       With grphChart.Axes(xlCategory)
           .TickLabels.Font.Size = 8
       End With
   Else
       grphChart.DataTable.Font.Size = 9
   End If
   
   '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 = 2
       .BackColor.SchemeColor = 19
       .TwoColorGradient msoGradientHorizontal, 2
   End With
   
   'Plot Area fill with Gradient Color
   With grphChart.PlotArea.Fill
       .Visible = True
       .ForeColor.SchemeColor = 2
       .BackColor.SchemeColor = 42
       .TwoColorGradient msoGradientHorizontal, 1
   End With
   
   grphChart.Deselect
   
   DoCmd.Close acReport, ReportName, acSaveYes
   DoCmd.OpenReport ReportName, acViewPreview
   
   ColumnChart_Exit:
   Exit Function
   
   ColumnChart_Err:
   MsgBox Err.Description, , "ColumnChart()"
   Resume ColumnChart_Exit
   End Function
   

   Preparing for Demo Run

2. Insert a new MS-Office Graph Chart Object on a new Report, change the basic property values as given in the earlier Article (Working with Graph Chart in VBA), and save the Report with the name myReport2. We can use the same Table that we have created for earlier examples.

   NB: Always create a new Chart Object for a new set of examples rather than using the same Chart created earlier. Some property changes were found to be shown incorrectly when reused for different Chart Types.

3. The Code can be tested either by running from a Command Button Click Event Procedure or directly from the Debug Window (Immediate Window). Press Alt+F11 to open the VBA Window,  press Ctrl+G for the Debug Window, and type the following command and press the Enter Key.

   ColumnChart "myReport2", "Chart1"

4. A menu will appear with four Column Chart Options. Type 1 in the Text Box and press the Enter key to run the first option.

   The Program Opens the Report myReport2 in Design View, modifies the Chart Properties, saves the Report with the Chart, and then re-opens it in Print Preview. A sample run image is given below.



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

5. Run the program a second time, and this time select Option number 2.

In this run, we are setting the ReversePlotOrder property value to True to flip the Chart upside down. The sample image is given below.

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

Every time you run the Code, the Colors are selected in Random order from the Color Chart given in the earlier Post (Working with Graph Chart in VBA) and will display the Chart Bars differently.

Saving the Report with the Chart.

If you plan to save the Chart and don't like the Color combination currently displayed, run the option more than once till you get the color combination to your liking. If you want to save the current run of the Chart, make a copy of the Report or Export the Report into MS-Access Snapshot Viewer Format (MS-Access 2000 or later).

Chart Area and Plot Area are set with a light Gradient Color so that they will look better when printed.

Sample Run of Option 3.

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

Sample Run of Option 4.

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

In Options 3 and 4 Runs, you can see that the Plot Area of the Chart is extended to display the Data Table and X-Axis Title. But, in the first two runs, these are appearing outside the Plot Area.

Saving PowerPoint Presentations.

You can copy and paste the finished Chart into PowerPoint Presentations. Before transporting the Chart to the PowerPoint page, copy and paste the Values from the Table into the Data Grid of the Chart so that you can edit the values in PowerPoint itself if needed.

1. Double-click on the Chart Object in Report Design View to activate the Chart and to display the Data Grid.
2. Open the Table in Datasheet-View, and click on the top left corner of the Table to highlight all the records.
3. Select Copy from the Edit Menu.
4. Right-click on the top-left corner cell of the Data Grid and select Paste from the displayed Shortcut Menu.

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