Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Error Bars Excel 2007 Lars B Excel Discussion (Misc queries) 8 January 27th 09 12:55 PM
Excel 2007 horizontal error bars DickB Charts and Charting in Excel 4 November 20th 07 03:23 AM
cUSTOM error Bars in Excel 2007 RM2007 Excel Discussion (Misc queries) 0 June 20th 07 09:17 AM
Excel 2007 Custom Error Bars BuzzinHornet Excel Discussion (Misc queries) 4 June 14th 06 10:47 PM
Custom error bars excel 2007 BuzzinHornet Charts and Charting in Excel 2 May 30th 06 02:21 AM


All times are GMT +1. The time now is 07:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"