Creating a line chart with variable error bars

Question

I'm trying to automatically generate a line chart using VBA and Excel where each data point has a different size of error bar. (I would love to use my go-to of Python/matplotlib, but am tied in for business reasons)

I tried recording a macro to see how to do it, but the code produced was this:

Range("C2:C8").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$C$2:$C$8")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$B$2:$B$8"
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).HasErrorBars = True
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).ErrorBars.Select
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, _
    Type:=xlCustom, Amount:=0
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).ErrorBars.Select

But that isn't too helpful - the Amount value is zero! So, I tried altering this and putting it in a subroutine, making the error bar range dynamic, like so:

Sub ErrorLine(sheetName As String, row1 As Integer, _
row2 As Integer, xcol As Integer, ycol As Integer, errCol As Integer)

Dim strErrorY As String
strErrorY = "=" & sheetName & "!" & _
    Range(Cells(row1, errCol), Cells(row2, errCol)).Address()

Sheets(sheetName).Activate
Sheets(sheetName).Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(Cells(row1, ycol), Cells(row2, ycol))
ActiveChart.ChartType = xlLineMarkers

With ActiveChart.SeriesCollection(1)
    .XValues = Range(Cells(row1, xcol), Cells(row2, xcol))
    .HasErrorBars = True
    .ErrorBars.Select
    .ErrorBar Direction:=xlY, Include:=xlBoth, _
        Type:=xlCustom, Amount:=strErrorY, MinusValues:= _
        strErrorY
End With
End Sub

But this just gives me a line chart, with no error bars. Can anyone help me out? Help appreciated.

Jean-François Corbett · Accepted Answer · 2011-07-13 13:09:28Z

1

.ErrorBar doesn't seem to like A1 style addresses, which is what the .Address method returns. It likes RC style addresses.

My advice is: forget that string concatenation business to make cell addresses. It's messy and it's a pain!

Just supply the ranges themselves.

Dim rngAmount As Range
Set rngAmount = _
    Worksheets(sheetName).Range(Cells(row1, errCol), Cells(row2, errCol))

and then

Amount:=rngAmount, MinusValues:=rngAmount

answered Jul 13, 2011 at 13:09

Jean-François Corbett

38.7k30 gold badges146 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a line chart with variable error bars

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related