As nice as Excel 2007 is, and much of Office 2007 in general, Microsoft made some annoying decisions with developer access in VBA.
Charts are now Excel objects and their data are based on worksheets. This is pretty nice overall, but Microsoft does not allow access to the sheet data when pasted into Word or PowerPoint. So
Also, changes in the chart are not picked up with the macro reader. So developers can’t use the reader to help figure out the object model and thus write code.
The following routine is one alternative to the macro reader. Select a chart and then run the module. It will stop into break mode and then you can set up watches (right click on them to do so) on each of the three objects. You can select objects (keep the focus on the chart, though) and see the values change.
Public Sub ExploreChartElements()
‘select a chart and then run this, it will break and select various chart elements.. use watches…
Dim oChart As Excel.Chart
Dim oChartGroup As Excel.ChartGroup
Dim oSeries As Excel.series
Set oChart = ActiveChart
Set oChartGroup = oChart.ChartGroups(1)
Set oSeries = oChart.SeriesCollection(1)