Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Column Chart and VBA

Introduction.

In our earlier example, Working with Access Graph Chart in VBA, we have made preparations to try out MS Access Charts with VBA and did sample Runs for three Types of Charts viz. Column Clustered, Line Chart, and Pie Chart. If you have landed on this page for the first time you may go to the earlier post by clicking on the link above. Go through the simple preparations explained there, by following Steps 2 to 7, to try out the code given here.

Earlier, we could change the Chart type through Code to one of three types with common property settings that can be applied to all three. But we have conditionally skipped some properties not relevant to PIE Chart, like Chart Axis Titles. We will deal with the PIE Chart separately later and will see how to format individual Pie Slices and change other settings as well.

Here, we will try only one Category of Charts viz. Column Chart Type and will run the Code for 4 different designs. This type of Chart with vertical Bars is the most commonly used Chart and is loosely referred to as a bar chart. The Category Description of the Values plotted on the chart are placed horizontally along the X-Axis, below the chart and the Chart Scale is calculated automatically and placed vertically on the left side of the Chart (on the Primary Y-Axis) with fixed intervals of values. This can be set manually too if needed.

When very small Values are plotted along with large values on the Chart the variations of the small values over a period of time may not be clearly visible and may become difficult to differentiate one value from the other. In such situations, the small value Series can be plotted on the Secondary Y-Axis (right-side vertical axis) and the variations in the values will be clearly visible. With the help of data labels, the values on the chart become more meaningful and easily analyzable.

When 3D Chart Type is selected Y-Axis gives way to Z-Axis for displaying Tick Labels and Axis Titles. The Charts with Horizontal Bars are the real 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 Cone or Cylinder.

In this example, we are turning On the HasDatatable Property of Chart Object that will display the Source Data Table on the Chart. The X-Axis Category Labels Qrtrl to Qrtr4 becomes part of the Data Table display and if we attempt to use the X-Axis Tick-Label Property to set Font or Font Size we will run into errors. We have used it conditionally without eliminating the code altogether so that you will know the difference.

DataLabels Orientation Property gives us the ability to display the Data Labels tilted to a specified Angle in Degrees, besides normal Horizontal or Vertical formats. To try out the examples do the following:

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 Step-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 shown incorrectly when re-used 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 VBA Window,  press Ctrl+G for Debug Window and type the following command and press 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.

  5. Run the program again 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.

Every time when 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 you may 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 you may 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 light Gradient Color so that they will look better when printed.

Sample Run of Option 3.

Sample Run of Option 4.

In Option 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 left top corner of the Table to highlight all the records.
  3. Select Copy from Edit Menu.
  4. Right-Click on the left-top 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
Share:

1 comment:

  1. Hi Friend.I keep reading your website.it is very nice.YOUR POSTING IS REAL STATE.plz exchange link.then let me know I will do the same.my url,
    www.googleadsensesystem.blogspot.com ,
    www.softwareonlinehelper.blogspot.com.
    thanks.

    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