Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o></o>
I'm new here, so let me give a short introduction. My name is Geert, I'm from the <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:country-region w:st="on"><st1lace w:st="on">Netherlands</st1lace></st1:country-region> and I'm using Excel/VBA for my work.<o></o>
<o></o>
Right now I'm adapting an existing macro that was developed with Excel 2003, and I'm adapting it to work under Excel 2007.<o></o>
<o></o>
The macro collects data from a measurement instrument, filters it and makes tables and charts of selected data. The charts have a zoom function, so they work with dynamic named ranges.<o></o>
<o></o>
One of the features of the macro is that the user can convert a standard XY chart of data into a step chart. The step chart is not a standard excel chart but is made by using the error bars option. This method was found elsewhere on the web.<o></o>
<o></o>
Problem right now is that the step chart option does not work anymore, and the point of failure is when a named range is used to define the Y-error bar length. <o></o>
<o></o>
To illustrate the problem, I have made a simplified example workbook with simplified macro code. I will give these here:
<o></o>
<o></o>
<o></o>
Sub step_chart()<o></o>
Dim X_ERROR As Single <o></o>
<o></o>
X_ERROR = [C2]<o></o>
<o></o>
ActiveWorkbook.Names.Add Name:="y_error_2", RefersToR1C1:="=Sheet1!R2C4:R11C4"<o></o>
<o></o>
ActiveSheet.ChartObjects("Chart 1").Activate<o></o>
ActiveChart.SeriesCollection(1).Select<o></o>
<o></o>
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:=xlMinusValues, Type:=xlFixedValue, Amount:=0
<o></o>
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=X_ERROR 'works!!<o></o>
<o></o>
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=0
<o></o>
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlMinusValues, Type:=xlCustom, Amount:="=Sheet1!y_error_2" 'this line does not work under Excel 2007<o></o>
<o></o>
ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap<o></o>
<o></o>
[A1].Select<o></o>
End Sub
Hope you can help me out. Thanks!
Geert<o></o>
fficeffice" /><o></o><o
></o>I'm new here, so let me give a short introduction. My name is Geert, I'm from the <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com
ffice:smarttags" /><st1:country-region w:st="on"><st1lace w:st="on">Netherlands</st1lace></st1:country-region> and I'm using Excel/VBA for my work.<o></o><o
></o>Right now I'm adapting an existing macro that was developed with Excel 2003, and I'm adapting it to work under Excel 2007.<o
></o><o
></o>The macro collects data from a measurement instrument, filters it and makes tables and charts of selected data. The charts have a zoom function, so they work with dynamic named ranges.<o
></o><o
></o>One of the features of the macro is that the user can convert a standard XY chart of data into a step chart. The step chart is not a standard excel chart but is made by using the error bars option. This method was found elsewhere on the web.<o
></o><o
></o>Problem right now is that the step chart option does not work anymore, and the point of failure is when a named range is used to define the Y-error bar length. <o
></o><o
></o>To illustrate the problem, I have made a simplified example workbook with simplified macro code. I will give these here:
></o><o
></o><o
></o>Sub step_chart()<o
></o>Dim X_ERROR As Single <o
></o><o
></o>X_ERROR = [C2]<o
></o><o
></o>ActiveWorkbook.Names.Add Name:="y_error_2", RefersToR1C1:="=Sheet1!R2C4:R11C4"<o
></o><o
></o>ActiveSheet.ChartObjects("Chart 1").Activate<o
></o>ActiveChart.SeriesCollection(1).Select<o
></o><o
></o>ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:=xlMinusValues, Type:=xlFixedValue, Amount:=0
<o
></o>ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=X_ERROR 'works!!<o
></o><o
></o>ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=0
<o
></o>ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlMinusValues, Type:=xlCustom, Amount:="=Sheet1!y_error_2" 'this line does not work under Excel 2007<o
></o><o
></o>ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap<o
></o><o
></o>[A1].Select<o
></o>End Sub
Hope you can help me out. Thanks!
Geert<o
></o>
Last edited:
