Abstracting Error Bar Data From Charts

[Spiggy Topes]

I have a series of charts that I build each day and mail out to
multiple recipients. Some of them would like to see the underlying
data. Rather than send the data files, I have provided them with a
macro to abstract the data from the charts they already receive, and
to set the source for the chart to that abstracted data.

Fine, except for the error bars. The charts contain custom error bars
on one series, and I'd like to abstract those from the charts too. But
I can not find the place in the object model where the data is
stored.

I know that the Chart.SeriesCollection will tell me whether there are
error bars, through the HasErrorBars property, and I know there is an
ErrorBars object, but it doesn't appear to contain the data. The Excel
help, as is invariably the case with Excel 2007, is absolutely
useless.

Anyone know how I get to this data?

--------

The full macro, in case anyone would have a use for it, is:

Option Explicit
Public Sub Extract_Data_From_Charts()
Dim iRows As Integer
Dim iCell As Integer
Dim iChart As Integer
Dim iChartObjects As Integer
Dim iSeries As Integer
Dim i As Integer

Dim chtChart As ChartObject
Dim shtSheet As Object

Dim strSheetName As String

Dim X As Object

iChart = 1
While iChart <= ActiveWorkbook.Charts.Count
' Calculate the number of rows of data.
Set shtSheet = ActiveWorkbook.Charts(iChart)
For iChartObjects = 1 To shtSheet.ChartObjects.Count
Set chtChart = shtSheet.ChartObjects(iChartObjects)
iRows = UBound(chtChart.Chart.SeriesCollection(1).Values)
strSheetName = shtSheet.Name
Worksheets.Add.Move After:=shtSheet
i = InStrRev(strSheetName, "Chart")
If i > 28 Then
i = 28
End If
If i = 0 Then
strSheetName = Left(strSheetName, 28) & " " &
iChartObjects
Else
strSheetName = Left(strSheetName, i - 1) & "(" &
iChartObjects & ")"
End If
ActiveSheet.Name = strSheetName
Worksheets(strSheetName).Cells(1, 1) = "X Values"

' Write x-axis values to worksheet.
''' For iSeries = 1 To
shtsheet.ChartObjects(iChartObjects).Chart.SeriesCollection.Count
With Worksheets(strSheetName)
.Range(.Cells(2, 1), .Cells(iRows + 1, 1)) = _

Application.Transpose(chtChart.Chart.SeriesCollection(1).XValues)
chtChart.Chart.SeriesCollection(1).XValues =
Worksheets(strSheetName).Range(.Cells(2, 1), .Cells(iRows + 1, 1))
End With

' Loop through all series in the chart and write their values
to
' the worksheet.
iCell = 2
For Each X In chtChart.Chart.SeriesCollection
Worksheets(strSheetName).Cells(1, iCell) = X.Name

With Worksheets(strSheetName)
.Range(.Cells(2, iCell), .Cells(iRows + 1, iCell))
= Application.Transpose(X.Values)
X.Values =
Worksheets(strSheetName).Range(.Cells(2, iCell), .Cells(iRows + 1,
iCell))
iCell = iCell + 1
End With
Next
Next iChartObjects
iChart = iChart + 1
Wend
End Sub

 

[Jim Cone]

When creating a chart manually, one has the option of specifying the error bar type and amounts
(where applicable).
The error bar types available are: fixed values, percentage, standard deviation, standard error or
a custom value.
I would just note on your spreadsheet that the Error bars are calculated using 2 standard deviations
(what ever you specify).
That should be sufficient for most.
Excel, of course, can separately calculate StdDev or StdErr for a series of values if that was
necessary.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
Permutations add-in: numbers/alphas (valid words highlighted)




"Spiggy Topes" <[email protected]>
wrote in message

 

[Spiggy Topes]

When creating a chart manually, one has the option of specifying the error bar type and amounts
(where applicable).
The error bar types available are:   fixed values, percentage, standarddeviation, standard error or
a custom value.
I would just note on your spreadsheet that the Error bars are calculated using 2 standard deviations
(what ever you specify).
That should be sufficient for most.
Excel, of course, can separately calculate StdDev or StdErr for a series of values if that was
necessary.

Doesn't help at all, I'm afraid. I said in the original posting that I
was using CUSTOM error bars, which are quartiles generated from a SAS
program. There's no way that I can ask the user to figure the actual
values out for themselves.

All I need to know is where in the object model I can find the data
that goes into the error bars. It patently has to be there, just as
the individual series have to be there, as the recipients see it
without access to the original source files. But it's probably off in
some obscure corner of the model, as so much of Excel appears to be.
But the help tells me all the stuff I don't need to know - mostly
formatting stuff I don't care about - and NOTHING about the data
content. "Microsoft" and "help" appear to be two words that just don't
belong in the same sentence any more. Sad.

 

[Jon]

The ranges used to define custom error bars are not exposed to VBA.