excel 2007 error bars, why so complicated...

[mwildt]

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

 

[Jon Peltier]

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

 

[mwildt]

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

 

[mwildt]

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

 

[Jon Peltier]

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