Error bar macro does not work with Excel2007

Geert1968

New Member
Joined
Oct 23, 2008
Messages
6
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:eek:ffice:smarttags" /><st1:country-region w:st="on"><st1:place w:st="on">Netherlands</st1:place></st1:country-region> and I'm using Excel/VBA for my work.<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
To illustrate the problem, I have made a simplified example workbook with simplified macro code. I will give these here:


screenexcelgw9.jpg
<o:p></o:p>

<o:p></o:p>
<o:p></o:p>
Sub step_chart()<o:p></o:p>
Dim X_ERROR As Single <o:p></o:p>
<o:p></o:p>
X_ERROR = [C2]<o:p></o:p>
<o:p></o:p>
ActiveWorkbook.Names.Add Name:="y_error_2", RefersToR1C1:="=Sheet1!R2C4:R11C4"<o:p></o:p>
<o:p></o:p>
ActiveSheet.ChartObjects("Chart 1").Activate<o:p></o:p>
ActiveChart.SeriesCollection(1).Select<o:p></o:p>
<o:p></o:p>
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:=xlMinusValues, Type:=xlFixedValue, Amount:=0
<o:p></o:p>
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=X_ERROR 'works!!<o:p></o:p>
<o:p></o:p>
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=0
<o:p></o:p>
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlMinusValues, Type:=xlCustom, Amount:="=Sheet1!y_error_2" 'this line does not work under Excel 2007<o:p></o:p>
<o:p></o:p>
ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap<o:p></o:p>
<o:p></o:p>
[A1].Select<o:p></o:p>
End Sub




Hope you can help me out. Thanks!

Geert<o:p></o:p>
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Have you tried using the workbook name rather than the sheet name when referring to the named range? (assuming it wasn't actually created as a worksheet-level name)
 
Upvote 0
Hi Rory,

thanks for the reply! I tried to change the code, as you suggested, but unfortunately it didn't work.

What I also did was try to recreate the given code by recording it with the macro recorder. Prior to recording, I already had the named range ("y_error_2") defined. The XY chart, as shown in the first post, was also prepared.
While recording, when creating the Y-error bars, I then used the predefined named range as entry in the popup screen. Everything worked fine, the whole step chart was created. But...when I stopped recording and went on to view the recorded code, the named range was not included in the code line!! Instead, the digit "0" was shown in place of "Sheet1!y_error_2".

It looks like this is a bug in Excel 2007, since it used to work fine in Excel 2000 and 2003.


Best regards,
Geert
 
Upvote 0
There is a number of issues with charts in Excel (quite a few were fixed by SP1 - do you have that installed?), especially where macro recording is concerned. I don't have 2007 here to test with and probably can't test before the weekend, but I will try and remember to have a play with your code then if someone else hasn't come up with an answer in the meantime.
 
Upvote 0
Hi Rory,

I already installed SP1 (and removed Google desktop search).

It would be very helpful if you could take a look at the code on Excel2007, I would appreciate that very much.

Do you need the workbook (including code) to do that? I can send it to you if needed. It's not much more than the stuff shown in the first post.

Best regards,
Geerten.
 
Upvote 0
Yes, it would be helpful to have a sample to test on. I will PM you my email address.
 
Upvote 0
2007 uses existing VBA code successfully 99.5% of the time. This is part of the other 0.5%.

What you have to do is explicitly enter a zero for the unused direction.

Code:
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlPlusValues, _
    Type:=xlCustom, Amount:="=Sheet1!y_error_1", MinusValues:="0" 

ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlMinusValues, _
    Type:=xlCustom, Amount:="0", MinusValues:="=Sheet1!y_error_2"
 
Upvote 0
Hi Jon,

Thanks for your reply. I tried your solution and it works perfectly! I certainly couldn't have thought of this myself! Thank you very much for your help and time.

Have a good weekend,
Geert.
 
Last edited:
Upvote 0
Hi Rory,

thanks again for the offer to help. Mr. Peltier's solution saves you some time, I guess.

Best regards,
Geert.
 
Upvote 0
It sure does - I can spend the weekend in Photoshop instead! :)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top