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