how to add error bars into bar chart

[John Smith]

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").Left,
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

 

[isabelle]

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