how to add error bars into bar chart


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,

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



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).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,
End With
End With
Application.ScreenUpdating = True
End Sub


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question