| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to add error bars into bar chart | Charts and Charting in Excel | |||
| Customise error bars on individual bars in a bar chart 2007 | Charts and Charting in Excel | |||
| How to add error bars to 3-D chart | Excel Discussion (Misc queries) | |||
| Trendline for chart with error bars | Charts and Charting in Excel | |||
| Colouring error bars in a chart | Excel Programming | |||
Linear Mode
