Custom error bars in Bar Chart with VBA Excel

Question

I am writing an Excel Macro that should create a bar graph with custom error bars (Standard error of mean, calculated on the sheet). But I got stuck with including the values.

This is what I want to accomplish:

The averages should be displayed in the bar graph, the calculated group standard errors should be added as values for the error bars.

This is the VBA code:

Dim ValueCell as Range

Set ValueCell = Range("B98:C103")
ValueCell.Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).HasErrorBars = True

Set ValueCell = Worksheets(SHEET_NAME).Range(Cells(108, 3), Cells(112, 3))
ActiveChart.FullSeriesCollection(1).ErrorBar Direction:=xlY, _
                  Include:=xlPlusValues, Type:=xlCustom, Amount:=ValueCell, _
                  PlusValues:=ValueCell

It got stuck when I include the Amount and/or PlusValues parameter. I get an "Application-defined or object-defined error". After running the code it includes the graph, but with fixed Error bars. I searched for similar answers, but none seem to work in my code. I tried different formats for those 2 parameters (range, arrays) but nothing seems to work. I also tried to record a macro and then extract the code I need, but it does not help. It should be possible, but I can't figure out how to.

You're correct, I can't find anymore where I got that parameter from. Thanks — Mic Mic
– Mic Mic, Commented Mar 10 at 9:25

MGonet · Accepted Answer · 2025-03-10 05:19:28Z

0

This code worked for me:

Sub AddChartWithErrors()
    Dim Sheet_Name As String
    Sheet_Name = "Arkusz2"
    With ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Chart
        .SetSourceData Source:=Worksheets(Sheet_Name).Range("$B$3:$C$8")
        .FullSeriesCollection(1).HasErrorBars = True
        .FullSeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
        xlPlusValues, Type:=xlCustom, Amount:=Worksheets(Sheet_Name).Range("$C$13:$C$18")
        .Axes(xlValue).TickLabels.NumberFormat = "General"
    End With
End Sub

answered Mar 10 at 5:19

MGonet

2,6752 gold badges3 silver badges9 bronze badges

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

Comments

Mic Mic · Accepted Answer · 2025-03-10 09:36:50Z

0

With the help of these answers I got this code to work

Private Sub BarGraphWithErrorBars(ByVal ShName As String, ByVal AvRange As Range, ByVal ErrRange As Range)

    AvRange.Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.FullSeriesCollection(1).HasErrorBars = True
    ActiveChart.FullSeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlErrorBarIncludePlusValues, Type:=xlErrorBarTypeCustom, Amount:=ErrRange, MinusValues:=ErrRange
    
End Sub

I am adding more functionalities to it to fit the whole program, I suppose the main error was in the non-existing PlusValues parameter. Changing it to MinusValues does the trick, although I am using Plus values it seems counter intuitive.

edited Mar 10 at 9:36

answered Mar 10 at 9:36

Mic Mic

12 bronze badges

Collectives™ on Stack Overflow

Custom error bars in Bar Chart with VBA Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related