Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting the range of cells to be used for customtype errorbars
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).S eriesCollection.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting the range of cells to be used for customtype errorbars
On Apr 16, 1:11 pm, "Steve" wrote:
This gives an error 438: "Object doesn't suppoert this property or method" Which line gives you this error? David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting the range of cells to be used for customtype errorbars
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 "David G" wrote: Which line gives you this error? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting the range of cells to be used for customtype errorbars
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(strSourceR ange), _ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting up formatting for a range of cells | Excel Discussion (Misc queries) | |||
Setting an array range of cells??? | Excel Programming | |||
Help finding error setting range to Visible Cells | Excel Programming | |||
Setting number format on range of cells | Excel Programming | |||
Setting range of cells format to currency | Excel Programming |