A Line Charting VBA Syntax Demo. (1 Viewer)

Status
Not open for further replies.

ChrisO

Registered User.
Local time
Tomorrow, 00:16
Joined
Apr 30, 2003
Messages
3,202
G’day all.

Attached is a demo of a line chart.

Requirements…
As written the following is required: -
1. Access 2000 or later. It was produced in A003 file format but converted to A2000 file format for posting. It has been tested to Access 2007, which has a problem showing charts but that seems to have been resolved.
2. A screen resolution of 1280 x 1024 or better.

Aim…
The aim of the demo is to put as much VBA line chart syntax into one place (I personally don’t like to say “I did that somewhere but can’t find it now.”) To that end, and where possible, each chart syntax property is broken down to the bare minimum to perform the specific function. With charts that is not always possible as some properties interact so in that instance interacting properties need to be handled as a group. But where possible the property is handled within a With/End With block so that other properties may be added.

Execution Speed…
With that break down comes the penalty of slow execution, for example; to turn on/off Point Markers we could use the Series as a whole or do each Point individually. The reason I chose to do each individually is because it shows the syntax of doing so. This would then allow highlighting the maximum, or minimum, of a Series by changing the Point size or Point Font size or both. Because of the slow execution, a progress bar has been included.

Conditional Formatting …
With charts, by default as in this demo, the Series is automatically assigned a colour. This made it difficult to do conditional formatting on the data entry sub-Form. With the sub-Form there is a limit of 64 levels of conditional formatting using the old method (Resource #3). From a purely technical point of view there is virtually no limit, except for the number of controls on a Form, but in practical terms there is. A chart can become over crowded, to the point of being useless, and it would become very slow. So the number of Series (records) in the sub-Form should be kept reasonable.

Biggest Problem…
Perhaps the singularly most time consuming thing was to find a way to get Windows 7 to update a chart for display. With the help of RainLover, another member of this site, it was narrowed down to the Chart seeming to need to lose focus. There are other suggested methods we found on the web but none worked. However, when a Chart has finished formatting, clicking on another Control on the Form did display it. But a VBA SetFocus to another Control did not work, nor did a SetFocus to the Chart and then a SetFocus to another Control. In the long run, very long, we needed to use the Charts Updated event to SetFocus to the Chart and then to SetFocus to another Control. As a side effect this can cause screen flicker and that is still ‘work in progress’.

Error Handling…
Global error handling has been included and it is turned on. If you want to test or modify the code I would suggest turning it off. To do so go to Module mdlHandleErrors and set conHandleErrors to False. There is a thing with Charts in that changes need to be saved before running them. If you do not save your work you may get a message about ‘the object is not connected to the server’ (or some such). The best way I have found is to do a Compact/Repair after changes and allow the Form to auto open from the Start Up menu.

Late Binding…
The attachment requires no, and has no, references. What little DAO there is is done With CurrentDB and the Chart is just an Object. I removed the references to avoid the possibility of something going Missing and causing a failure to load. The downside of that is that Intellisense doesn’t work with the Chart Properties or Methods. If that is required then re-reference the Chart, please see Resources below.


Charts are not easy but please have fun modifying the attachment and if you have any questions, please ask them in the appropriate forum.

Regards,
Chris.

Resources…
1.
The Chart reference on my machine is: -
Microsoft Graph 11.0 Object Library
C:\program files\microsoft office\OFFICE11\GRAPH.EXE

2.
Microsoft Access 97 Sample Graphs Available in Download Center: -
http://support.microsoft.com/kb/186855

3.
Conditional Formatting old style: -
http://www.mvps.org/access/forms/frm0024.htm

Updates...
Version 1.4 (6-Apr-2010)
Added Mouse Move over Chart Plot Area
Version 1.3 (29-Mar-2010)
Added Linear Trendlines for all Series
Version 1.2 (4-Mar-2010)
Added Highlight Maximum and Minimum for Series


Search Key Words…
Chart Graph SeriesCollection Count HasTitle ChartTitle Text Font Name Size Bold Color Smooth HasDataLabels DataLabels Border HasLegend Legend LegendEntries LegendKey Interior MarkerStyle MarkerSize Points PlotArea Axes Axis AxisTitle TickLabels AutoActivate HasMinorGridlines HasMajorGridlines Caption Orientation CrossesAt MajorUnit MinorUnit MaximumScale MinimumScale Updated MarkerBackgroundColor MarkerForegroundColor Trendlines Add
 

Attachments

  • LineGraphDemoA2K3_ForPosting.zip
    628.7 KB · Views: 2,365
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom