Creating custom standard deviation bars in VBA from Access

Question

I'm trying to include custom standard deviation bars in a chart using VBA, but I keep getting a run time error 13 "type mismatch" in the line that actually adds the bars. I believe it has a problem with my range object (rngStD), but I don't know why. I'm using this VBA in Access, but I've created an Excel application (xlApp), which is where the data is now and where the graphs are being created.

'Start of relevant code
xlApp.Sheets("Monday").Select
Set rngAv = Range(Cells(numRows + 2, 3), Cells(numRows + 2, 26))
Set rngStD = Range(Cells(numRows + 3, 3), Cells(numRows + 3, 25))
xlApp.Sheets("Graphs").Select
'Creates graph for average usage with standard deviation at each point
Set oChart = xlApp.Worksheets("Graphs").ChartObjects.Add(600, 10, 500, 250).Chart
    oChart.SetSourceData Source:=rngAv   'xlApp.Selection
    oChart.Type = xlLine
    oChart.HasTitle = True
    oChart.ChartTitle.Text = "Average Usage for Mondays"

'At this point the code works and correctly creates the above graph

With oChart.FullSeriesCollection(1)
    .HasErrorBars = True
    .ErrorBars.Select
    'Error is on the next line, I believe it doesn't like the "Amount:=rngStD"
    .ErrorBar Direction:=xlY, Include:= _
        xlBoth, Type:=xlCustom, Amount:=rngStD.Value
    .ErrorBars.Select

End With

EDIT: added .Value at the end of rngStD.Value in the last line. Now the amount is fixed at 50 though instead of the individual values for each point in range. Not sure why or how to fix it.

Answer 1

If rngStD has been declared as a range then you need to add .Value to it. This will pass the value stored by the range not the range object itself.

Answer 2

You need to pass in the address of the range in R1C1 notation, preceded by an equals sign. Try this syntax:

.ErrorBar Direction:=xlY, Include:=xlBoth, _
    Type:=xlCustom, Amount:="=" & rngStD.Address(, , xlR1C1, True), _
    MinusValues:="=" & rngStD.Address(, , xlR1C1, True)