If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Error bars in VBA
Hi everyone,
I am having trouble with some vba code to add error bars to a new series I am creating. When I run the following code I get a 'ErrorBar method of Series class failed' error on the first .errorbar line. Can anyone help me out? With ActiveChart.SeriesCollection.NewSeries .name = GraphForm.tbxSeriesName.Value .Values = Worksheets("Data").Range("B1:B25") .XValues = Worksheets("Data").Range("A1:A25") .ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("C1:C25") .ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("D125") End With There is probably a better way to do this (add error bars to a new series) but I am not familiar enough with Excel programming to know what it is. Thanks for any help in advance. |
#2
|
|||
|
|||
Error bars in VBA
I have had some problems in the past drawing charts with error bars. After
a while I found that it helps to organise the code a more logical fashion. Here is an example of something I has lying around which I have just grabbed for you. Hopefully, you can see where to change bits for your own requirements. dim rngData as range dim rngErrX as range dim rngErry as range dim chtChart as chart With ActiveSheet Set rngData = Sheets("......").Range(".....") Set rngErrX = Sheets("......").Range(".....") Set rngErrY = Sheets("......").Range(".....") Set chtChart = .ChartObjects.Add(..., ..., ..., ...).Chart End With With chtChart .ChartType = xlXYScatter .SetSourceData rngData, PlotBy:=xlColumns with SeriesCollection(1) .ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, Amount:=rngErrX .ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, MinusValues:=rngErrY end with end with Hope that helps wrote in message oups.com... Hi everyone, I am having trouble with some vba code to add error bars to a new series I am creating. When I run the following code I get a 'ErrorBar method of Series class failed' error on the first .errorbar line. Can anyone help me out? With ActiveChart.SeriesCollection.NewSeries .name = GraphForm.tbxSeriesName.Value .Values = Worksheets("Data").Range("B1:B25") .XValues = Worksheets("Data").Range("A1:A25") .ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("C1:C25") .ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("D125") End With There is probably a better way to do this (add error bars to a new series) but I am not familiar enough with Excel programming to know what it is. Thanks for any help in advance. |
#3
|
|||
|
|||
Error bars in VBA
A couple of points.
First, the only kind of a chart that supports error bars is one with a numeric X axis (XY Scatter chart, for example). Second, I'm not sure how you got that code. My usual way of generating a starting point is to use the macro recorder. With it, the code I got was ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=data!R1C1:R25C1" ActiveChart.SeriesCollection(1).Values = "=data!R1C2:R25C2" ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=data!R1C3:R25C3" ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=data!R1C4:R25C4" which is easily modified to With ActiveChart.SeriesCollection.NewSeries .XValues = "=data!R1C1:R25C1" .Values = "=data!R1C2:R25C2" .ErrorBar Direction:=xlX, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=data!R1C3:R25C3" .ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=data!R1C4:R25C4" End With The above works just fine with a XY Scatter chart but fails with a couple of the other types that I tested. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article .com, says... Hi everyone, I am having trouble with some vba code to add error bars to a new series I am creating. When I run the following code I get a 'ErrorBar method of Series class failed' error on the first .errorbar line. Can anyone help me out? With ActiveChart.SeriesCollection.NewSeries .name = GraphForm.tbxSeriesName.Value .Values = Worksheets("Data").Range("B1:B25") .XValues = Worksheets("Data").Range("A1:A25") .ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("C1:C25") .ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("D125") End With There is probably a better way to do this (add error bars to a new series) but I am not familiar enough with Excel programming to know what it is. Thanks for any help in advance. |
#4
|
|||
|
|||
Error bars in VBA
Minor adjustment to Tushar's first statement:
The only kind of a chart that supports **X** error bars is one with a numeric X axis (XY Scatter chart, for example). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... A couple of points. First, the only kind of a chart that supports error bars is one with a numeric X axis (XY Scatter chart, for example). Second, I'm not sure how you got that code. My usual way of generating a starting point is to use the macro recorder. With it, the code I got was ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=data!R1C1:R25C1" ActiveChart.SeriesCollection(1).Values = "=data!R1C2:R25C2" ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=data!R1C3:R25C3" ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=data!R1C4:R25C4" which is easily modified to With ActiveChart.SeriesCollection.NewSeries .XValues = "=data!R1C1:R25C1" .Values = "=data!R1C2:R25C2" .ErrorBar Direction:=xlX, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=data!R1C3:R25C3" .ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=data!R1C4:R25C4" End With The above works just fine with a XY Scatter chart but fails with a couple of the other types that I tested. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article .com, says... Hi everyone, I am having trouble with some vba code to add error bars to a new series I am creating. When I run the following code I get a 'ErrorBar method of Series class failed' error on the first .errorbar line. Can anyone help me out? With ActiveChart.SeriesCollection.NewSeries .name = GraphForm.tbxSeriesName.Value .Values = Worksheets("Data").Range("B1:B25") .XValues = Worksheets("Data").Range("A1:A25") .ErrorBar Direction:=xlX, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("C1:C25") .ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeBoth, Type:=xlCustom, _ Amount:=Worksheets("Data").Range("D125") End With There is probably a better way to do this (add error bars to a new series) but I am not familiar enough with Excel programming to know what it is. Thanks for any help in advance. |
#5
|
|||
|
|||
Error bars in VBA
In article ,
says... Minor adjustment to Tushar's first statement: The only kind of a chart that supports **X** error bars is one with a numeric X axis (XY Scatter chart, for example). - Jon ------- Thanks, Jon. {grin} -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
#6
|
|||
|
|||
Error bars in VBA
Thanks for the help everyone. Come to find out that when you try and do
an Include:=xlErrorBarIncludeBoth or Include:=xlBoth (which is the same thing as far as I can see) with the Type:=xlCustom you must include both the Amount:= (for the positive directions) and the MinusValues:= (for the negative directions) even if you want to use the same values for both the plus and minus of the error bars. So that was what I was leaving out. Thanks again. |
Thread Tools | |
Display Modes | |
|
|