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" <(E-Mail Removed)>
wrote in message
news:0a1dee5f-d6bb-41ff-848e-(E-Mail Removed)...
>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
>
Similar Threads
