Excel 2010 VBA Custom Error Bar Minus Direction Error (Creating a boxplot)

Question

I'm making a boxplot off a stacked column chart, and using minus and plus error bars to form the whiskers for my box plot. i referred to this to create a boxplot in Excel 2010

The code to create the plus direction error bar

.SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
        xlPlusValues, Type:=xlCustom, Amount:="=Sheet3!$B$12:$G$12"

works but the minus direction error bar:

.SeriesCollection(2).ErrorBar Direction:=xlY, Include:= _
        xlMinusValues, Type:=xlCustom, Amount:="=Sheet3!$B$9:$G$9"

throws a Runtime error '13': Type mismatch

Here's the entire code:

Sub boxplot()
    Dim rng As Range
    Dim cht As Chart

    Set datasht = ThisWorkbook.Sheets(3)
    Set rng = getChtrng(8, 12, 2, 7) 'select cell ranges to populate chart

    datasht.Activate
    rng.Select
    Set cht = charts.Add
    Set cht = cht.Location(Where:=xlLocationAsObject, Name:="Sheet3")

    With cht
        .SetSourceData rng
        .ChartType = xlColumnStacked
        .SeriesCollection(1).XValues = "=Sheet3!$B$1:$G$1"
        .HasTitle = True
        .HasLegend = False
        .ChartTitle.Text = ThisWorkbook.Sheets(1).Cells(1, 6).Value

        'remove min, q1 and max quartile bars
        .SeriesCollection(1).Select
        Selection.Format.Fill.Visible = msoFalse
        .SeriesCollection(2).Select
        Selection.Format.Fill.Visible = msoFalse
        .SeriesCollection(5).Select
        Selection.Format.Fill.Visible = msoFalse

        'include top whisker
        .SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
            xlPlusValues, Type:=xlCustom, Amount:="=Sheet3!$B$12:$G$12"

        'include bottom whisker
        .SeriesCollection(2).ErrorBar Direction:=xlY, Include:= _
            xlMinusValues, Type:=xlCustom, Amount:="=Sheet3!$B$9:$G$9"

    End With
End Sub

also, any alternatives to creating a boxplot in vba that works for sure?

I don't use charts often enough to be sure, but is there a reason why your question says that you set `xlMinusValues` for `SeriesCollection(4)` but your code shows you setting it for `SeriesCollection(2)`? — YowE3K, Jul 11 '17 at 09:22

score 0 · Answer 1 · answered Jul 22 '18 at 15:33

Syntax for the ErrorBar is as below: .ErrorBar( Direction , Include , Type , Amount , MinusValues )

which Amount is to be used for only the positive error amount and MinusValues is to be used for only the negative error amount when Type is xlErrorBarTypeCustom.

Thus, you will need to change your statement for MinusValues to be the below:

.SeriesCollection(2).ErrorBar Direction:=xlY, Include:= _
        xlMinusValues, Type:=xlCustom, _
        Amount:="=Sheet3!$B$9:$G$9", MinusValues:="=Sheet3!$B$9:$G$9"

Excel 2010 VBA Custom Error Bar Minus Direction Error (Creating a boxplot)

1 Answers1