Manipulating Excel Chart using late binding (Custom Error Bars)

[mitch_pearce79]

Hi All,

I have an excel spreadsheet resulting from code that I put together for a Bootstrap analysis of an environmental risk assessment.

The sheet ("Bootstrap") ends up with a table as follows:

Rank	Site	2SD	Average Score
1	a	48	706
2	b	22	282
3	c	22	317
4	d	19	272
5	e	19	221
6	f	9	159
7	g	8	115
8	h	10	122

Where '2SD' is 2 x stdev (95% confidence) and 'Average Score' is the average site/sensitivity score over 1000000 random samples

Now I need to make a clustered column graph in excel showing the average for each site (a-h), and add Error Bars to the value of +2SD/-2SD (range "Bootstrap!$C$2:$C$9")

In excel2010 my code (below) gets hung up at the last line (Type:=xlCustom). It worked fine when I select standard error just to test it, but I want it to plot error bars for the range "Bootstrap!$C$2:$C$9"
In excel2007 my code (below) gets hung up at the 'delete' line.

Dim xl As Object
Set xl = CreateObject("Excel.Application")
…


xl.charts.Add
xl.charts("Chart1").Name = "Bootstrap Graph"
xl.charts("Bootstrap Graph").Activate
xl.ActiveChart.FullSeriesCollection(1).Delete
xl.ActiveChart.FullSeriesCollection(1).Select
xl.ActiveChart.FullSeriesCollection(1).HasErrorBar s = True
xl.ActiveChart.FullSeriesCollection(1).ErrorBars.S elect
'xl.ActiveChart.SetElement (msoElementErrorBarStandardError)
xl.ActiveChart.FullSeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlCustom, Amount:="10" 'xl.Range("Bootstrap!$C$2:$C$9")

I've searched and searched and not been able to find a solution... at least not using late binding anyway, which is my preference.

Any ideas? Should I be setting workbooks, worksheets, chart objects and charts?

Regards,

Mitch

[rpeare]

If nobody answers this you might try an excel forum, I personally don't do much of anything in excel so I am of no use on this question.

[mitch_pearce79]

Cheers. I put it up here because I'm driving it all from access 2010. Most of the code above resulted from recording an excel macro and putting a "xl." in front... and most of it works. Since I recorded the macro from excel I'm assuming is should work IN excel, and that it must be a communication issue between access and excel? Fingers crossed someone has had this issue before and managed to resolve it

[thebigthing313]

I doubt that using late binding vs early binding is the issue here. The only way early binding can help I think is it would identify if the properties/methods you are using exist in the object library. With early binding, you will get compilation errors for invalid properties/methods, but with late binding, the error happens at runtime.

[Jon Peltier]

The code isn't terribly efficient, but it looks like it ought to work. The issue isn't early vs. late binding but rather the use of new properties in an older version of Excel.

FullSeriesCollection wasn't introduced until Excel 2013. Use SeriesCollection instead.

Here is I would code this:

With xl.Charts.Add
    .Name = "Bootstrap Graph"
    .SeriesCollection(1).Delete
    With .SeriesCollection(1)
        .ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlCustom, Amount:=xl.Range("Bootstrap!$C$2:$C$9"), MinusValues:=xl.Range("Bootstrap!$C$2:$C$9")
    End With
End With

For Excel questions, try the Mr Excel forum.