PDA

View Full Version : Solved: Help with modifying error bars via VBA



EirikDaude
04-24-2012, 05:47 AM
I have a chart in which I have put some "arbitrary gridlines", using the technique described on this page on Peltier Tech's homepage (http://peltiertech.com/Excel/Charts/ArbitraryGridlines.html). Now, I want my VBA macro to adjust the length of the "grid lines" or rather error bars, depending on how many values I have along the x-axis of the chart. I used the macro recorder to see how I should do this, and got this output:
Sub Macro5()
ActiveSheet.ChartObjects("Langsone3").Activate
ActiveChart.SeriesCollection(3).ErrorBars.Select
' 10 is the value I entered for the errorbar's length.
ExecuteExcel4Macro "ERRORBAR.X(2,1,10)"
End Sub

Trying to put this into my own macro, I wrote this code-snippet:

Set xverdier = Range(maalCelle.Offset(0, 1), maalCelle.Offset(0, 1).End(xlDown))
maaleantal = xverdier.Cells.Count
With Worksheets("Utvikling").ChartObjects(chartname)
extragrid = "ERRORBAR.X(2,1," + CStr(maaleantal) + ")"
.Chart.SeriesCollection(3).ErrorBars.ExecuteExcel4Macro extragrid
End With

However, when I try to run the macro I get an error message; "Run-time error '438': Object doesn't support this property or method". When debugging I am told the error is in the line with the ExecuteExcel4Macro-function in it.

So, I was hoping someone here would be so kind as to tell me what it is I am doing wrong, and hopefully how I can reach my goal.

Cheers! :)

- edit - Running Excel 2003, in case that matters.

p45cal
04-24-2012, 01:47 PM
It looks like the excel4macro needs things to be selected to work.
This might work:extragrid = "ERRORBAR.X(2,1," + CStr(maaleantal) + ")"
.Chart.SeriesCollection(3).ErrorBars.Select
ExecuteExcel4Macro extragrid
but not tested.

However..
While recording the macro, instead of selecting the errorbars then formatting them, select the series, format the series navigating to the error bars tab and make adjustments there.
You'll end up with something like:
.chart.SeriesCollection(3).ErrorBar Direction:=xlX, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=3.33
where it's the 3.33 you need to substitute a variable for.
Perhaps:
.chart.SeriesCollection(3).ErrorBar Direction:=xlX, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=maaleantal
??

EirikDaude
04-24-2012, 03:22 PM
Thanks for the reply, and for taking the time to help :) Will give it a shot when I get to work tomorrow, and get back to you on how it worked.

EirikDaude
04-24-2012, 11:17 PM
I tried the second method you suggested, since that simply looks more tidy and transparent than using ExecuteExcel4Macro, and it worked like a charm.

Thanks a ton - and sorry for bothering you all, I should have thought of editing the error bars via formatting the series myself :P