Error while creating error bars in Excel with VBA

Question

I want to insert error bars into a clustered column chart in excel using macros. Whenever I do, an error is thrown.

I have a much larger program that takes and sorts data into a couple column charts, and I want to add error bars to them. In an attempt to troubleshoot, I stripped the program way back to just the part that adds the error bars in a separate sheet (code below). I made a simple column chart, selected it and tried to run the program, but it threw an Application Defined error. Then I tried recording a macro, and that also failed (with a "Method SetElement of Object _Chart Failed" error).

I've Googled around a bit, and found next to nothing (only examples of how to use my first method, close to exactly the same as mine), but also I am relatively new to VBA and might just not know the correct words to google.

Here is my code:

'The code that I had written
Sub AddErrorBars()

'Adds error bars (Standard deviation) to selected chart

ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, _
      Type:=xlErrorBarTypeStDev, Include:=xlBoth

End Sub


'The code generated by recoring a macro
Sub AddErrorBarsV2()

'Adds error bars (Standard deviation) to selected chart

    ActiveChart.SetElement (msoElementLineHiLoLine)

End Sub

One odd thing, the Include enum in first sub (xlBoth) was listed as xlErrorBarIncludeBoth in the MO documentation website, but the listed value is 1, which is the value of xlBoth according to the debugger, xlErrorBarIncludeBoth is empty. Using xlNone doesn't spit out an error, but also doesn't generate error bars. Ive also tried changing the Direction, but that doesnt seem to help.

Is there something I am doing wrong, or perhaps is it the way my data is formatted? Does anyone else seem to have the same issue?

dwirony · Accepted Answer · 2019-07-19 19:19:19Z

0

If you have the chart selected, then ActiveChart.SetElement (msoElementErrorBarStandardError) will work fine. However, you should call the chart by name, like so:

ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SetElement (msoElementErrorBarStandardError)

answered Jul 19, 2019 at 19:19

dwirony

5,4604 gold badges26 silver badges49 bronze badges

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

1 Comment

Andrew Over a year ago

Thank you, my issue was the mso enum inside of SetElement. For some reason, excel was generating msoElementLineHiLoLine when I recorded the macro. When I replaced it with msoElementErrorBarStandardError, it worked fine.

Collectives™ on Stack Overflow

Error while creating error bars in Excel with VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related