chart error bars
 

Has anyone had success with creating custom error bars in Excel 2007 from VBA?

In prior versions I was able to do so with

ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=MySheet!R2C17:R4C17"

When I try that code in 2007 I get error 1004, Application-defined or
object-defined error.

chart error bars
 

How odd. It works fine but only if you do it like this

On Error Resume Next
' set the error bars
On Error Goto 0

Maybe one for Andy Pope !

Regards,
Peter T

"Paula Galloway" wrote in message
...
Has anyone had success with creating custom error bars in Excel 2007 from
VBA?

In prior versions I was able to do so with

ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=MySheet!R2C17:R4C17"

When I try that code in 2007 I get error 1004, Application-defined or
object-defined error.

chart error bars
 

As I mentioned, the code worked for me under 'on error resume next'. However
to get it to work without forcing, it seems need to supply both error bars,
even if the minus are not required.

Set sr = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
sr.ErrorBar Direction:=xlY, _
Include:=xlBoth, _
Type:=xlCustom, _
Amount:="=Sheet1!R2C17:R4C17", _
MinusValues:="={0}"

Could also do
Amount:=Range("$Q$2:$Q$4")

Note, in the above, chart, series index and sheet name changed from your
original.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
How odd. It works fine but only if you do it like this

On Error Resume Next
' set the error bars
On Error Goto 0

Maybe one for Andy Pope !

Regards,
Peter T

"Paula Galloway" wrote in
message ...
Has anyone had success with creating custom error bars in Excel 2007 from
VBA?

In prior versions I was able to do so with

ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=MySheet!R2C17:R4C17"

When I try that code in 2007 I get error 1004, Application-defined or
object-defined error.

chart error bars
 

Peter -

You were right. Andy figured this one out for me some time ago, and it's now
part of my bag of tricks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Peter T" <peter_t@discussions wrote in message
...
As I mentioned, the code worked for me under 'on error resume next'.
However to get it to work without forcing, it seems need to supply both
error bars, even if the minus are not required.

Set sr = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
sr.ErrorBar Direction:=xlY, _
Include:=xlBoth, _
Type:=xlCustom, _
Amount:="=Sheet1!R2C17:R4C17", _
MinusValues:="={0}"

Could also do
Amount:=Range("$Q$2:$Q$4")

Note, in the above, chart, series index and sheet name changed from your
original.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
How odd. It works fine but only if you do it like this

On Error Resume Next
' set the error bars
On Error Goto 0

Maybe one for Andy Pope !

Regards,
Peter T

"Paula Galloway" wrote in
message ...
Has anyone had success with creating custom error bars in Excel 2007
from VBA?

In prior versions I was able to do so with

ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=MySheet!R2C17:R4C17"

When I try that code in 2007 I get error 1004, Application-defined or
object-defined error.

chart error bars
 

Andy figured this one out for me some time ago

I should have guessed :-)

Regards,
Peter T


"Jon Peltier" wrote in message
...
Peter -

You were right. Andy figured this one out for me some time ago, and it's
now part of my bag of tricks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Peter T" <peter_t@discussions wrote in message
...
As I mentioned, the code worked for me under 'on error resume next'.
However to get it to work without forcing, it seems need to supply both
error bars, even if the minus are not required.

Set sr = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
sr.ErrorBar Direction:=xlY, _
Include:=xlBoth, _
Type:=xlCustom, _
Amount:="=Sheet1!R2C17:R4C17", _
MinusValues:="={0}"

Could also do
Amount:=Range("$Q$2:$Q$4")

Note, in the above, chart, series index and sheet name changed from your
original.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
How odd. It works fine but only if you do it like this

On Error Resume Next
' set the error bars
On Error Goto 0

Maybe one for Andy Pope !

Regards,
Peter T

"Paula Galloway" wrote in
message ...
Has anyone had success with creating custom error bars in Excel 2007
from VBA?

In prior versions I was able to do so with

ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=MySheet!R2C17:R4C17"

When I try that code in 2007 I get error 1004, Application-defined or
object-defined error.

chart error bars
 

Thanks!

"Peter T" wrote:

Andy figured this one out for me some time ago

I should have guessed :-)

Regards,
Peter T


"Jon Peltier" wrote in message
...
Peter -

You were right. Andy figured this one out for me some time ago, and it's
now part of my bag of tricks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Peter T" <peter_t@discussions wrote in message
...
As I mentioned, the code worked for me under 'on error resume next'.
However to get it to work without forcing, it seems need to supply both
error bars, even if the minus are not required.

Set sr = ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1)
sr.ErrorBar Direction:=xlY, _
Include:=xlBoth, _
Type:=xlCustom, _
Amount:="=Sheet1!R2C17:R4C17", _
MinusValues:="={0}"

Could also do
Amount:=Range("$Q$2:$Q$4")

Note, in the above, chart, series index and sheet name changed from your
original.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
How odd. It works fine but only if you do it like this

On Error Resume Next
' set the error bars
On Error Goto 0

Maybe one for Andy Pope !

Regards,
Peter T

"Paula Galloway" wrote in
message ...
Has anyone had success with creating custom error bars in Excel 2007
from VBA?

In prior versions I was able to do so with

ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=MySheet!R2C17:R4C17"

When I try that code in 2007 I get error 1004, Application-defined or
object-defined error.