Working with Chart Object in VBA

Introduction.

Working with the Microsoft Access Chart Object in VBA can be quite complex — but it’s also a fascinating and rewarding process. Once the code is properly developed, it can be reused across different Access databases to create charts with a consistent look and feel in just a few minutes.

There are numerous chart types, chart areas, and object groups available, each with a wide range of configurable properties. Some of these properties, such as Auto Scaling, are specific to certain chart types — for example, 3D charts.

Note: Before proceeding, ensure that the following essential reference libraries are attached to your database. This will prevent VBA runtime errors when working with chart-related code.

Attaching System Reference Libraries

1. Open the Visual Basic Editor by selecting Tools → Visual Basic Editor or choosing Code from the View menu.

2. From the Tools menu, select References.

3. In the Available References dialog box, check the following libraries:

   • Microsoft Office 12.0 Object Library

   • Microsoft Excel 12.0 Object Library

These libraries are also required to run the demo database provided for download at the end of this page.

After some research and experimentation, I developed a VBA routine that can dynamically modify and format Microsoft Access charts with a clean, professional appearance. I hope you’ll enjoy working with it as much as I did. A sample result is shown in the image below.

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

Formatting Chart Elements

The resizing of the chart and formatting of its Report various elements — including the Chart Title, Axis Titles, Chart Area, Plot Area, Chart Bars with Gradient Colors, Legends, Grid Lines, Data Labels, and the alignment of Data Labels — are all controlled through VBA code. Placing a chart object on a report with minimal property settings is done manually as a starting point.

When the program is run, it presents three options for the chart type:

• Bar Chart

• Line Chart

• Pie Chart

The same chart object is dynamically transformed into the selected type, using sample values from a table for demonstration purposes. The Pie Chart only uses the first record from the table, and its data handling differs from the Bar and Line Charts. In this example, the Pie Chart is included for demonstration only and is not treated as a separate, fully detailed case.

Color Numbers

The following Color index numbers can be used in different combinations in the Program to get different shades of gradients.

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

Preparing for Trial Run

Follow the simple steps, explained below, and get prepared for the trial run.

1. Create a Table with the following Structure and save it with the name Table1.

   

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

2. Add the sample records with the values shown above.

3. Open a new Report in Design View.
4. Select Object from the Insert Menu and select Microsoft Graph 2000 Chart (or Microsoft Graph Chart in MS-Access 2003) from the Object Type List.

   The inserted chart object with default values will be in an activated state, displaying the default values in the Data Grid.

   

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

5. Click outside the chart on the Detail Section of the Report to deactivate and deselect the Chart.

6. Click on the Chart again to select it and display the Property Sheet (View ->Properties)

7. Change the following Property Values as given below:

   • Name: Chart1

   • Row Source Type: Table/Query

   • Row Source: Table1

   • Column Heads: Yes

   Even though the following properties can be changed manually, these are changed through the Code:

   • SizeMode : 3 (Zoom)

   • Column Count: Number of Columns in the Source Data

   • Left: 0.2917?

   • Top: 0.2708?

   • Width: 5.5729?

   • Height: 4.3854?

   2D Chart Area Zones

   An MS Access 2D Chart Object has 3 different areas:

   1. the outer Frame

   2. Chart Area where Chart Title, Axis Titles, and Legend Values appear.

   3. Plot Area where the chart Data Series Values, Data Labels, Gridlines, Scale, and Category Values appear in the Bar or Line Charts.

   If the Size Mode property is set to Clip (value 0), resizing the chart using the last four properties (Left, Top, Width, Height) will only adjust the outer frame, leaving the inner sections — the Chart Area and Plot Area — unchanged. If the property is set to Stretch or Zoom (values 1 or 3, respectively), the inner chart sections will resize to fit within the outer frame. Zoom is the preferred setting, as it maintains the correct aspect ratio of the chart during resizing, preventing distortion.

   The Column Count property is determined from the Row Source table or query.

   The program also validates the Row Source Type property. If it is not set to Table/Query, it will be corrected. However, if the Row Source property does not contain a valid table name or SQL string, the program will terminate and display an error message.

8. Save the Report with the Name myReport1.

The VBA Code.

9. Copy and Paste the following VBA Code into the Global Module and save the Module.

   Public Function ChartObject(ByVal ReportName As String, ByVal ChartObjectName As String)
   '------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : June-2008/Revision on 12/2019
   'Source Code : from Microsoft Access Help
   'and modified certain parameters
   '------------------------------------------------
   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
   Const twips As Long = 1440
   
   'On Error GoTo ChartObject_Err
   
   cr = vbCr & vbCr
   
   msg = "1. Bar Chart" & cr
   msg = msg & "2. Line Chart" & cr
   msg = msg & "3. Pie Chart" & cr
   msg = msg & "4. Quit" & cr
   msg = msg & "Select Type 1,2 or 3"
   
   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 = xlColumnClustered
       Case 2
          lngType = xlLine
       Case 3
          lngType = xl3DPie
   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."
      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.5729 * twips
       .Height = 4.3854 * twips
   End With
   
   grphChart.Activate
   
   'Chart type, Title, Legend, Datalabels,Data Table
   With grphChart
        .chartType = lngType
       ' .chartType = xlColumnClustered
       '.AutoScaling = True
       ' only for 3D type
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Font.Name = "Verdana"
       .ChartTitle.Font.Size = 14
       .ChartTitle.Text = "Revenue Performance - Year 2007"
       .HasDataTable = False
       .ApplyDataLabels xlDataLabelsShowValue
   End With
   
   'apply gradient color to Chart Series
   If typ = 1 Or typ = 2 Then
       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(1) * 54) + 2
           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
           End If
        End With
       Next j
   End If
   
   If ctype = 3 Then
    GoTo nextstep 'skip axes for pie chart
   End If
   
   'Y-Axis Title
   With grphChart.Axes(xlValue)
       .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
   
   With grphChart.Axes(xlValue, xlPrimary)
        .TickLabels.Font.Size = 10
   End With
   With grphChart.Axes(xlCategory)
        .TickLabels.Font.Size = 10
   End With
   
   nextstep:
   
   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 = 10
       .TwoColorGradient msoGradientHorizontal, 1
   End With
   
   grphChart.Deselect
   
   DoCmd.Close acReport, ReportName, acSaveYes
   DoCmd.OpenReport ReportName, acViewPreview
   
   ChartObject_Exit:
   Exit Function
   
   ChartObject_Err:
   MsgBox Err.Description, , "ChartObject()"
   Resume ChartObject_Exit
   End Function
   

   Running the Code

10. Open the VBA Module that you have pasted the code into if you have closed it.

11. Run the code from the Debug Window (press Ctrl+G to display the Debug Window) using the following syntax:

    ChartObject "myReport1", "Chart1"

    Or call the function from the On_Click() Event Procedure of a Command Button on a Form.

12. Select Chart Type 1, 2, or 3 for Bar Chart, Line Chart, or Pie, respectively.

The Program will open the Report myReport1 in Design View, modify the Graph Chart for the selected Type, save it, and then reopen it in Print Preview. You may minimize the VBA Window (Alt+F11) to view the Chart. Since the Report is saved after the changes, you may open it manually in the design view or Print Preview.

When you run the Code for Bar Chart or Line Chart, the Gradient Scheme Color Codes are selected randomly, which will apply different shades of Gradient Colors every time.

NB: Any suggestions for improvement are welcome.

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