Error bars in VBA

[mhandersen]

Hi everyone,

I am having trouble with some vba code to add error bars to a new
series I am creating. When I run the following code I get a 'ErrorBar
method of Series class failed' error on the first .errorbar line. Can
anyone help me out?

With ActiveChart.SeriesCollection.NewSeries
.name = GraphForm.tbxSeriesName.Value
.Values = Worksheets("Data").Range("B1:B25")
.XValues = Worksheets("Data").Range("A1:A25")
.ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth,
Type:=xlCustom, _
Amount:=Worksheets("Data").Range("C1:C25")
.ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth,
Type:=xlCustom, _
Amount:=Worksheets("Data").Range("D125")
End With

There is probably a better way to do this (add error bars to a new
series) but I am not familiar enough with Excel programming to know
what it is. Thanks for any help in advance.

 

[Graham Whitehead]

I have had some problems in the past drawing charts with error bars. After
a while I found that it helps to organise the code a more logical fashion.
Here is an example of something I has lying around which I have just grabbed
for you. Hopefully, you can see where to change bits for your own
requirements.

dim rngData as range
dim rngErrX as range
dim rngErry as range
dim chtChart as chart

With ActiveSheet
Set rngData = Sheets("......").Range(".....")
Set rngErrX = Sheets("......").Range(".....")
Set rngErrY = Sheets("......").Range(".....")
Set chtChart = .ChartObjects.Add(..., ..., ..., ...).Chart
End With

With chtChart
.ChartType = xlXYScatter
.SetSourceData rngData, PlotBy:=xlColumns
with SeriesCollection(1)
.ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth,
Type:=xlCustom, Amount:=rngErrX
.ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth,
Type:=xlCustom, MinusValues:=rngErrY
end with
end with

Hope that helps

 

[Tushar Mehta]

A couple of points.

First, the only kind of a chart that supports error bars is one with a
numeric X axis (XY Scatter chart, for example).

Second, I'm not sure how you got that code. My usual way of generating a
starting point is to use the macro recorder. With it, the code I got was
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=data!R1C1:R25C1"
ActiveChart.SeriesCollection(1).Values = "=data!R1C2:R25C2"
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=data!R1C3:R25C3"
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=data!R1C4:R25C4"

which is easily modified to
With ActiveChart.SeriesCollection.NewSeries
.XValues = "=data!R1C1:R25C1"
.Values = "=data!R1C2:R25C2"
.ErrorBar Direction:=xlX, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=data!R1C3:R25C3"
.ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=data!R1C4:R25C4"
End With

The above works just fine with a XY Scatter chart but fails with a couple of
the other types that I tested.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Minor adjustment to Tushar's first statement:

The only kind of a chart that supports **X** error bars is one with a
numeric X axis (XY Scatter chart, for example).

- Jon

 

[Tushar Mehta]

Minor adjustment to Tushar's first statement:

The only kind of a chart that supports **X** error bars is one with a
numeric X axis (XY Scatter chart, for example).

- Jon
-------

Thanks, Jon. {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[-matt]

Thanks for the help everyone. Come to find out that when you try and do
an Include:=xlErrorBarIncludeBoth or Include:=xlBoth (which is the same
thing as far as I can see) with the Type:=xlCustom you must include
both the Amount:= (for the positive directions) and the MinusValues:=
(for the negative directions) even if you want to use the same values
for both the plus and minus of the error bars. So that was what I was
leaving out. Thanks again.