Reading chart attributes in VBA for a spreadsheet documenter tool

I often get complex spreadsheets to amend and extend. As they were written in haste by others, the big task is understanding them.
I wrote a reporter that gets most attributes from most objects; this brings everything into one place rather than use dozens of diablog boxes. The output is also useful reference for creating new code in VBA - you can see the object names and value names without having to try to find a complete object diagram (if you know of one PLEASE say where).

Anyway I have a chart with datatable applied. This can be done manually or using VBA:

Please Login or Register  to view this content.

I want my reporter to show this attribute. But I see only SetElement, and no GET, although the thing is described as Read/Write. As so many characterictics use SetElement, I've no idea how to fetch and unravel the datatable option or the others that might be interesting.

Can you help, please?

I think there is a .getchartelement? From the help file:

GetChartElement Method
See AlsoApplies ToExampleSpecificsReturns information about the chart element at specified X and Y coordinates. This method is unusual in that you specify values for only the first two arguments. Microsoft Excel fills in the other arguments, and your code should examine those values when the method returns.

expression.GetChartElement(X, Y, ElementID, Arg1, Arg2)
expression Required. An expression that returns a Chart object.

X Required Long. The X coordinate of the chart element.

Y Required Long. The Y coordinate of the chart element.

ElementID Required Long. When the method returns, this argument contains the XLChartItem value of the chart element at the specified coordinates. For more information, see the “Remarks” section.

Arg1 Required Long. When the method returns, this argument contains information related to the chart element. For more information, see the “Remarks” section.

Arg2 Required Long. When the method returns, this argument contains information related to the chart element. For more information, see the “Remarks” section.

Remarks
The value of ElementID after the method returns determines whether Arg1 and Arg2 contain any information, as shown in the following table.

ElementID Arg1 Arg2
xlAxis AxisIndex AxisType
xlAxisTitle AxisIndex AxisType
xlDisplayUnitLabel AxisIndex AxisType
xlMajorGridlines AxisIndex AxisType
xlMinorGridlines AxisIndex AxisType
xlPivotChartDropZone DropZoneType None
xlPivotChartFieldButton DropZoneType PivotFieldIndex
xlDownBars GroupIndex None
xlDropLines GroupIndex None
xlHiLoLines GroupIndex None
xlRadarAxisLabels GroupIndex None
xlSeriesLines GroupIndex None
xlUpBars GroupIndex None
xlChartArea None None
xlChartTitle None None
xlCorners None None
xlDataTable None None
xlFloor None None
xlLegend None None
xlNothing None None
xlPlotArea None None
xlWalls None None
xlDataLabel SeriesIndex PointIndex
xlErrorBars SeriesIndex None
xlLegendEntry SeriesIndex None
xlLegendKey SeriesIndex None
xlSeries SeriesIndex PointIndex
xlShape ShapeIndex None
xlTrendline SeriesIndex TrendLineIndex
xlXErrorBars SeriesIndex None
xlYErrorBars SeriesIndex None

Originally Posted by yudlugar

From the help file:

I did look, honest! And went a-Googling - thanks for including the detail. I'll see what I can do now.

GetChartElement certainly not in my Excel 2010 help file! And I was Googling GetElement rather than GetChartElement which is not the obvious match, however its spec includes xlDataTable so it sounds right.

So I googled correctly and found a few entries, though all different from my needs. (You'll doubtless have done that.)

I found this example of use where a mouse action provides coordinates, so I now opine the method is telling me what it finds at a point in the chart. Here the moving mouse gives coordinates to use. This example warns the user if she moves the mouse over the chart legend.
Visual Basic for Applications

Please Login or Register  to view this content.

Well that’s nice. I did not know one could trap mouse overs in VBA (I knew in VB).

But it does not help me. How will I get back the msoElementDataTable and similar replies, especially as arg1 and arg2 return no data? And I would not know what coordinates to use for something whose presence I am trying to detect!

Thanks for the info, interesting and informative, but not a solution yet I fear.

Maybe you can use something like:

Please Login or Register  to view this content.

Thanks, that looks like the answer. It is so remote from the way the Datatable wis switched on!

I suppose your help file suggested HasDataTable - I'm still looking for a full explanation of the Chart and related object models.

Perfect, thanks so much. Points coming....