error bars in VBA excel 2007 macro

[Roland]

I can't manage Y-error bars as the way it works in Excel 2000 VBA macro.
The macro stops and shows a run-time error 1004 at command;
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:=4
What's different in Excel 2007 vs 2000 ?

 

[Andy Pope]

Hi,

You will need to change your code for xl2007.
If the Amount is fixed, in your case to the value 4, then you will need to
use the correct Type argument rather than custom.
If it is custom then you will need an array of values. And you will need
both plus and minus values even if the minus values are not used.

Sub xx()

Dim objSeries As Series
Dim vntArrayPlus() As Variant
Dim vntArrayMinus() As Variant
Dim lngIndex As Long

With ActiveChart.SeriesCollection(1)
ReDim vntArrayPlus(1 To .Points.Count)
ReDim vntArrayMinus(1 To .Points.Count)
For lngIndex = 1 To .Points.Count
vntArrayPlus(lngIndex) = 4
Next
.ErrorBar Direction:=XlErrorBarDirection.xlY, Include:=xlPlusValues,
_
Type:=xlErrorBarTypeCustom, Amount:=vntArrayPlus,
MinusValues:=vntArrayMinus

.ErrorBar Direction:=XlErrorBarDirection.xlY, Include:=xlPlusValues,
_
Type:=xlErrorBarTypeFixedValue, Amount:=4
End With


End Sub

Cheers
Andy