how to add error bars into bar chart
 

Dear All,

I am trying to use following code to create a bar chart with error
bar. But " .ErrorBar Direction:=xlY, Include:=xlBoth,
Type:=xlCustom, Amount:=errBar" doesn't work.

Could anyone help me figure out how to handle this?

Thanks

John




Sub DrawBarChart2()
Dim barChart As ChartObject
Dim titles, srcData, errBar As Range

Application.ScreenUpdating = False

Set barChart =
ActiveSheet.ChartObjects.Add(Left:=Range("H1").Lef t,
Top:=Range("H1").Top, Width:=Range("A3:E18").Width,
Height:=Range("A3:E18").Height)

Set titles = Range("A1:F1") ' data: g1 g2 g3 g4 g5 g6
Set srcData = Range("A2:F2") ' data: 11.594816 17.29588
8.554076 14.671445 9.924798 10.263842
Set srcData = Union(titles, srcData)
Set errBar = Range("A3:F3") ' data: 3.299938235 1.630907253
0.883572613 3.966173892 2.840271819 2.192138694

With barChart
.Chart.SetSourceData Source:=srcData, PlotBy:=xlRows
.Chart.ChartType = xlColumnClustered
.Chart.Axes(xlValue).MajorGridlines.Delete
.Chart.Legend.Delete
.Chart.Axes(xlValue).HasTitle = True
.Chart.Axes(xlValue).AxisTitle.Text = "Group mean with std
error bar"
.Chart.Axes(xlCategory).HasTitle = True
.Chart.Axes(xlCategory).AxisTitle.Text = "groups"
.Chart.HasTitle = True
.Chart.ChartTitle.Text = "Bar chart with std errors"
With .Chart.SeriesCollection(1)
.HasErrorBars = True
.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlCustom,
Amount:=errBar
End With
End With
Application.ScreenUpdating = True
End Sub

how to add error bars into bar chart
 

hi John,

we must add these two arguments

Amount Optional Variant. Amount of the error. Used only for the amount of a positive error
when Type is set xlErrorBarTypeCustom.

MinusValues €‹€‹Optional Variant. Amount of negative error
when Type is set xlErrorBarTypeCustom.

..ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlErrorBarTypeCustom, Amount:=1, MinusValues:=-5

--
isabelle


Le 2011-12-06 16:55, John Smith a écrit :
Dear All,

I am trying to use following code to create a bar chart with error
bar. But " .ErrorBar Direction:=xlY, Include:=xlBoth,
Type:=xlCustom, Amount:=errBar" doesn't work.

Could anyone help me figure out how to handle this?

Thanks

John




Sub DrawBarChart2()
Dim barChart As ChartObject
Dim titles, srcData, errBar As Range

Application.ScreenUpdating = False

Set barChart =
ActiveSheet.ChartObjects.Add(Left:=Range("H1").Lef t,
Top:=Range("H1").Top, Width:=Range("A3:E18").Width,
Height:=Range("A3:E18").Height)

Set titles = Range("A1:F1") ' data: g1 g2 g3 g4 g5 g6
Set srcData = Range("A2:F2") ' data: 11.594816 17.29588
8.554076 14.671445 9.924798 10.263842
Set srcData = Union(titles, srcData)
Set errBar = Range("A3:F3") ' data: 3.299938235 1.630907253
0.883572613 3.966173892 2.840271819 2.192138694

With barChart
.Chart.SetSourceData Source:=srcData, PlotBy:=xlRows
.Chart.ChartType = xlColumnClustered
.Chart.Axes(xlValue).MajorGridlines.Delete
.Chart.Legend.Delete
.Chart.Axes(xlValue).HasTitle = True
.Chart.Axes(xlValue).AxisTitle.Text = "Group mean with std
error bar"
.Chart.Axes(xlCategory).HasTitle = True
.Chart.Axes(xlCategory).AxisTitle.Text = "groups"
.Chart.HasTitle = True
.Chart.ChartTitle.Text = "Bar chart with std errors"
With .Chart.SeriesCollection(1)
.HasErrorBars = True
.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlCustom,
Amount:=errBar
End With
End With
Application.ScreenUpdating = True
End Sub