Unsolved: Manipulate chart object via code (MS Graph) on a FORM

ironfelix717

ironfelix717

Registered User.
Local time
Today, 16:58
Joined
Sep 20, 2019
Messages
193
Hello,

Next up on unsolved mysteries: manipulating a chart object via code. I reached out to Access Forums about this issue with no success in solving: here

The problem at hand is manipulating a graph/chart via code. Specifically, manipulating the axes (scaling), plot area, other specific formatting. You know, things you should be able to do with VBA.

Confusions
The entire charting mechanism in Access is confusing. Its worth noting these confusions, which i'm sure i'm not alone on.
These sources of confusion might be... (i'm not requesting answers - though be my guest)
  • insert "modern chart" vs "chart" - lets keep it simple and just call something a "chart". Are they different? Are they the same VBA object? Who even knows? From my experience the "modern" charts are awful and you can make a chart about as complex as a grade 3 student could.
  • Microsoft Graph 16.0 Library VBA reference - None of the other controls need a specific reference, so why should a chart?
  • Excel and Access seem to handle chart object completely differently - I could probably hop on Excel right now and do everything i need to do in VBA with a chart without any need for additional references.
  • The documentation on the vba "chart object" references a chart on a REPORT - not a form.
Some of the pitfalls above is probably due to my speculation that graphing in Access (as well as Excel) is built on underlying MS Graphs frameworks ported to VBA.

Prefaces
  • The problems i am facing are with Windows 10, Access 2019 x64
  • The chart i reference is on a form, not a report
  • There is a test file for example in the description

Problems
All i am trying to do is really adjust the max value and min value of the axes.

First and foremost, the newcomer would think maybe i can just use the control's properties. Surely the graph properties are in there to adjust the axes. Wrong. Just a bunch of rather useless properties besides 'Name', 'RowSourceType' etc, etc...

So... googling and googling. I made attempts at....

------------------------------------------------------------------------------------------

Code: 
dim mygraph        as Chart
set mygraph = me.controls("testgraph")

The mygraph object won't set because its invalid type - hence the documentation says '... on a REPORT'
Should a chart on a report be any different than one in a form? hmmm...

------------------------------------------------------------------------------------------

The closest i've gotten to manipulating a graph via code (besides frame height, etc) is the plot area. And i've used this...

Code: 
dim obj            as Object
Set obj = testgraph.Object.Application.Chart
obj.PlotArea.Height = 2 * 1440   'WORKS
obj.Axes(xlSeries, xlPrimary).MinimumScale = 7  'DOESNT WORK - CANT FIND 'AXES PROPERTY'

The above lets me change the plot area. But has no property for Axes.
Furthermore - i have no intellisense for that object either, which is wonderful 🙃 (probably because its dimmed as 'Object')
Yes - MS Graph 16.0 reference is checked!
-----------------------------------------------------------------------------------------



So, i am hoping someone out there can provide some insight into whats going on here and why attempting to make chart adjustments via code has to be so difficult!

Thanks for your help!
 

Attachments

Last edited:
Update:

Still doing some investigating.

I was able to change the max and min scale of the axes for a SCATTER PLOT. The original issue I had with previous attempts was getting runtime error '1004: "Unable to get the Axes property of the chart class"

That error description sounds a lot like "We can't find the Axes property of the chart class"
Instead, what the error means is "you have a erroneous value in Axes but were too lazy to inform you of it"
For instance, calling .Axes(3) will fail because there is no "tertiary" axis. The max is 2 (xlPrimary, xlSecondary). That has nothing to do with being unable to "get the Axes property of the chart class" - its a user error that should've been presented as such. Again, bad design on MS part.

Now, onto what I did achieve.

The charts I have been using are Line Plots - which it shouldn't matter the slightest in this case.
This worked on scatter plot:

Code: 
Me.graphVVT.Object.Axes(2, 1).MinimumScale = 5
Me.graphVVT.Object.Axes(2, 1).MaximumScale = 15

--------------------------------------------------------------------------------------

I still have no intellisense. Fortunately, Microsoft documented all the properties/methods of the Object.Axes method.
... . . . . .Oh wait... They didn't ----> documentation

That link is for Excel's "chart" object, which I am 'somehow' accessing via an Access chart using me.graph,object.axes()
---------------------------------------------------------------------------------------

OK lets, look at Access' "chart" object -----> documentation
One would think they could do this....

Code: 
Dim c       As Chart
Set c = graphVVT  'using .Object doesn't work either
c.PrimaryValuesAxisMaximum = 6
c.PrimaryValuesAxisMinimum = 4

Again, as demonstrated in OP, the above fails when object 'c' is trying to set. The chart object in Access as a VBA component is unbelievably worthless.

---------------------------------------------------------------------------------------

So, again, an unsolved mystery.
 
Last edited:
I'm not sure how much I can assist here, but I've been doing quite a bit of PowerPoint automation from Access and was massively underwhelmed at how well documented it all is.
I had to late bind everything as I can't rely on the end client having references set.

To manipulate the PPT object I ended up using
Code: 
  oSh.Chart.Axes(2).MaximumScale = iMaxPrice + 1.5    ' X axis max value
  oSh.Chart.Axes(1).MaximumScale = iNoOfCols + 1      ' Y axis no of columns

Where oSh is effectively recursed to

PowerPointObject.PresentationObj.Slides(1).Shapes("MyShapeName")

I'm wondering if a similar approach might work on the Access chart object?? Just my 2p worth.
 
Minty,

Thanks for replying. Yeah, thats the same properties i accessed above...

The more i research this the more frustrating this is. The fact that they've littered the market with about 50 different combinations of charting in office over the last 20 years has really helped matters. What a joke.

You'd think by 2020, after nearly 27 years of development, the Office team would have at least found a way to standardize an object model between all the Office applications. - but they didnt. Comical.
 
It is comical. The methods in PPT are all over the place. Something as simple as changing a colour is just plain silly for instance;

oSh.Chart.SeriesCollection(3).Points(iPoint).DataLabel.Font.Color = rs2.Fields("PColour")
oSh.Chart.SeriesCollection(2).Points(iPoint).Format.Fill.ForeColor.RGB = rs2.Fields("PColour")
oSh.Chart.SeriesCollection(3).Points(iPoint).DataLabel.Font.ColorIndex = rs2.Fields("PColour")

The first changes the datalabel font colour, the second the markertextlabel font colour, the third changes the whole series data labels colour. It's not documented as far as I could find.

It's as consistent as a forgetful politician.
 
Agreed.

What I am working on next is building my own class to work specifically with Scatter or Line charts. That way i at least have a simplified intellisense dialog and can actually write some code. This class wouldn't be as big of a deal had they provided all the properties in chart.object somewhere.

I'll share if I get anything useful. Will report back if any solid findings.
 
UPDATE:

OK Back again with more information.

I began diving into building this class and things are going somewhat smoothly.
The class so far can adjust the scaling and units of a scatterplot.

However, as we discussed, the documentation is a joke. Therefore, finding all the properties is basically done via google.

BUT! When googling for how to change the scatterplot line colors, i found an amazing thread that answered the question of why intellisense isn't working. ------- > HERE

Thanks to Utter Access contributor BananaRepublic, I know how to properly dimension the scatterplot as the correct "chart" object:

To enable early binding and therefore intellisense support, you need to reference the Graph library. In VBA editor, Tools -> Reference and select "Microsoft Graph XX.X Object Library"
When in code:
CODE
Dim cht As Graph.Chart
Set cht = Me.MyChartControl.Object
cht.<whatever>
....
....
Click to expand...

That helps somewhat, but the intellisense doesn't go deep enough after .Axes() or even .SeriesCollection(), which aligns with the crap documentation MS offered on their charts object model.

Either way, its progress.
Will report back with more updates.
 
Intellisense doesn't always expose every method or property of an object.
Did you look here and not find what you need?
docs.microsoft.com

Chart object (Access)

Office VBA reference topic
docs.microsoft.com
 
@Micron, I think @ironfelix717 references that link in Post#2. The problem is that it was written in 2018 and most of the properties have no links to their details, with the promise of 'more info coming soon'! 😣
 
cheekybuddha said:
@Micron, I think @ironfelix717 references that link in Post#2. The problem is that it was written in 2018 and most of the properties have no links to their details, with the promise of 'more info coming soon'!
Click to expand...
I believe that page is for Excel and was so stated in the post. I posted a link for the Access chart object
 
Sorry, missed that link. What didn't help me is that when I used the first link I saw the comment about missing info and that it was for Excel. When I used my bookmark to the Access portion of the Office object model and navigated to the Access Chart Object it wasn't there. When I used the second link, it was there.
 
Update(s):

This will probably be my final post for this, as i've achieved what I need for personal use for now. Here are final points, until next time.


Custom Class "ScatPlotFormatter'
I have attached a class that is called "ScatPlotFormatter".

Note: Can't upload. The file is 1.11mb and says "File is too large". Even after compacted and zipped. Thats disappointing.

Purpose of this class is to make it easier to manipulate key properties of a scatter plot. It simply allows me to have access to intellisense and not have to re-look at notes or forum posts or scratch my head to do something as simple as change a line color because MS couldn't find a way to properly write an object in their own language... for their own software... (don't get me started 😅)
Like...

-max scale
-min scale
-major unit
-minor unit
-line color
-line thickness
-line style
-assigning series to a specific axis

What i could not achieve here is...

-marker properties (color, size, style) - I was able to access 0 properties of a marker. And i hate markers so its not that much of a chip on my shoulder.
-points - I thought it would be super awesome to color specific points via VBA (Say, if value > 20, make red). I'll probably investigate this in the future. Could do some really cool gradients and stuff!

There is an example scatter plot included and a sub that shows modifications. You can do a lot with this which is cool, like scale the axes in a loop which looks cool!

Note: this only works with a ScatterPlot! Does not work with a Line Graph.

Its rough and dirty, i just threw it together to make my life easier with my current project. Maybe someone else will find value.

Closing Comments on Graphing in VBA/Office/Access
I think i've made my feelings clear on this - perhaps too clear, but i want to reinforce to anyone looking to use Access as a platform to build a graph dependent application, the precautions and issues. My go-to graphing app platform is Excel, but frankly, Access is a much superior platform in many ways. Its fast. It looks way better and feels way more modern than Excel in both programming and UI. Anyway. Here are some finishing points
  1. Documentation: Need i say more... Absolute rubbish! No solid documentation anywhere for a property list beyond the first layer for the "Chart Object" - which is accessed via Graph.Chart object with references to Microsoft Graph 16.0.
  2. Excel vs Access: Excel being the workhorse of the 2 applications, there is obviously a lot more resources out there for Excel graphing in VBA. You can contrive the properties for Access to some extent by examining the Excel object model, but mostly, you'll find yourself annoyed when there are differences between the two: which there should not be.
  3. The object model appears to be very sensitive to the actual chart type. For instance, the class i provided does not work with a Line chart. It probably would've been a smart idea for MS to...
    1. not include so many charts in the first place (how many charts do you actually need??)
    2. break the "chart" model up by chart types (dim mychart as LineChart) - Because i realize how complex building that object model is with 50 different charts that are entirely different from one another.
  4. Time is a factor: MS changed things around over the years with their object model (which you conveniently know little about in the first place). So finding resources for correct properties that are current is difficult.

Best regards, thanks for the help. Will keep thread unsolved for any other contributors that might be interested in indulging.
 
Maybe my sentiment is a hold-over from days of yore, but I've oft said in one forum or another that when it comes to using graphs in Access, I prefer to rub sand in my eyes. I think you're adding support for that view with your comments. Sorry to hear this didn't pan out for you.
 
Update:

About a week later from finishing up the class for manipulating Scatter plots.

Implementing this into my main project wonderfully brings several thousand curse words to the forefront of my computer screen. Heaven forbid you assign an erroneous value to an Axes.Property and the whole chart throws a fit with the error i mention in my original post:
1004: "Unable to get the Axes property of the chart class

or how about...

1004: "Unable to get the Series property of the axis class

So descriptive. We are so grateful for such a vivid description of what is going wrong.

Which the only way to remedy such an error is to literally delete the chart and create an entire new chart object in the Access designer UI. Such errors are produced, for example, when assigning an axis max value when the user has already deleted the physical axis within the form designer. Therefore, the whole framework has a fit and cries and cries and cries some more until you are forced to wipe the object and restart.

[INSERT] BillMays.Voice "But wait Theres More!"

Not only does it cry about the "Axes" property of the chart class - But the Chart object will raise an error in a non-systematic way, which just so happens to be the most helpful thing when troubleshooting a problem in your code (sarcasm)....

Code: 
dim obj   as Graph.Chart
set obj = MyGraph.object  'this is where 1004 will be risen NEXT execution'

obj.Axes(1).MaximumScale = 5  'this is the problematic line - for example'

consensus: MS you are a failure at developing a successful graphing framework for Access.
My advice: forget trying to even touch a chart in Access with VBA, unless all you want to do is change the color of your title (HA so useful)
 
ironfelix717 said:
My advice: forget trying to even touch a chart in Access with VBA, unless all you want to do is change the color of your title (HA so useful)
Click to expand...
See post 15. Welcome to the club! However, as a member, you will not be permitted to even place a chart in an Access report or form. ;)
 
I guess I want to 'pile on', saying that I've run into the exact same challenges trying to include charts/graphs in my Access solutions ----- FOR YEARS. It is the "for years" part that is so frustrating. It's one thing to forego a capability when a solution is starting out. It's another to simply ignore it forever. I guess "Modern Charts" is supposed to reflect a renewed commitment to this - but it falls so woefully short - not even measuring up to 1/10 the capability of the old/outdated/barely working charts. That's pretty sad. (I can't even set a y-axis to be logarithmic ---- really?) I've had to "punt" on including certain charts in the solution - and resort to outputting the chart in an Excel export - because Access simply cannot do it. (I'm a little confused as to why it is that MS can't integrate their own Excel charts into their own Access...).

Rather than *only* complain - I will interject that I WAS able to control a bar chart as desired (on an 'old' chart - not a 'modern' chart) - at least in some fashion. I wanted each bar to be a particular color depending on its value. (Really just two colors - i.e., when the value was above/below a particular value.)

On the odd chance that this helps anyone - I was able to do this as follows:
Dim chrtBar As Object Dim dsBar As Object Dim intOrd As Integer Dim varValue Dim varCheck '******************************************************* Set chrtBar = Me.chrtEstSummary.Object Set dsBar = chrtBar.Application.datasheet intOrd = 1 varValue = dsBar.Range("A" & intOrd) varCheck = dsBar.Cells(intOrd + 1, 1) Do While varCheck <> "" If varValue >= 0 Then 'Set to "bad" color - light yellow in our case. chrtBar.SeriesCollection(1).Points(intOrd).Interior.Color = RGB(255, 255, 204) Else 'Set to "good" color - light green in our case. chrtBar.SeriesCollection(1).Points(intOrd).Interior.Color = RGB(153, 204, 0) End If intOrd = intOrd + 1 If intOrd > 20 Then Exit Sub varValue = dsBar.Range("A" & intOrd) varCheck = dsBar.Cells(intOrd + 1, 1) Loop
 
Last edited:
You must log in or register to reply here.

Similar threads

R
Access Report - Custom Stacked Line Chart
Replies
2
Views
764
June7
June7
B
Caller ID via TAPI (tapi32.dll) from phone phone PBX
Replies
1
Views
252
Uncle Gizmo
Uncle Gizmo
P
Need VBA Code Guidance - BeforeUpdate Event
Replies
6
Views
411
The_Doc_Man
The_Doc_Man
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
969
The_Doc_Man
The_Doc_Man
Uncle Gizmo
How to Simplify MS Access Form Searches Using Class Modules and Collections to Pair Text Boxes and Option Groups for Efficient SQL Query Generation
Replies
14
Views
817
MarkK
MarkK

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom