setting the range of cells to be used for customtype errorbars

[Steve]

Hello everybody,

I am trying to set the range of cells to be used for an errorbar of
the 'customtype'. The range that I want to be used changes every time
I work on a different set of data.

This is basically what I want to do:

'******** Beginning of code *******

With Application.Workbooks("Auswerter.xls").Worksheets("data")
Set xrng = .Range(.Cells(2, fraction_row + 1), .Cells(merge_end -
1, fraction_row + 1))
Set yrng = .Range(.Cells(2, fraction_row + 2), .Cells(merge_end -
1, fraction_row + 2))
Set errrng = .Range(.Cells(2, fraction_row + 3), .Cells(merge_end
- 1, fraction_row + 3))
End With

Application.Workbooks("Auswerter.xls").Charts(2).SeriesCollection.NewSeries

With Application.Workbooks("Auswerter.xls").Charts(2)
.SeriesCollection(1).XValues = xrng
.SeriesCollection(1).Values = yrng
.errorbar Direction:=xlX, Include:=xlBoth,
Type:=xlErrorBarTypeCustom, Amount:=errrng minusValues:=errrng 'this
line generates the error
End With

'******* End of code *******

This gives an error 438: "Object doesn't suppoert this property or
method"

Setting it to the same range manually works, so there should not be a
problem with the values in the cells, but I guess it is rather the
usage of a range variable for amount and minusValues.

I already looked all over google, but cannot find a clue why this
error comes up or how I can get rid of it.
Would be great if someone could help me!

Thanks!
Steve

 

[David G]

This gives an error 438: "Object doesn't suppoert this property or
method"

Which line gives you this error?

David

 

[GJCV]

I am having the same problem. In Steve's sample code the following line gives
the error:

..errorbar Direction:=xlX, Include:=xlBoth, _
Type:=xlErrorBarTypeCustom, Amount:=errrng minusValues:=errrng

Anyone a solution?
Thank you. GJCV

 

[GJCV]

Passing a string holding a range generates this error.
VisualBasic expects real or integer values here.

I found a solution to this problem:
Using an array variable you can set (or update) the error bar values to the
values in a range of cells using an array variable that holds these values.

(I have not been able to reference the error bar cells in the chart, in
other words if the values in the spreadsheet change, the error bars in the
chart do not change unless you change the errorbars manually (point and
click) or by code (using the array variable).

Compare the following code:

'varErrorbarArray is the variable that will hold an array of errorvalues
Dim varErrorbarArray As Variant
Dim intError(1 To 15) As Integer

'OTHER CODE IN BETWEEN ....
'in the following code, ch is chart object variable, GetCellContent is a
procedure
'I wrote that retrieves the contents of a specified cell
'in the follwing example the chart contains a single series with only four
values

'make graph
Set ch = Worksheets(strDataSheet1).ChartObjects.Add(1400, (216 * k -
216), intChartWidth, intChartHeigth)
With ch.Chart
.ChartWizard
Source:=Worksheets(strDataSheet1).Range(strSourceRange), _
Gallery:=xlColumn, _
PlotBy:=xlColumns, _
categorylabels:=1, _
HasLegend:=False, _
Title:=strChartTitle, _
valuetitle:="Expression"

'make ErrorBars
For i = 1 To 4
intError(i) = CInt(GetCellContent(strDataSheet1, "U", CLng(17 *
k - 16 + i)))
Next i
varErrorbarArray = Array(intError(1), intError(2), intError(3),
intError(4))

.SeriesCollection(1).HasErrorBars = True
.SeriesCollection(1).ErrorBar _
Direction:=xlY, _
Include:=xlErrorBarIncludeBoth, _
Type:=xlErrorBarTypeCustom, _
amount:=varErrorbarArray, _
minusvalues:=varErrorbarArray
.SeriesCollection(1).ErrorBars.EndStyle = xlCap
.SeriesCollection(1).ErrorBars.Border.Weight = xlMedium

End With