Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 error bars, why so complicated...
Hi,
How can the task of adding 'error bars' to a graph be simplified with excel 2007. In excel 2003 it is done way easier, almost just by double clicking the graph. In the 2007 version you have to walk through menus and then more menus to get the task done. When one has to do this for 40 graphs then the 2007 way quickly becomes a huge hassle. Any way to make excel 2007 as easy to use as excel 2003 for this particular feature ? (maybe with a macro or some other way to get this added to the 'right' click menu somehow) Thanks, Michael |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 error bars, why so complicated...
Michael -
This is one of the problems with charting in Excel 2007. I see no way within the built in user interface to handle error bars any more easily than you've found. To alleviate this and other issues, I've started designing my own set of dialogs for editing charts. This is a nights and weekends kind of effort, so it won't be available too soon, unfortunately. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mwildt" wrote in message ... Hi, How can the task of adding 'error bars' to a graph be simplified with excel 2007. In excel 2003 it is done way easier, almost just by double clicking the graph. In the 2007 version you have to walk through menus and then more menus to get the task done. When one has to do this for 40 graphs then the 2007 way quickly becomes a huge hassle. Any way to make excel 2007 as easy to use as excel 2003 for this particular feature ? (maybe with a macro or some other way to get this added to the 'right' click menu somehow) Thanks, Michael |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 error bars, why so complicated...
Hi Jon,
Appreciate your reply on this subject. Sad to hear excel 2007 have gone down hill compared to the 2003 version. Hope they will bring back some of the good features from 2003 that got removed. Thanks, Michael "Jon Peltier" wrote: Michael - This is one of the problems with charting in Excel 2007. I see no way within the built in user interface to handle error bars any more easily than you've found. To alleviate this and other issues, I've started designing my own set of dialogs for editing charts. This is a nights and weekends kind of effort, so it won't be available too soon, unfortunately. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mwildt" wrote in message ... Hi, How can the task of adding 'error bars' to a graph be simplified with excel 2007. In excel 2003 it is done way easier, almost just by double clicking the graph. In the 2007 version you have to walk through menus and then more menus to get the task done. When one has to do this for 40 graphs then the 2007 way quickly becomes a huge hassle. Any way to make excel 2007 as easy to use as excel 2003 for this particular feature ? (maybe with a macro or some other way to get this added to the 'right' click menu somehow) Thanks, Michael |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 error bars, why so complicated...
Hi Jon,
I looked at your very nice web page and I cooked up a macro (based on your examples) to do what I needed. It has unfortunately 2 issues which I cannot find an explantion to. - It fails when running in Excel 2007 in the line with SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:=rngChtErrorVal - The other issue is with the caps on the error bars. It seems like there is some 'memory' of the EndStyle type. Just does the opposite of what is done in the script until you have corrected it once. Only verified this with Excel 2003 ..SeriesCollection(1).ErrorBars.EndStyle = xlNoCap The script requires the following data to be selected, example: Compound B Samples Response a 1 0.2 b 2 0.3 c 3 0.1 d 4 0.4 e 5 0.5 f 6 0.2 g 7 0.3 h 8 0.3 Compound B is title Samples is the x-axis title Response is the y-axis title Script: Sub EmbeddedGraph() ' ' EmbeddedGraph Macro ' ' Keyboard Shortcut: Ctrl+g ' Dim myChtObj As ChartObject Dim rngChtData As Range Dim rngChtXVal As Range Dim rngChtYVal As Range Dim rngChtErrorVal As Range Dim iColumn As Long 'Make sure a range is selected If TypeName(Selection) < "Range" Then Exit Sub 'Define chart data Set rngChtData = Selection 'Define chart's X values With rngChtData Set rngChtXVal = .Columns(1).Offset(2).Resize(.Rows.Count - 2) End With 'Define chart's Y values With rngChtData Set rngChtYVal = .Columns(2).Offset(2).Resize(.Rows.Count - 2) End With 'Define chart's Error values With rngChtData Set rngChtErrorVal = .Columns(3).Offset(2).Resize(.Rows.Count - 2) End With 'Add the chart Set myChtObj = ActiveSheet.ChartObjects.Add _ (Left:=250, Width:=375, Top:=75, Height:=225) With myChtObj.Chart 'Make an XY chart .ChartType = xlColumnClustered 'Remove extra series Do Until .SeriesCollection.Count = 0 .SeriesCollection(1).Delete Loop 'Add series from selected range, column by column For iColumn = 1 To rngChtData.Columns.Count - 2 With .SeriesCollection.NewSeries .Values = rngChtYVal.Offset(, iColumn - 1) .XValues = rngChtXVal .Name = rngChtData(1, iColumn) End With Next 'Turn on Error Bars .SeriesCollection(1).HasErrorBars = True .SeriesCollection(1).ErrorBars.EndStyle = xlNoCap .SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:=rngChtErrorVal 'Add Chart axis titles .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = rngChtData(2, 1) .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = rngChtData(2, 2) End With End Sub Thanks, Michael "mwildt" wrote: Hi Jon, Appreciate your reply on this subject. Sad to hear excel 2007 have gone down hill compared to the 2003 version. Hope they will bring back some of the good features from 2003 that got removed. Thanks, Michael "Jon Peltier" wrote: Michael - This is one of the problems with charting in Excel 2007. I see no way within the built in user interface to handle error bars any more easily than you've found. To alleviate this and other issues, I've started designing my own set of dialogs for editing charts. This is a nights and weekends kind of effort, so it won't be available too soon, unfortunately. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mwildt" wrote in message ... Hi, How can the task of adding 'error bars' to a graph be simplified with excel 2007. In excel 2003 it is done way easier, almost just by double clicking the graph. In the 2007 version you have to walk through menus and then more menus to get the task done. When one has to do this for 40 graphs then the 2007 way quickly becomes a huge hassle. Any way to make excel 2007 as easy to use as excel 2003 for this particular feature ? (maybe with a macro or some other way to get this added to the 'right' click menu somehow) Thanks, Michael |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 error bars, why so complicated...
This was one of those inadvertent changes to VBA in Excel 2007. You need to
include both positive and negative values in the statement: SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:=rngChtErrorVal, MinusValues:="0" Similarly if you are adding negative error bars, you must include Amount:="0". - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mwildt" wrote in message ... Hi Jon, I looked at your very nice web page and I cooked up a macro (based on your examples) to do what I needed. It has unfortunately 2 issues which I cannot find an explantion to. - It fails when running in Excel 2007 in the line with SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:=rngChtErrorVal - The other issue is with the caps on the error bars. It seems like there is some 'memory' of the EndStyle type. Just does the opposite of what is done in the script until you have corrected it once. Only verified this with Excel 2003 .SeriesCollection(1).ErrorBars.EndStyle = xlNoCap The script requires the following data to be selected, example: Compound B Samples Response a 1 0.2 b 2 0.3 c 3 0.1 d 4 0.4 e 5 0.5 f 6 0.2 g 7 0.3 h 8 0.3 Compound B is title Samples is the x-axis title Response is the y-axis title Script: Sub EmbeddedGraph() ' ' EmbeddedGraph Macro ' ' Keyboard Shortcut: Ctrl+g ' Dim myChtObj As ChartObject Dim rngChtData As Range Dim rngChtXVal As Range Dim rngChtYVal As Range Dim rngChtErrorVal As Range Dim iColumn As Long 'Make sure a range is selected If TypeName(Selection) < "Range" Then Exit Sub 'Define chart data Set rngChtData = Selection 'Define chart's X values With rngChtData Set rngChtXVal = .Columns(1).Offset(2).Resize(.Rows.Count - 2) End With 'Define chart's Y values With rngChtData Set rngChtYVal = .Columns(2).Offset(2).Resize(.Rows.Count - 2) End With 'Define chart's Error values With rngChtData Set rngChtErrorVal = .Columns(3).Offset(2).Resize(.Rows.Count - 2) End With 'Add the chart Set myChtObj = ActiveSheet.ChartObjects.Add _ (Left:=250, Width:=375, Top:=75, Height:=225) With myChtObj.Chart 'Make an XY chart .ChartType = xlColumnClustered 'Remove extra series Do Until .SeriesCollection.Count = 0 .SeriesCollection(1).Delete Loop 'Add series from selected range, column by column For iColumn = 1 To rngChtData.Columns.Count - 2 With .SeriesCollection.NewSeries .Values = rngChtYVal.Offset(, iColumn - 1) .XValues = rngChtXVal .Name = rngChtData(1, iColumn) End With Next 'Turn on Error Bars .SeriesCollection(1).HasErrorBars = True .SeriesCollection(1).ErrorBars.EndStyle = xlNoCap .SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:=rngChtErrorVal 'Add Chart axis titles .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = rngChtData(2, 1) .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = rngChtData(2, 2) End With End Sub Thanks, Michael "mwildt" wrote: Hi Jon, Appreciate your reply on this subject. Sad to hear excel 2007 have gone down hill compared to the 2003 version. Hope they will bring back some of the good features from 2003 that got removed. Thanks, Michael "Jon Peltier" wrote: Michael - This is one of the problems with charting in Excel 2007. I see no way within the built in user interface to handle error bars any more easily than you've found. To alleviate this and other issues, I've started designing my own set of dialogs for editing charts. This is a nights and weekends kind of effort, so it won't be available too soon, unfortunately. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mwildt" wrote in message ... Hi, How can the task of adding 'error bars' to a graph be simplified with excel 2007. In excel 2003 it is done way easier, almost just by double clicking the graph. In the 2007 version you have to walk through menus and then more menus to get the task done. When one has to do this for 40 graphs then the 2007 way quickly becomes a huge hassle. Any way to make excel 2007 as easy to use as excel 2003 for this particular feature ? (maybe with a macro or some other way to get this added to the 'right' click menu somehow) Thanks, Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Error Bars Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 horizontal error bars | Charts and Charting in Excel | |||
cUSTOM error Bars in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 Custom Error Bars | Excel Discussion (Misc queries) | |||
Custom error bars excel 2007 | Charts and Charting in Excel |