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.
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.