none
VBA coding error for Custom Error Bar plotting RRS feed

  • Question

  • Hello

    I'm using VBA to specify custom error bars for a graph.  There are two series of data.  I'm receiving a Type Mismatch error on the following command:

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

    Any assistance is greatly appreciated!

    Thanks very much!

    MarkKirk

    Code for this segment...

    'Select the chart and Activate the Error Bars
    'Set the range of the values for the Custom Error Bars
        Dim rngErr1 As Range
        Dim rngErr2 As Range

        With ActiveSheet
        Set rngErr1 = Sheets("Subclinical_Infections").Range("AD7:AQ7")
        Set rngErr2 = Sheets("Subclinical_Infections").Range("AD8:AQ8")

    End With

    'Select 1st data series to apply Custom Error Bar
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.SeriesCollection(1).Select
        ActiveChart.SeriesCollection(1).HasErrorBars = True
        ActiveChart.SeriesCollection(1).ErrorBars.Select
        ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, _
            Type:=xlCustom, Amount:=rngErr1
        ActiveChart.SeriesCollection(1).ErrorBars.Select

    'Select 2nd data series to apply Custom Error Bar
        ActiveChart.SeriesCollection(2).ErrorBars.Select
        ActiveChart.SeriesCollection(2).Select
        ActiveChart.SeriesCollection(2).HasErrorBars = True
        ActiveChart.SeriesCollection(2).ErrorBars.Select
        ActiveChart.SeriesCollection(2).ErrorBar Direction:=xlY, Include:=xlBoth, _
            Type:=xlCustom, Amount:=rngErr2
        ActiveChart.SeriesCollection(2).ErrorBars.Select

    Wednesday, December 31, 2014 5:14 PM

Answers

  • Hi MarkKirk,

    Thanks for posting in MSDN forum.

    Based on the description, you are creating an error bar for the chart and the type of error bar is custom.

    As far as I know, if we the error bar type is custom, we need to specific both positive and negative error amount.

    Here is a sample for your reference:

     ActiveChart.FullSeriesCollection(2).ErrorBar Direction:=xlX, Include:= _
            xlBoth, Type:=xlCustom, Amount:=Range("D2:D9"), MinusValues:=Range("E2:e9")
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 2, 2015 5:58 AM
    Moderator