how to add error bars into bar chart

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

Dear All,

I am trying to use the following code to create a bar chart with error
bar. But failed on
......
.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlCustom,
Amount:=errBar
......

Could any one help me figure out what's wrong?

Thanks

John



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

Application.ScreenUpdating = False

Set barChart = ActiveSheet.ChartObjects.Add(Left:=Cells(1,
8).Left, Top:=Cells(1, 8).Top, _
Width:=Range("A3:E18").Width, Height:=Range("A3:E18").Height)

Set titles = Range("A1:F1") 'data are: g1 g2 g3 g4 g5 g6
Set srcData = Range("A2:F2") 'data are: 11.594816 17.29588
8.554076 14.671445 9.924798 10.263842
Set srcData = Union(titles, srcData)
Set errBar = Range("A3:F3") 'data are: 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
 
You need to convert errBar from a range to an address, like this:

.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlCustom, Amount:=errBar.Address(True, True, xlR1C1, True)

(in Excel 2010 and maybe in 2007 you can use xlA1 instead of xlR1C1).
 
Back
Top