Charts : Error Bars thru VBA

lambuhere1 · Jan 6th 2004

Hi

I am having a issue with respect to adding error bars to each data point in a series collection. Error Propogates in Time. And my chart is Time Vs Independent variable in XY Scatter Chart Sub Type One. Error on each data point is bothe positive and negative. Both the values are same.

Chart is made from data in columns A and B. Column A has time in seconds, Column B has independent variable, lets say X. Column C has the error corresponding to each data point. Now this error needs to be represented on each data point using VBA. Can anyone be kind enough to help me out in this.

Thanks

Ram P

**Andy Pope** · Jan 6th 2004

Hi Ram P,

Some slightly modified code generated by using the record macro.

Code

With ActiveChart.SeriesCollection(1)
        .ErrorBar Direction:=xlX, Include:=xlBoth, _
        Type:=xlCustom, Amount:="=Sheet1!R1C3:R10C3", MinusValues:= _
        "=Sheet1!R1C3:R10C3"
        .ErrorBar Direction:=xlY, Include:=xlBoth, _
        Type:=xlCustom, Amount:="=Sheet1!R1C3:R10C3", MinusValues:= _
        "=Sheet1!R1C3:R10C3"
    End With

The xy chart data series is based on data in range A1:B10. The x and y error bars are based on C1:C10.
It uses the Custom +- values to set a error bar for each point in the series.

Hopefully this is what you were after. If not then post back.

Cheers
Andy

lambuhere1 · Jan 6th 2004

Andy

Thanks for your reply.

My issue comes with respect to Amount and Minus values syntax.
If I use errorx = "C2:C10" and If I use Amount:= = errorx and the same syntax for minus values, I get a error. Why do you think is this happening?

Thanks

Ram P

**Andy Pope** · Jan 6th 2004

Looks like the range reference needs to be in R1C1 format.

Try this revised code.

Code

Sub x()
    Dim strErrorX As String
    strErrorX = "=" & ActiveSheet.Name & "!" & Range("C1:C10").Address(ReferenceStyle:=xlR1C1)
    With ActiveChart.SeriesCollection(1)
        .ErrorBar Direction:=xlX, Include:=xlBoth, _
        Type:=xlCustom, Amount:=strErrorX, MinusValues:= _
        strErrorX
        .ErrorBar Direction:=xlY, Include:=xlBoth, _
        Type:=xlCustom, Amount:=strErrorX, MinusValues:= _
        strErrorX
    End With
End Sub

Display More

Cheers
Andy

lambuhere1 · Jan 6th 2004

Thanks Andy for your help.

Beer is on me !!!

Charts : Error Bars thru VBA

Participate now!

Share